Make, Model, Year (Dependent dropdown lists)

The Make-Model-Year customization displays a set of dropdown lists that update themselves based on the values selected in other dropdown lists.  For example, you might first select a vehicle ‘make’ via a dropdown list, such as ‘Toyota’.  Once selected, the ‘model’ dropdown list is updated to display only those models belonging to Toyota, such as Camry, Prius and Celica.  Similarly, selecting the model (Camry) displays the appropriate model years in the ‘year’ dropdown list.

Let’s first select “Mercedes” as the Make from the Make dropdown.

Once the make is selected, the second dropdown shows the specific models belonging to the selected make, in our case “Mercedes”.

And when the application user selects the make and the model, the available years are shown.

Select products: A two-level dependent dropdown list example

This example shows two dropdown lists for CategoryID and ProductID where selecting CategoryID updated ProductID.  Select the “Beverages” category from the Category dropdown as shown below:

Once the “Beverages” category is selected, the second dropdown shows the products belonging to the “Beverages” category:

Iron Speed Designer automatically determines the interdependency between controls.

Step 1:  In the Layout Editor, create a CategoryID dropdown list by dragging a DropDownList control from the ToolBox on to page.

Step 2:  Rename the control to “CategoryID”.

 

Step 3:  Populate the CategoryID dropdown list with the different product categories in our database using the “Populate From Database” event.  

Step 3a:  In the Property Sheet, Queries section, select the appropriate query.

Step 3b:  Open the Query Wizard (Edit…) to create this query:

select CategoryID from Categories

Step 3c:  In the Query Wizard, select the Categories table in the “FROM” step and “CategoryID” in the “SELECT” step.  Since we want to display category names instead of the CategoryIDs, write CategoryName in the Display As section of the Query Wizard.

Step 3d:  Click ‘Finish’ and close the Query Wizard’.  The query is displayed in the Property Sheet, Actions group.

 

Step 4:  Populate ProductID dropdown list with all the ProductsIDs from the Products table which have their CategoryID equal to the one selected in Category dropdown. This can be done by setting ‘Depends on’ in the Property Sheet, Controls section. Using this property you do not need to manually add the WHERE clause below.

Step 4a:  Select the ProductID control in the Layout Editor, open the Property Sheet, Queries section,  and select the appropriate query.

Step 4b:  Open the Query Wizard (Edit…) and add this WHERE clause:

SELECT ProductID

FROM Products

WHERE Products_.CategoryID = Order_DetailsRecordControl.CategoryID.selectedvalue

Step 4c:  Enter this formula in the Display As section to display the product names rather than the ProductID values:

= ProductName

Step 4d:  Click ‘Finish’ and close the Query Wizard.  The resulting query is displayed in the Property Sheet, Actions group.

 

Step 5: Build and run your application.

See Also

Common Formula Examples