Creating Cascading Drop Down Lists in Visual Studio LightSwitch

A common technique on data entry screens is using one “drop down list” (called an auto-complete box in LightSwitch) as a filter into the next. This limits the amount of choices that need to be brought down and guides the user into easily locating values. This technique is also useful if you have cascading filtered lists where the first selection filters data in the second, which filters data in the next, and so forth. LightSwitch makes this easy to do using parameterized queries and parameter binding. In this post let’s take a look a couple common scenarios.

Cascading Lists based on Multiple Tables

Let’s take an example where we have a table of States and a child table of Cities. Cities are then selected on Customers when entering them into the system. So we have one-to-many relationships between State and Cities and City and Customers. Our data model looks like this:

When the user is entering new customers we don’t want to display thousands of cities in the dropdown to choose from. Although the users can use the auto-complete box feature to locate a city, bringing down all these records affects performance. It’s better to either use a Modal Window Picker search dialog (like I showed in this article) or present the list of States first and then filter the list of Cities down based on that selection.

First we need to create a couple queries. The first will simply sort the list of States so that they show up in alphabetical order in the list. Right-click on the States table in the Solution Explorer and Add Query to open the Query Designer. Create a query called “SortedStates” that sorts on the state’s Name Ascending:

Next create a query called “CitiesByState” by right-clicking on the Cities table in the Solution Explorer and selecting Add Query again. This time we will create a parameterized query: Where the State.Id property is equal to a new parameter called Id. The Query Designer should now look like this:

Now create the Customer Detail Screen like normal. Right-click on the Screens node and select “Add Screen…”, select the Edit Details Screen template then choose Customers for the screen data. The Screen Designer opens and all the fields in the Customer entity will be in the content tree. The City field is displayed as an auto-complete box.

Next we’ll need to add a data item to our screen for tracking the selected State. We will use this value to determine the filter on the City list so that users only see cities in the selected state. Click “Add Data Item” and add a Local Property of type State called SelectedState.

Next, drag the SelectedState over onto the content tree above the City. LightSwitch will automatically create an auto-complete box control for you.

Since we want to display the states sorted, next add the SortedStates query to the screen. Click “Add Data Item” again, this time select Query and choose SortedStates.

Then select the SelectedState auto-complete box in the content tree and on the Properties window, set the Choices property to SortedStates.

Next, add the CitiesByState query to the screen and set that as the Choices property of the Cities auto-complete box. Again, click “Add Data Item” and choose the CitiesByState query.

Then select the Cities auto-complete box and set the Choices property to this query.

Lastly we need to hook up the parameter binding. Select the Id parameter of the CitiesByState query and in the properties window set the Parameter Binding to SelectedState.Id. Once you do this a grey arrow on the left side will indicate the binding.

Once you set the value of a query parameter, LightSwitch will automatically execute the query for you so you don’t need to write any code for this. Hit F5 and see what you get. Notice that the Cities drop down list is empty until you select a State at which point it feeds the CitiesByState query and executes it. Also notice that if you make a city selection and then change the state, the selection is still displayed correctly, it doesn’t disappear. Just keep in mind that anytime a user changes the state, the city query is re-executed against the server.

One additional thing that you might want to do is to initially display the state to which the city belongs. As it is, the Selected State comes up blank when the screen is opened. This is because it is bound to a screen property which is not backed by data. However we can easily set the initial value of the SelectedState in code. Back in the Screen Designer drop down the “Write Code” button at the top right and select the InitializeDataWorkspace method and write the following:

 Private Sub CustomerDetail_InitializeDataWorkspace(saveChangesTo As List(Of Microsoft.LightSwitch.IDataService))
    ' Write your code here.
   If Me.Customer.City IsNot Nothing Then<br>        Me.SelectedState = Me.Customer.City.State    
End If
End Sub

Now when you run the screen again, the Selected State will be displayed.

Cascading Lists Based on a Single Table

Another option is to create cascading lists based on a single table. For instance say we do not want to have a State table at all. Instead it may make more sense to store the State on the City. So our data model could be simplified by having just a City table related to many Customers.

This time when we create a parameterized query called CitesByState, we’ll set it up Where the State is equal to a new parameter called State.

On the screen, select “Add Data Item” to add the CitiesByState to the screen and set it as the Choices property of the City auto-complete box just like before.

This time, however, the State is the query parameter we need to bind. Add a string screen property to the screen to hold the selected state. Click “Add Data Item” again, add a required Local Property of type String and name it SelectedState.

Drag the SelectedState onto the content tree above the City. This time LightSwitch will create a textbox for us since this is just a local string property.

Finally, we need to set up the query parameter binding. Select the State query parameter and in the properties window set the Parameter Binding to SelectedState.

In order to set the SelectedState when the screen opens, the same code as before will work. Now when we run this, you will see a textbox that will filter the list of cities.

However, this may not be exactly what we want. If the user has a free-form text field then they could mistype a state code and the query would return no results. It would be better to present the states in a auto-complete box like before. Close the application and open the Screen Designer again. Select the SelectedState screen property. Notice in the properties window you can display this as a static list of values by creating a Choice List.

Enter the states that the user should select from and then run the application again. Now we get an auto-complete box like before. However, this approach leaves us having to define the choice list of states on every screen we want this functionality. The first approach using a State table solves this issue but there is also one other approach we could take to avoid having to create a separate table.

Using a Choice List on the Entity

We could improve this situation by defining the choice list of states on the Customer entity. Then we would only have to define the lists of states in one place. Create a State property on the Customer using the Data Designer and on the properties window select Choice List and specify the list of states there.

Now add a new detail screen for Customer and you will see the State and City properties are set up as auto-complete boxes. Next add the CitiesByState query to the screen by clicking “Add Data Item” like before. We can use the same CitiesByState query as the previous example. Select the City auto-complete box and in the properties window set the Choices property to CitiesByState like before.

The difference is the query parameter binding. Select the State query parameter on the left and in the properties window set the Parameter Binding to Customer.State.

With this technique, you also do not need to write any code to set the initial selected State because we are now storing that value on the Customer record. Run the application again and the screen should behave the same. The only difference now is that we are storing the State on the Customer instead of a separate table.

This technique is the simplest to set up, so if you have a lot of Customer screens, then this may be the best choice for you if you have a static list of values like States. However if you have dynamic list of values then it’s better to store these in a separate table like the first technique showed.

For more information on filtering data and configuring lists see:

• How to Create a Multi-Column Auto-Complete Drop-down Box in LightSwitch
• How to Allow Adding of Data to an Auto-Complete Drop-down Box in LightSwitch
• Filtering Lookup Lists with Large Amounts of Data on Data Entry Screens

Enjoy!

Comments

• Anonymous
  January 12, 2012
  Thank you very much Beth!

• Anonymous
  January 12, 2012
  We could not make advanced LS apps. without Beth Massi

• Anonymous
  January 12, 2012
  Hello, I am running into a slight issue with the cascading dropdowns in terms of the City not refreshing or being cleared when the State dropdown selection is changed.  Here is the scenario

1. I create the customer reord
2. I intially select Washington as the State
3. I select Seattle as the city in Washington
4. Before saving the data, I change the state to Texas.
5. The value of the City still reflects the Seattle choice I made earlier.
6. When I save the record I have the Customer with State = Texas / City = Seattle I assume the best practice would be to create code to clear the city value in the SelectedState_Changed event but I am not quite sure what objects to access to change / clear the City dropdown list item.  Any help you can provide would be appreciated. Thanks Don

• Anonymous
  January 13, 2012
  The comment has been removed

• Anonymous
  January 15, 2012
  wow

• Anonymous
  January 18, 2012
  Thanks very much Beth. This is a great help and very well explained

• Anonymous
  January 26, 2012
  Hi Thanks for this. I am able to filter using a test scenario. However, my application is a little more complex and I am running into som problems. In the Invoice Entry, I have Order Header and Order Detail. When I select a customer, I want to be able to filter only their order. When the order header is selected, I want to display only those order details. I tried the above and was able to filter customers to order header from the SelectCustomer query but I am having some difficulty getting the order headers to order details to filter and assigning the SelectedCustomer to Customer. I am coding in C#. Any assistance would be most appreciated Many thanks

• Anonymous
  February 04, 2012
  Hi Beth, thank you very much, this article is really helpful, but it would be really nice to be able to "catch" the autocompletebox_change event. I would like to enable/disable (or show/hide) some controls according to picked choice, but I still cannot find the way how to do it. In vb.net such task takes me like 10 seconds, in LS I've spent 2 days and still struggling, so any help would be really appreciated. Thanks in advance:-).

• Anonymous
  April 05, 2012
  Hi Beth I´m using your solution to create cascading dropdowns based on a parent LOV table(Site) and a child LOV(City) and it works nicely, however i´m loosing the bold label on my parent cascading dropdowns, in your case the dropdown based on the local property SelectedSite. The SelectedSite property is set to IsRequired and should therefore trigger the bold label, or??? what can I do to get the same bold label I have on the child dropdown? Many thanks :)

• Anonymous
  August 07, 2012
  I wonder if anyone checks this anymore. Is it possible to do this but in a grid instead of with autocomplete boxes?  For example, I'd like to have the user select from a dropdown in column A, which filters the options in the dropdown in column B. Thanks to anyone who can help.

• Anonymous
  October 10, 2012
  Hi, Thank you very much for this tutorial. One more question though. It is possible to add buttons in a that drop down, like is the Refresh button in the last image. Let's say I want to add a new button there, near Refresh. Is it possible? I'm looking forward for an answer, please.

• Anonymous
  October 11, 2012
  The comment has been removed

• Anonymous
  December 14, 2012
  Awesome article! Thanks for providing it. This filtering works when adding a record via the table..is there any way to get cascading to work when adding through the [Add..] button in the table's header? The choice list doesn't seem to affect both as I hoped it would.

• Anonymous
  September 05, 2013
  Thank you for the article. In HTML LightSwitch, how can I initialise the dropdown values for the edit screen. I see for the Silverlight LS you have Me.SelectedState = Me.Customer.City.State. On the HTML version, for the dropdown, I get methods such getSeleectedState and setSelectedState and I am stuck. Please help.

• Anonymous
  September 26, 2013
  <a href="www.kettic.com/.../dropdown_list_auto_complete.shtml">DropDown List Control</a>

• Anonymous
  January 16, 2014
  www.kettic.com/.../dropdown_list.shtml

• Anonymous
  April 25, 2014
  The comment has been removed

• Anonymous
  August 07, 2014
  I’ve tried my best to follow this tutorial. I’m running into an issue at the part where I need to bind the CitiesByState ID parameter with the SelectedState.Id parameter. Here are the steps i took. (I've recreated my project from scratch multiple times following these steps)

1. Created a data model to match yours.

2. Created SortedStates sort query on the States table

3. Created CitiesByState filter on the Cities table

4. Created a Customers Details Screen

5. Added the DataItem State (SelectedState)

6. Dragged the SelectedState on the content tree.

7. Added DataItem query (SortedStates)

8. Added SortedStates query as the SelectedState “Choices”

9. Added DataItem query (CitiesByState)

10. Added CitiesByState query as the City “Choices” Here is where I run into trouble, I click on the CitiesByState query parameter Id. Then I click on the Parameter Binding in the Properties pane. My options are: Customer, CustomerId, SelectedState, SortedStates, and CitiesByState.  I don’t see SelectedState.ID. I’ve tried to choose SelectedState as my param binding but I get the error, “Cannot assign type ‘State’ to ‘Int32?’ Can someone explain what I’m doing wrong? I’m using VS 2012 pro Thank you! -Tech

    • Anonymous
      March 02, 2016
      I know this is a long shot since you posted so long ago...but did you find an answer for this?? I have the exact same issue.Pl;ease email me if so. Thank you!!

• Anonymous
  February 01, 2015
  How to perform the same operation in 'Editable Grid Screen'. Editable grid show the same state for all the rows.

• Anonymous
  July 29, 2015
  Hey beth i have this issue. In HTML5 client app, i want a field to be autofilled whenever i open a screen for " Add Edit view". Instead of always filling in the field is autofilled with a default value. How can i do that. Am using VB.Net

• Anonymous
  February 07, 2016
  Hello, did someone figure it out the best way to cascade in lightswitch? I made Beth's example but I am still facing two issues. 1) The actual state field is not recorded when selecting a Select State dropdown list so it makes the user selecting twice and 2) Could not make a State => Region => City hierarchical cascade as suggested by Luc. Lightswitch is awesome, hopefully it will evolve and never give up. Thank you.