How to Use a Picker as a Filter in your LightSwitch apps

I’ve gotten more than a few questions over the last couple months on how to do this common pattern for filtering data based on a picker. For instance, I have a table of customers and I want to pick from a list and then pull up their details. Or say I have a list of Customers and I want to see all the related Orders for them.

Honestly, I thought I already had a blog post on how to do this somewhere -- but if I can’t find it then you probably can’t either! The steps are simple and you don’t need to write any code, so here it goes…

Filtering Data in the Same Table

Let’s say we have a Customer table and we want a screen where the user should select the customer in a picker before the rest of the fields are displayed to them. Here are the steps:

1 – Create a Query that retrieves the customer by ID 

On the Customers table in the Solution Explorer, right-click to Add a Query, for this example I’ll name it CustomerByID. Add a filter Where ID equals, select parameter, then select Add New to create a new query parameter named Id.

Note that you can select the parameter and set whether it is optional in the properties window. If you do that then all the customer details will be displayed when the screen comes up. If you leave it required, then the user must select a customer to see the details.

2- Create a screen using the query

Regardless of what client you are using (Silverlight or HTML) the technique here is similar, just the template varies. Right-click on your client node and Add New Screen…

Select the CustomerByID as the Screen Data. Choose the Browse template if you’re using the HTML client. If using the Silverlight client, just pick any list screen like the Search screen.

Select the CustomerId property in the view model and change the name to Customer in the properties window. Notice that your view model is set up automatically for you where the Customer property is really a Customer entity and its Id is data bound to the CustomerByID Query’s Id parameter (as indicated by the arrow when you select the Customer.Id item in the view model). The CustomerByID query will also be auto executed automatically as indicated in the properties window. That means the query will fire when all the required parameters are entered, in this case, the Customer is selected.

Next change the Customer control from Summary to a Details Modal Picker. If you’re using the Silverlight client this will already be set to an auto-complete box but you can also choose a Modal Window Picker if you like.

3- Run it!

When the screen comes up, the user will have to choose a customer before their details are displayed.

HTML Client:

Silverlight Client:

Note that if you have a lot of potential customers in the system, I recommend you provide a filter based on field(s) and allow users to simply type in the parameters (like name, phone, etc.)  For the Silverlight client you can use a Modal Window Picker instead which provides a search automatically. See:Filtering Lookup Lists with Large Amounts of Data on Data Entry Screens

Filtering Data Across Relationships

What if we want to use a filter across relationships? For instance say we have a one-to-many relationship from Customer to Orders and we want to display all the customer’s related orders when we select a customer in a picker. The technique is almost exactly the same, it’s just the query that’s different.

1 – Create a Query that retrieves the Orders by Customer ID 

On the Orders table in the Solution Explorer, right-click to Add a Query, for this example I’ll name it OrdersByCustomer. Add a filter Where Customer.ID equals, select parameter, then select Add New to create a new query parameter named Id.

2- Create a screen using the query

Use the same steps as above to add the query to a Browse screen, then change the name of the OrderId property in your view model to Customer. (The reason why LightSwitch picked OrderId for the name of the property is because we created a query based on “Orders” and named our parameter “Id”. If we had named the parameter to CustomerId then we would see OrderCustomerId in the screen’s view model. I digress…).

Finally change the Customer control in your content tree from a Summary control to a Details Modal Picker. Make sure you pick the outer control and not the content inside the control.)

Tip: If you want to display more than just the Summary property in the Details Modal Picker, change the content control inside to a Rows layout and select the fields you want.

3 – Run it!

Run it and same as before, users will have to select a customer before the query will execute. Once you select a customer, now the related orders will be displayed.

HTML Client:

Silverlight Client:

Wrap Up

Those are a couple variations on how you can use the common pattern of using pickers on your screens to filter data how you want. There are three key pieces -- the data model (easy), the query design (pretty easy), and the screen designer (medium). The screen designer definitely has the most knobs on it out of all the LightSwitch designers. It takes practice and exploration, trial and error, but hopefully not too much pain and suffering ;-) I hope I showed you how easy this particular pattern can be to set up.

Enjoy!

Comments

• Anonymous
  August 29, 2013
  Just what I was looking for! Thanks.

• Anonymous
  August 30, 2013
  These one it's ease ;) but... i would like to filter with options on Choice List of a Entity, for example INVOICE pending. This option is on a Integer Entity named Estatus on: 1.- Pending 2.- Refund 3,.---- Can you help me? Thanks!!

• Anonymous
  August 30, 2013
  Hi Ivan, Sure. Create your query with a filter on the Integer field you want to use on the invoice, say EStatus. Create the screen based on that query. The screen designer will automatically create a Textbox for the  InvoiceEstatus property it added to the screen for you. Select that property and in the properties window, you will see a "Choice List" link. There you can fill in the values you want the user to see as the choices for the filter. HTH, -Beth

• Anonymous
  September 06, 2013
  hello- small variation...but i can't quite get it.  using the same data/example and silverlight client.... how can i show all the customers in a list on the left of a screen then - when a customer is selected from the list, the right side of the screen refreshes (runs query) to just show the selected customers order(s)? thanks in advance!

• Anonymous
  September 06, 2013
  @ihayes916 - Just make your parameter optional in the Query designer. Select the parameter and in the properties windows, check "Is Optional" "Note that you can select the parameter and set whether it is optional in the properties window. If you do that then all the customer details will be displayed when the screen comes up. If you leave it required, then the user must select a customer to see the details. " HTH, -Beth

• Anonymous
  September 11, 2013
  Thanks Beth Based on the above is there a way to filter for Customers who have NO Orders (Silverlight)? Really appreciate your clear posts :)

• Anonymous
  September 11, 2013
  Sure, the customer picker above can come from a query as well. You can set that using the Choices property on the picker. Change if from "Auto" to your query that filters the customers how you like. In your case, you'll have to write a simple LINQ query in the PreProcessQuery method in order to work with the collection. Something like query = From customer In query Where Not customer.Orders.Any() HTH, -Beth

• Anonymous
  November 05, 2013
  Hi, I have been trying all day to get this type of filtering working but each time I get stuck at the same point. Quoting from your example above "Notice that your view model is set up automatically for you where the Customer property is really a Customer entity and its Id is data bound to the CustomerByID Query’s Id parameter (as indicated by the arrow when you select the Customer.Id item in the view model). " this doesn't happen for me, at least in my VS2012. When I create a query based on an entity (with a parameter as above) and then create a screen based on the query, what I don't see is the parent entity linked to the query. So in your screenshot below the quote I don't see Customer linked to CustomerById. If I try to add the data source manually it gets populated as a separate entity with its selected item property. Therefore there is no collection to work with and no modal picker or even a lookup control to work with. Unfortunately a scout around the web i've been unable to find any working example like yours. Perhaps this is a new VS2013 feature?

• Anonymous
  November 05, 2013
  Hi Neil, IIRC, in VS2012 you have to add the Customer property to the screen manually. Create the screen based on the query, then select "Add Data Item" in the Screen Designer. Select Property of type Customer. Then select the ID parameter on the query in the view model and in the properties window set the Databinding to the CustomerProperty.ID to set up the "arrows" on the screen designer. Then drag the CustomerProperty onto the screen where you want it -- it will automatically be a picker control. HTH, -Beth

• Anonymous
  November 07, 2013
  The comment has been removed

• Anonymous
  November 07, 2013
  The comment has been removed

• Anonymous
  March 17, 2014
  In your post you give a tip: "If you want to display more than just the Summary property in the Details Modal Picker, change the content control inside to a Rows layout and select the fields you want." In Visual Studio 2013 Update 1 the pick list shows the selected fields, but when a choice is made the modal picker only shows the summary property.  Is there a workaround in order to have the selected fields displayed (like your second last image for the modal picker behind the pick list). Thanks, Chummy

• Anonymous
  March 17, 2014
  Forgot to mention in my previous comment.  It is for the HTML client. Chummy

• Anonymous
  March 09, 2016
  I think that is one of the so much vital information for me.And i'm satisfied reading your article. But want to commentary on some basic issues, The website style is ideal, the articles is actually excellent :D. Just right activity, cheers

• Anonymous
  March 11, 2016
  Although it truly is not quite a mega-ship, the one,800-passenger Grand Celebration sits really considerably in mainstream cruise ship territory.