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 »
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…
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.
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
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.
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.
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.
Just what I was looking for! Thanks.
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:
Can you help me?
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.
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!
@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. "
Based on the above is there a way to filter for Customers who have NO Orders (Silverlight)?
Really appreciate your clear posts :)
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()
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?
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.
Does the Detail Modal Picker only exist in the HTML client project type? I am using VS 2012 and the standard LightSwitch Application project type. The only options I get for my parameters is Custom Control, Label, and Text Box. Is there a way to use drop-downs in the standard LightSwitch Application project type besides having to hard-code a Custom List?
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.
The Silverlight client has 2 choices for pickers, a modal window picker (with a search) and an Auto-complete box. See this post for details: blogs.msdn.com/.../filtering-lookup-lists-with-large-amounts-of-data-on-data-entry-screens.aspx