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.

image image
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.

image
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.
    image
    Fig3: Adding the ‘Counties’ query

  3. Click on the ‘Add Data Item’ button and create a new local property called ‘prpSelectedCounty’
    image
    Fig4: Adding the County property

  4. Drag ‘prpSelectedCounty’ onto the screen designer above ‘Town’ in order to create a AutoCompleteBox
    image
    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’
    image
    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’.
    image
    Fig 7: Click the CountyId Query Parameter shown above

  9. On the properties for the parameter, enter ‘prpSelectedCounty.ID’ for the ‘Parameter Binding’
    image
    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.

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

 

About these ads

About dotnettim

Tim Leung is a Microsoft .Net / SQL Server developer based in England.
This entry was posted in LightSwitch. Bookmark the permalink.

22 Responses to LightSwitch – Nested AutoCompleteBox for data entry

  1. Sam Johnson says:

    How do you then map the prpSelectedCountry value to the Country field for the Customer entity? I tried your example and I get a validation error because the Country field isn’t being populated by the prpSelectedCountry dropdown. Maybe I missed a step?…

    • dotnettim says:

      Hi Sam,

      In this example, there isn’t a Customer.County field so I think you’ve created a slightly different database schema.

      The prpSelectedCounty property was created because the Customer.County field doesn’t exist. If Customer.County is a valid field in your database, you can simply add that field onto your form instead of carrying out step 4.

      If it helps, you can download a working solution here:

      http://cid-7a042700ee409d7b.office.live.com/self.aspx/LightswitchFiles/CustomerTowns.zip

      Creating nested dropdown boxes will depend largely on the schema of your data. If I get a chance, I’ll create an updated post explaining how to do this in instances where County is a field in the Customer table.

      Tim

      • Can you please create that example you are talking about above? I’ve a case where I have three tables: fuels, units, and emissions table. The emissions table contains both fuels and units as properties, but I need the units to be filtered based on the selected fuel.
        Thanks.

  2. Hi. Question here:

    Regarding:
    “A query called ‘TownsByCounty’ will therefore be created that filters Town by CountyID using a parameter called ‘CountyId’. ”

    …it isn’t clear to the beginner (me) which table you’re creating this query against.
    Did you mean:

    “…created /under the Customers table/ that filters Town by CountyID using a parameter called ‘CountyId’. ”

    …or at some other point? I’m having some difficulty reconciling how you weld a parameterised query to a screen element.

  3. Ok, I think I have it (please correct me):

    Assume [Table A] contains a foreign key pointing to the primary key in [Table B].

    If you want to filter [Table A] such that only those rows from [Table A] are shown that relate to a choice selected in an autocomplete box for [Table B], you create your parameterised query against [Table A].

    Is this generally correct?

    Then you follow up by clicking on the query parameter under your query as shown on the left hand pane, and set your parameter binding in the Properties pane using IntelliSense.

    I think I got this right, but not sure if the solution is complete…

  4. dotnettim says:

    Hi Kelley,
    Sorry for the delay in replying – in response to your question, yes you’re right.
    Mapping what you have to the example above, [TableA] would be the town table and [Table B] would be the county table. You’re right again in terms of how you set the parameter binding in the left hand pane.
    Just to add a bit more clarity for others, the query in figure 2 is created on the town table.
    Tim

  5. Rashmi Ranjan Panigrahi says:

    Hi Tim

    I used your “Nested AutoCompleteBox” in my application.
    I successfully run the feature, but i cant able to find Country data in my database.

    How can i get that.

  6. dotnettim says:

    Hi Rashmi,
    Could you explain what you mean by being unable to find the country data in your database?
    Tim

  7. Abdullah says:

    Hi Tim
    Thanks for solving my problem :)
    I am learning lightswitch now a days,,,

  8. Marden says:

    How clearing the contents of the second combobox if the User select another value in the first combobox

  9. Faizal says:

    Same question as marden

  10. Pingback: Nested Autocomplete Box « Aaron's Tech Stuff

  11. What’s up everyone, it’s my first pay a visit at this site, and paragraph is actually fruitful for me, keep up posting these articles.

  12. Penny Lee says:

    I’m really impressed with your writing skills as well as with the layout on your weblog. Is this a paid theme or did you customize it yourself? Either way keep up the excellent quality writing, it is rare to see a great blog like this one today.

  13. dotnettim says:

    Hi Penny, thanks for your kind comment :) This blog just uses a standard theme with some custom CSS to tidy up the fonts and layout.

  14. My partner and I stumbled over here different website
    and thought I should check things out. I like what I see so
    now i’m following you. Look forward to looking over your web page for a second time.

  15. Thank you. You made my day!

  16. prabhathk says:

    When I work through number 6 and 7, it says auto complete is generated by default. But what i get is a Data grid. But when i continue with Datagrid, it works
    It appears as a one row data grid.
    Please help, I am still learning lightswitch..

  17. pritesh says:

    Hi,

    Can you help me out here.
    If I want to make cascading of 3 autocompleteboxes.
    1. BusinessUnit
    2. Division
    3. Team

    How can we achieve that?

  18. web site says:

    It’s remarkable in support of me to have a web site, which is helpful for my experience. thanks admin

  19. To the proper, then to the left; up and down; and all points in among.

  20. Challenging disks and strong-state drives are used for internal storage.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s