Overview of Article
Very often, it’s necessary to filter the contents of a drop down box based upon the value of another drop down box. Examples of such pairs could include States and Cities, car manufacturers and car models, departments and employees.
In the following example, a customer data entry screen will be created. The screen includes a AutoCompleteBox that allows you to select a county. After selecting a county, the town AutoCompleteBox is filtered to show only those towns that are in the selected county.
The example uses the the Customer and Town tables from the earlier post here. In addition to the Town table, we’ll add a County table and create a ‘1-to-Many’ relationship between County and Town.
Here are the schemas of the tables:
Here are the example rows which have been added into the Town and County tables:
|4||2||Henley on Thames|
For additional info, here are the illustrations of our relationships.
In order for us to filter the towns based upon county, we need to create a parameterised query to return the towns for any given county. A query called ‘TownsByCounty’ will therefore be created that filters Town by CountyID using a parameter called ‘CountyId’. The query will look like this.
Edit 15 Aug 2011: This query is created against the ‘Town’ table. To create a query, right click on the ‘Town’ table in Solution Explorer and select the ‘Add Query’ option.
First of all, create some data entry screens to enter in some Counties and Towns.
Now onto the Customer entry screen…..
- Create a ‘Data Entry Screen’ based upon the Customer table
- Since the Customer table doesn’t contain a County column, we need to add a stand-alone AutoCompleteBox to filter the towns. Click on the ‘Add Data Item’ button and add the ‘Counties – County(All)’ query onto the screen.
Fig3: Adding the ‘Counties’ query
- Click on the ‘Add Data Item’ button and create a new local property called ‘prpSelectedCounty’
Fig4: Adding the County property
- Drag ‘prpSelectedCounty’ onto the screen designer above ‘Town’ in order to create a AutoCompleteBox
Fig5: Here’s how the screen should look
- In the AutoCompleteBox which is created, we need to populate the control with a list of counties. To do this, set the ‘Choices’ property to ‘Counties’
Fig 6: Set the ‘Choices’ attribute to ‘Countries’
- Click on the ‘Add Data Item’ button and add the ‘TownsByCounty’ query onto the screen.
- By default, an AutoComplete box will have been created for the ‘Towns’ field. Set the ‘Choices’ property of this to ‘TownsByCounty’
- Now map the ‘CountyId’ parameter of the ‘TownsByCounty’ query to the prpSelectedCounty’ value. Click on the ‘CountyId’ Parameter for ‘TownsByCounty’.
Fig 7: Click the CountyId Query Parameter shown above
- On the properties for the parameter, enter ‘prpSelectedCounty.ID’ for the ‘Parameter Binding’
Fig 8: Click the CountyId Query Parameter shown above
Run the application and open the Create New Customer form. You can now use the county AutoCompleteBox to select a county and the Town dropdown box will now be filtered based upon the selected country.