Guidance needed creating multi-criteria search form with subform

[kev1n]

Could you also post the db schema for the 3 tables (without data)?

[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.

[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

[Keith-i]

Thanks for the heads up on exporting empty tables. Would you still like the schema dump?

[kev1n]

Yes, please. This allows me to take a look at the forms without much effort.

[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 */;

[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.

[kev1n]

BTW, I saw that you're using various constraints. They may cause issues when you edit data/add new data.

[Keith-i]

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?

[kev1n]

Since the variable is reused, you only need to declare it once.