LightSwitch – Nested AutoCompleteBox for data entry Part 2

Overview of Article

The previous article demonstrated a method for creating a pair of nested pair of dropdown boxes.
https://dotnettim.wordpress.com/2011/03/20/lightswitch-nested-autocompletebox-for-data-entry/

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.

image
Fig1: Data schema for previous example

Compare this with a diagram of what we’ll be using today.

image

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.

http://social.msdn.microsoft.com/Forums/en-US/lightswitchgeneral/thread/d933f0db-95d0-4182-8ddc-4bd89ab18b2b/#ab45d257-67ff-4fe1-b75c-e940277e67a5 

 

Data

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:

County
ID Integer
CountyName String
Towns Town Collection
Town
ID Integer
TownName String
Customers Customer Collection
Customer
ID Int32
Firstname String
Surname String
County County
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
Fig3: Illustrations of Relationships

Here’s the additional relationship that has been added in addition to our previous post.

image

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.

image
Fig5: Illustration of Query

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. Click on the ‘Add Data Item’ button and add the ‘TownsByCounty’ query onto the screen.
  3. By default, an AutoComplete box will have been created for the ‘Towns’ field. Set the ‘Choices’ property of this to ‘TownsByCounty’
  4. Now map the ‘CountyId’ parameter of the ‘TownsByCounty’ query to the value of the County field. Click on the ‘CountyId’ Parameter for ‘TownsByCounty’.
    image
    Fig 6: Click the CountyId Query Parameter shown above
  5. On the properties for the parameter, enter ‘CustomerProperty.County.Id’ for the ‘Parameter Binding’
    image
    Fig 7: Set the CountyId Query Parameter shown above 

Conclusion

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.

image image
Fig9: Illustration of Town box showing different towns depending on County

A completed solution can be downloaded here:
http://cid-7a042700ee409d7b.office.live.com/self.aspx/LightswitchFiles/CustomerTowns2.zip

Advertisements

About dotnettim

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

5 Responses to LightSwitch – Nested AutoCompleteBox for data entry Part 2

  1. Gurvinder says:

    Very nice post.
    How to do the reverse. I mean user selects Town and County is autoselected.

  2. dotnettim says:

    Thanks Gurvinder.

    There are various ways that you can do the reverse. One possibility is to handle the _changed event on the TownId field. You can then write some code in this event to set the County and the following article contains some example syntax.

    https://dotnettim.wordpress.com/2011/03/16/lightswitch-setting-combobox-values-in-code/

  3. Gurvinder says:

    Thanks.
    I tried this using the same method mentioned in this post. And got the results. Only problem is that when the window is first opened it takes the First County as default value. Though the Town value is null. Not sure why this happened.
    Also i am a bit confused about what should be the optimized way to implement this. The method of this post or the _changed one.
    I have tried to implement by _changed in the past but not able to recall it.

  4. Michael says:

    This is incredibly helpful, thank you so much for the clear instructions :)!

  5. I have a problem with this http://puu.sh/2YaH3.png .. The information in the field “Empresa” and “Sucursal” , i follow all step by step, and i dont know why this problem appear.

    “Empresa” is like “Country”
    “Sucursal” is like “Town”

    I hope can you help me, bye, have a good day

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