LightSwitch - Nested AutoCompleteBox for data entry

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.

Data

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:

County
ID	Integer
CountyName	String
Towns	Town Collection

Town
ID	Integer
TownName	String
Customers	Customer Collection

Customer
ID	Int32
Firstname	String
Surname	String
Town	Town

Here are the example rows which have been added into the Town and County tables:

Counties
ID	CountyName
1	Berkshire
2	Oxfordshire

Towns
ID	CountyID	TownName
1	1	Reading
2	1	Bracknell
3	2	Oxford
4	2	Henley on Thames

For additional info, here are the illustrations of our relationships.

 
Fig1: Illustrations of 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.

Fig2: Illustration of Query

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.

Screens

First of all, create some data entry screens to enter in some Counties and Towns.

Now onto the Customer entry screen…..

1. Create a ‘Data Entry Screen’ based upon the Customer table
2. 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

3. Click on the ‘Add Data Item’ button and create a new local property called ‘prpSelectedCounty’
   
   Fig4: Adding the County property
   
   
4. Drag ‘prpSelectedCounty’ onto the screen designer above ‘Town’ in order to create a AutoCompleteBox
   
   Fig5: Here’s how the screen should look

5. 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’
   
   
6. Click on the ‘Add Data Item’ button and add the ‘TownsByCounty’ query onto the screen.
7. By default, an AutoComplete box will have been created for the ‘Towns’ field. Set the ‘Choices’ property of this to ‘TownsByCounty’
8. 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

9. On the properties for the parameter, enter ‘prpSelectedCounty.ID’ for the ‘Parameter Binding’
   
   Fig 8: Click the CountyId Query Parameter shown above 

Conclusion

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.

 
Fig9: Illustration of town drop down box showing different towns depending on County

 

Technorati Tags: LightSwitch,Nested,AutoCompleteBox,ComboBox,Dropdown

About these ads