Sharing the goodness…
Beth Massi is a Senior Program Manager on the Visual Studio team at Microsoft and a community champion for business application developers. Learn more about Beth.
More videos »
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.
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
Me.SelectedState = Me.Customer.City.State
Now when you run the screen again, the Selected State will be displayed.
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.
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:
Thank you very much Beth!
We could not make advanced LS apps. without Beth Massi
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.
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
Thanks very much Beth. This is a great help and very well explained
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
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:-).
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.
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.