Creating Cascading Drop Down Lists in Visual Studio LightSwitch

Creating Cascading Drop Down Lists in Visual Studio LightSwitch

Rate This
  • Comments 17

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:

image

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:

image

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:

image

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.

image

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.

image

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

image

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.

image

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

image

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.

image

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

image

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.

image

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.

image

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

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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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:

Enjoy!

Leave a Comment
  • Please add 4 and 8 and type the answer here:
  • Post
  • Thank you very much Beth!

  • We could not make advanced LS apps. without Beth Massi

  • 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

  • Hi DS,

    This is a feature of the Auto-Complete box, it will maintan the user's previous selection. However, if you want to clear the City when the State changes you do that in the Data Designer and handle the State_Changed method on the Customer entity:

    Public Class Customer

       Private Sub State_Changed()

           'If the state changes make sure to clear the city selection

           If Me.City IsNot Nothing Then

               If Me.City.State <> Me.State Then

                   Me.City = Nothing

               End If

           End If

       End Sub

    End Class

    HTH,

    -Beth

  • wow

  • Thanks very much Beth. This is a great help and very well explained

  • 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

  • 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:-).

  • 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 :)

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

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

  • @Liam - See this referenced post on an alternative. The filtering doesn't work directly inside the grid but you can do it with the selected item: blogs.msdn.com/.../filtering-lookup-lists-with-large-amounts-of-data-on-data-entry-screens.aspx

    @John - you can't add buttons inside the control but you can add them next to it. See this post: blogs.msdn.com/.../how-to-allow-adding-of-data-to-an-auto-complete-drop-down-box-in-lightswitch.aspx

    If you want them inside you'll need to create a custom control fro that.

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

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

  • <a href="www.kettic.com/.../dropdown_list_auto_complete.shtml">DropDown List Control</a>

Page 1 of 2 (17 items) 12