Overview of Article
The previous article demonstrated a method for creating a pair of nested pair of dropdown boxes.
A slight problem with nested controls is that the process will vary depending upon the schema of the database. The following article illustrates how to create a pair of nested dropdown boxes for a variation of what we had before.
Here’s an illustration of how the data looked in the previous post.
Compare this with a diagram of what we’ll be using today.
Fig2: Data schema for this post
Notice how the Customer table has been amended to include a County column. In the previous post, this did not exist which meant that we had to create a County property on our form. Now that a County field exists in the Customer table, this local property is no longer required.
Note: If you select a County and subsequently clear the contents of the County AutoCompleteBox, the Town AutoCompleteBox will not automatically clear itself. This is related to a bug which should hopefully be resolved by RTM. In the meantime, there is some code to work around this issue in the following thread.
The example uses the same data from the previous post but we’ll be adding an additional relationship between the Customer and County tables.
Here’s an overview of our data:
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.
Here’s the additional relationship that has been added in addition to our previous post.
Fig4: Additional relationship between customer and County
In order for us to filter the towns based upon county, we need to a parameterised query to return the towns for any given county. A query called ‘TownsByCounty’ will therefore be created that will filter Town by CountyID using a parameter called ‘CountyId’. The query will look like this.
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
- 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 value of the County field. Click on the ‘CountyId’ Parameter for ‘TownsByCounty’.
Fig 6: Click the CountyId Query Parameter shown above
- On the properties for the parameter, enter ‘CustomerProperty.County.Id’ for the ‘Parameter Binding’
Fig 7: Set the CountyId Query Parameter shown above
Run the application and open the Create New Customer form. You can now use the county drop down to select a County and the Town dropdown box will now be filtered based upon the selected country. Compared to the previous example, the screen coding is simplified due the different schema that we have.
A completed solution can be downloaded here: