nuBuilder Forum


https://forums.nubuilder.cloud/

Guidance needed creating multi-criteria search form with subform

https://forums.nubuilder.cloud/viewtopic.php?t=11872

Page 6 of 7

Re: Guidance needed creating multi-criteria search form with subform

Posted: Wed Feb 15, 2023 10:35 am
by kev1n
Could you also post the db schema for the 3 tables (without data)?

Re: Guidance needed creating multi-criteria search form with subform

Posted: Wed Feb 15, 2023 11:29 am
by Keith-i
Does this help? If not I'll do some googling as I'm not sure how to get you just the schema without data.

Re: Guidance needed creating multi-criteria search form with subform

Posted: Wed Feb 15, 2023 11:42 am
by kev1n
Thank you, that certainly helps to understand the relations.

To export a table (schema), open phpMyAdmin, select the table to export, set Number of rows to 0 and start the export. Repeat it for each table.
2023-02-15_113959.png

Re: Guidance needed creating multi-criteria search form with subform

Posted: Wed Feb 15, 2023 11:51 am
by Keith-i
Thanks for the heads up on exporting empty tables. Would you still like the schema dump?

Re: Guidance needed creating multi-criteria search form with subform

Posted: Wed Feb 15, 2023 12:03 pm
by kev1n
Yes, please. This allows me to take a look at the forms without much effort.

Re: Guidance needed creating multi-criteria search form with subform

Posted: Wed Feb 15, 2023 12:31 pm
by Keith-i
tblInstructions

Code: Select all

-- phpMyAdmin SQL Dump
-- version 5.2.1-dev+20221028.64e273f305
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Feb 15, 2023 at 11:25 AM
-- Server version: 8.0.32-0ubuntu0.20.04.2
-- PHP Version: 7.4.3-4ubuntu2.17

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `rcs2022db`
--

-- --------------------------------------------------------

--
-- Table structure for table `tblInstructions`
--

CREATE TABLE `tblInstructions` (
  `idInstructions` int NOT NULL,
  `JobNo` varchar(6) DEFAULT NULL,
  `Date` date DEFAULT NULL,
  `FlatNo` varchar(10) DEFAULT NULL,
  `id_Clients` int DEFAULT NULL,
  `id_Applicants` int DEFAULT NULL,
  `id_Properties` int DEFAULT NULL,
  `Schedule` tinyint DEFAULT NULL,
  `Level_1` tinyint DEFAULT NULL,
  `Level_2` tinyint DEFAULT NULL,
  `Level_3` tinyint DEFAULT NULL,
  `BTL` tinyint DEFAULT NULL,
  `Revalue` tinyint DEFAULT NULL,
  `Insurance` tinyint DEFAULT NULL,
  `Redbook` tinyint DEFAULT NULL,
  `External` tinyint DEFAULT NULL,
  `Desktop` tinyint DEFAULT NULL,
  `Valuation` decimal(12,2) DEFAULT NULL,
  `Type` int DEFAULT NULL,
  `Style` int DEFAULT NULL,
  `Location` int DEFAULT NULL,
  `Bedrooms` int DEFAULT NULL,
  `Parking` tinyint DEFAULT NULL,
  `Seaview` int DEFAULT NULL,
  `Integral` int DEFAULT NULL,
  `FTB` tinyint DEFAULT NULL,
  `Size` int DEFAULT NULL,
  `id_Surveyor` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblInstructions`
--
ALTER TABLE `tblInstructions`
  ADD PRIMARY KEY (`idInstructions`),
  ADD UNIQUE KEY `idInstructions_UNIQUE` (`idInstructions`),
  ADD KEY `fk_Instructions_Clients1_idx` (`id_Clients`),
  ADD KEY `fk_Instructions_Applicants1_idx` (`id_Applicants`),
  ADD KEY `fk_Instructions_Properties1_idx` (`id_Properties`),
  ADD KEY `tblInstructions_ibfk_1` (`id_Surveyor`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tblInstructions`
--
ALTER TABLE `tblInstructions`
  MODIFY `idInstructions` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=51470;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `tblInstructions`
--
ALTER TABLE `tblInstructions`
  ADD CONSTRAINT `fk_Instructions_Applicants1` FOREIGN KEY (`id_Applicants`) REFERENCES `tblApplicants` (`idApplicants`),
  ADD CONSTRAINT `fk_Instructions_Clients1` FOREIGN KEY (`id_Clients`) REFERENCES `tblClients` (`idClients`),
  ADD CONSTRAINT `fk_Instructions_Properties1` FOREIGN KEY (`id_Properties`) REFERENCES `tblProperties` (`idProperties`),
  ADD CONSTRAINT `tblInstructions_ibfk_1` FOREIGN KEY (`id_Surveyor`) REFERENCES `tblSurveyor` (`idSurveyor`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
tblProperties

Code: Select all

-- phpMyAdmin SQL Dump
-- version 5.2.1-dev+20221028.64e273f305
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Feb 15, 2023 at 11:24 AM
-- Server version: 8.0.32-0ubuntu0.20.04.2
-- PHP Version: 7.4.3-4ubuntu2.17

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `rcs2022db`
--

-- --------------------------------------------------------

--
-- Table structure for table `tblProperties`
--

CREATE TABLE `tblProperties` (
  `idProperties` int NOT NULL,
  `BuildingName` varchar(45) DEFAULT NULL,
  `UnitNo` varchar(45) DEFAULT NULL,
  `UnitName` varchar(45) DEFAULT NULL,
  `HouseNo` varchar(45) DEFAULT NULL,
  `id_Roads` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblProperties`
--
ALTER TABLE `tblProperties`
  ADD PRIMARY KEY (`idProperties`),
  ADD UNIQUE KEY `idProperties_UNIQUE` (`idProperties`),
  ADD KEY `fk_Properties_Roads1_idx` (`id_Roads`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tblProperties`
--
ALTER TABLE `tblProperties`
  MODIFY `idProperties` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=36487;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `tblProperties`
--
ALTER TABLE `tblProperties`
  ADD CONSTRAINT `fk_Properties_Roads1` FOREIGN KEY (`id_Roads`) REFERENCES `tblRoads` (`idRoads`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
tblRoads

Code: Select all

-- phpMyAdmin SQL Dump
-- version 5.2.1-dev+20221028.64e273f305
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Feb 15, 2023 at 11:24 AM
-- Server version: 8.0.32-0ubuntu0.20.04.2
-- PHP Version: 7.4.3-4ubuntu2.17

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `rcs2022db`
--

-- --------------------------------------------------------

--
-- Table structure for table `tblRoads`
--

CREATE TABLE `tblRoads` (
  `idRoads` int NOT NULL,
  `RoadName` varchar(45) DEFAULT NULL,
  `Parish` varchar(45) DEFAULT NULL,
  `Postcode` varchar(10) DEFAULT NULL,
  `PrivateRd` varchar(20) DEFAULT NULL,
  `AgeofEstate` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblRoads`
--
ALTER TABLE `tblRoads`
  ADD PRIMARY KEY (`idRoads`),
  ADD UNIQUE KEY `idRoads_UNIQUE` (`idRoads`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tblRoads`
--
ALTER TABLE `tblRoads`
  MODIFY `idRoads` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3578;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
tblClients

Code: Select all

-- phpMyAdmin SQL Dump
-- version 5.2.1-dev+20221028.64e273f305
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Feb 15, 2023 at 11:23 AM
-- Server version: 8.0.32-0ubuntu0.20.04.2
-- PHP Version: 7.4.3-4ubuntu2.17

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `rcs2022db`
--

-- --------------------------------------------------------

--
-- Table structure for table `tblClients`
--

CREATE TABLE `tblClients` (
  `idClients` int NOT NULL,
  `ClientName` varchar(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblClients`
--
ALTER TABLE `tblClients`
  ADD PRIMARY KEY (`idClients`),
  ADD UNIQUE KEY `idClients_UNIQUE` (`idClients`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tblClients`
--
ALTER TABLE `tblClients`
  MODIFY `idClients` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24467;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
tblApplicants

Code: Select all

-- phpMyAdmin SQL Dump
-- version 5.2.1-dev+20221028.64e273f305
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Feb 15, 2023 at 11:20 AM
-- Server version: 8.0.32-0ubuntu0.20.04.2
-- PHP Version: 7.4.3-4ubuntu2.17

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `rcs2022db`
--

-- --------------------------------------------------------

--
-- Table structure for table `tblApplicants`
--

CREATE TABLE `tblApplicants` (
  `idApplicants` int NOT NULL,
  `ApplicantName` varchar(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblApplicants`
--
ALTER TABLE `tblApplicants`
  ADD PRIMARY KEY (`idApplicants`),
  ADD UNIQUE KEY `idApplicants_UNIQUE` (`idApplicants`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tblApplicants`
--
ALTER TABLE `tblApplicants`
  MODIFY `idApplicants` int NOT NULL AUTO_INCREMENT;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
tblEnquiries

Code: Select all

-- phpMyAdmin SQL Dump
-- version 5.2.1-dev+20221028.64e273f305
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Feb 15, 2023 at 11:24 AM
-- Server version: 8.0.32-0ubuntu0.20.04.2
-- PHP Version: 7.4.3-4ubuntu2.17

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `rcs2022db`
--

-- --------------------------------------------------------

--
-- Table structure for table `tblEnquiries`
--

CREATE TABLE `tblEnquiries` (
  `idEnquiries` int NOT NULL,
  `Date` date DEFAULT NULL,
  `id_Lender` int DEFAULT NULL,
  `ApplicantName` varchar(60) DEFAULT NULL,
  `id_Properties` int DEFAULT NULL,
  `id_Agents` int DEFAULT NULL,
  `EstValue` decimal(12,2) DEFAULT NULL,
  `FlatNo` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblEnquiries`
--
ALTER TABLE `tblEnquiries`
  ADD PRIMARY KEY (`idEnquiries`),
  ADD UNIQUE KEY `idEnquiries_UNIQUE` (`idEnquiries`),
  ADD KEY `idAgents_idx` (`id_Agents`),
  ADD KEY `fk_Enquiries_Banks1_idx` (`id_Lender`),
  ADD KEY `fk_Enquiries_Properties1_idx` (`id_Properties`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tblEnquiries`
--
ALTER TABLE `tblEnquiries`
  MODIFY `idEnquiries` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=957;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `tblEnquiries`
--
ALTER TABLE `tblEnquiries`
  ADD CONSTRAINT `fk_Enquiries_Banks1` FOREIGN KEY (`id_Lender`) REFERENCES `tblLender` (`idLender`),
  ADD CONSTRAINT `fk_Enquiries_Properties1` FOREIGN KEY (`id_Properties`) REFERENCES `tblProperties` (`idProperties`),
  ADD CONSTRAINT `idAgents` FOREIGN KEY (`id_Agents`) REFERENCES `tblAgents` (`idAgents`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Re: Guidance needed creating multi-criteria search form with subform

Posted: Wed Feb 15, 2023 12:49 pm
by kev1n
Corrected JS:

Code: Select all

function nuSelectBrowse(e){
    
     const recordId   = $('#' + e.target.id).attr('data-nu-primary-key');
     
    // Refresh the iframe "Instructions List"
     let f = parent.$("#iframe_instructionslist")[0].contentWindow; 
     f.nuSetProperty('Propertyid', recordId); // Set a hashcookie
     f.nuGetBreadcrumb(); // refresh the iframe

    // Refresh the iframe "Enquiries List"
     f = parent.$("#iframe_enquirieslist")[0].contentWindow;
     f.nuSetProperty('Propertyid', recordId);
     f.nuGetBreadcrumb();

     return false;

}
PS: Always check the developer console for JS errors.

Re: Guidance needed creating multi-criteria search form with subform

Posted: Wed Feb 15, 2023 12:54 pm
by kev1n
BTW, I saw that you're using various constraints. They may cause issues when you edit data/add new data.

Re: Guidance needed creating multi-criteria search form with subform

Posted: Wed Feb 15, 2023 1:13 pm
by Keith-i
kev1n wrote: ↑Wed Feb 15, 2023 12:54 pm BTW, I saw that you're using various constraints. They may cause issues when you edit data/add new data.
Okay, not sure what these are but will look into it.Thanks for pointing it out.

Just to clarify on the new JS. On on entery it says let f = ... on the other it just says f = ... Is that correct?

Re: Guidance needed creating multi-criteria search form with subform

Posted: Wed Feb 15, 2023 1:20 pm
by kev1n
Since the variable is reused, you only need to declare it once.
All times are UTC+02:00
Page 6 of 7

Powered by phpBB® Forum Software © phpBB Limited