dbFront will automatically create cascading dropdowns when you have multi-key relationships. A multi-key relationship is any single relationship that involves multiple fields joining two tables.
- ProductID (PK)
- Product Name
- ProductID (PK and (FK => ProductTable))
- ProductSpecificationID (PK)
- InvoiceID (PK)
- ProductID (FK => ProductSpecification)
- ProductSpecificationID ( (FK => ProductSpecification))
It is important that you create the relationship from InvoiceTable to ProductSpecification as follows:
ALTER TABLE [InvoiceTable]
ADD CONSTRAINT [FK_InvoiceTable_to_ProductSpecification]
REFERENCES [ProductSpecification] ([ProductID],[ProductSpecificationID])
Because the fields [ProductID] and [ProductSpecificationID] are part of a single relationship, dbFront will manage them together instead of treating the fields as individual lookups.
The result of this configuration is that InvoiceTable will have two dropdowns. dbFront will realize that the ProductID dropdown should be a view on the ProductTable, and once you fill out that dropdown it will allow you to pick from the second dropdown on ProductSpecificationID which will be filtered by the previous dropdown to only show matching records.
As an added note: The dropdowns in dbFront are searchable and can handle 1 million + rows.