Cascading Lookups/Dropdowns

dbFront automatically creates cascading dropdowns when you have multi-key relationships chained to simpler relationships.

A multi-key or composite relationship is any single relationship that involves multiple fields joining two tables. If dbFront can find a simpler relationship on the parent table involving a subset of those fields then it will create a cascading dropdown.

Starting Table Structures

ProductTable {
ProductID (PK)
  Product Name
}

ProductModel {
  ProductID (PK and (FK => ProductTable))
  ProductModelID (PK)
  Color
  Size
}

InvoiceTable {
  InvoiceID (PK)
  ProductID (FK => ProductModel)
  ProductModelID ( (FK => ProductModel))
  Quantity
  Price
}

First Cascading Dropdown

The first step is to create a simple relationship from ProductModel to ProductTable for the ProductID column. This will cause dbFront to show a simple lookup on the ProductModel table for the column ProductID.

ALTER TABLE ProductModel
    ADD CONSTRAINT FK_ProductModel_to_Product
    FOREIGN KEY(ProductID)
    REFERENCES ProductTable (ProductID);

The second step is to create a relationship from InvoiceTable to ProductModel as a composite relationship (multiple fields).

/* NOTE: Composite relationships require a composite unique index or primary key. */
CREATE UNIQUE INDEX [IX_ProductModel]
    ON [ProductModel] ( [ProductID] ASC, [ProductModelID] ASC);

ALTER TABLE InvoiceTable
ADD CONSTRAINT FK_InvoiceTable_to_ProductModel
    FOREIGN KEY(ProductID, ProductModelID)
    REFERENCES ProductModel (ProductID, ProductModelID);

Because the fields ProductID and ProductModelID 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 follow the relationship from the InvoiceTable through ProductModel to the ProductTable and realize that the ProductID dropdown should be a lookup on the ProductTable. Once you fill out the ProductID dropdown, dbFront will allow you to pick from the second dropdown on ProductModelID which will be filtered by the previous dropdown to only show matching records.

Additional Notes

dbFront uses the column order in the composite relationship to determine how the cascade flows. Therefore it expects form fields to appear in the same sequence.
Selecting a higher-order column automatically resets the dependent dropdowns.

Additional Cascading Dropdowns

Additional Cascading Dropdowns can be added by extending the relationship chain. Examples of multilevel cascading dropdowns are:

Make - Model - Color,
Category - Products - Vendor - Model,
Subdivision location from combined:
- [Country - Region - City]
- [Country - Region - Electoral District]

Troubleshooting

For a cascading dropdown to work, dbFront must be able to travel up a relationship chain and arrive at the topmost single-field lookup.
To see what dbFront is thinking open the lookup field properties and open [Lookup Details]. Under the [Lookup Details] tab you will find another tab named [Lookup Source] which displays the relationships that dbFront considers relevant and which specific relationship it selected.

Server Outage Notice: dbFront.com will be transfering to a new Server on Friday 25th @ 7pm MST