How Do I: Filter Items in an Auto Complete Box or Modal Window Picker in LightSwitch (Eric Erhardt)

How Do I: Filter Items in an Auto Complete Box or Modal Window Picker in LightSwitch (Eric Erhardt)

Rate This
  • Comments 13

Note: This article has been updated for Beta 2 on 3/17/2011

As with most application platforms, it is possible to associate related pieces of data in Visual Studio LightSwitch. A classic example is a SalesOrder is associated with the Customer that placed the order.


When a sales order entry clerk is entering a new sales order into the system, normally the sales application will allow the clerk to select the Customer that is placing the order. In LightSwitch, there are two built-in UI controls to associate one record with another:

· Auto Complete Box

· Modal Window Picker

Which one you choose is dependent on your user experience needs. As a rule of thumb, for most data sets, I would use the Auto Complete Box.  This control allows for quick data entry by the end user.  The user can just begin typing, and the intended record is selected automatically.  For data sets that are going to grow to hundreds, maybe thousands, of items it is appropriate to use the Modal Window Picker.  The Modal Window Picker allows the end user to search and sort the items in order to find the correct record. By default, LightSwitch will choose the Auto Complete Box control.

Out of the box, these picker controls will show all of the records in a table. But there are plenty of cases where an application would want to filter which records are shown in the picker. For example, in our SalesOrder-Customer scenario above, we would like the system to track active and inactive Customers. Although the inactive Customer records are still in the system, a new SalesOrder should not be created to associate to an inactive Customer.

In order to accomplish this, we start by creating a New Data Screen based on the SalesOrder table.


In the newly created screen, notice that there is an Auto Complete Box control for the Customer that placed this SalesOrder.


We want to define a query that will filter out the inactive Customers from this picker. In order to do this, click the Add Data Item button at the top of the designer.


In the Add Data Item dialog, select the Query radio button, and the Customers query in the query list. Name this new data item “ActiveCustomers” and click OK.


This will create a new ScreenCollectionProperty named “ActiveCustomers” on the left side of the screen designer. In order to change the filter on this query, click the Edit Query hyperlink next to ActiveCustomers.


This will launch the query designer where you can specify the filter. Add a new filter “Where IsActive = ‘True’” and sort by “Name Ascending”.


Alternatively, instead of defining the query directly on the screen, you can add a new Query under the Customers table in the Solution Explorer. This allows you to reuse the query across multiple screens. To do this, right-click the Customers table in the Solution Explorer and select “Add Query”. Name the query “ActiveCustomers” and define the filter and sort as above. In the screen designer, instead of picking the “Customers (All)” query, select the “ActiveCustomers” query that was just created.

Now all that is left is to hook up the Customer picker control with this newly created query. Return to the CreateNewSalesOrder screen designer by clicking the Back arrow at the top left of the query designer. Find the “Customer” content item in the screen tree, select it and open the Properties sheet by pressing F4. In the Properties sheet, you will see a “Choices” property with a drop down. Open the Choices drop down and select the “ActiveCustomers” property that was created.


That’s it! Now you can press F5 to launch the application and open the CreateNewSalesOrder screen to create a new sales order. When attempting to select the customer for the new sales order, the user will only be allowed to pick active customers.

Leave a Comment
  • Please add 3 and 3 and type the answer here:
  • Post
  • This article is great but I'm still not able to create dependent dropdowns or dependent Modal Window Pickers.

    Is there a way to bind a query parameter to the selection of a modal window picker?

    Thx in Adance - Steve

  • This article is great but I'm still not able to create dependent dropdowns or dependent Modal Window Pickers.

    Is there a way to bind a query parameter to the selection of a modal window picker?

    Thx in Adance - Steve

  • @Steve:

    you can accomplish this, but you wouldn't bind a query parameter to the selection of a modal window picker.  Instead, the modal window picker is bound to a property (either on an entity instance or on the screen itself).  Then you bind the query parameter to the property.

    For example, imagine I have modeled "City" and "State" tables, with City having a many-to-one association to State.  I also modeled a "ShipCity" relationship from the SalesOrder table to the City table.  I wanted two pickers in my CreateNewSalesOrder screen.  First I want the user to pick the State, that will narrow the ShipCity picker down to only Cities in that State.

    What I would do is first model a query "CitiesByState" that takes in the State Name as a parameter and filters the Cities based on that parameter.  Then I would hook my SalesOrder ShipCity picker up to this query as described above.  Next, add another Data Item to the screen, but instead of a Query, choose "Local Property" and pick the Type of "State (Entity)".  Name it "SelectedState".  Add the SelectedState property as a screen visual by click the "Add" button in the screen tree -> Other Screen Data...  Type in "Screen.SelectedState" in the text box.  This will create a Modal Window Picker, which you can change to a ComboBox, that will allow the user to pick the State.  All that is left is to hook the CitiesByState query parameter up to the SelectedState.Name value.  On the left side of the screen designer, under CitiesByStateCollection/Query Parameters, select the State Name parameter.  In the Properties sheet set the "Parameter Binding" property to be "SelectedState.Name".  Now you have two pickers with the City picker dependent on the State picker.


  • A nice graphic like above would probably help explain that better?

    I have the above working nicely on an editable grid screen. However when I click on edit in the main grid to bring up the default edit screen the modal window picker doesn't use the filter query that the equivalent field on the main form is linked to so the full list is available.

    Surely it should inherit the connection to the query and filter the list in the same way?

  • This article allows increased expertise in the Light Switch Thank you very much,We need more

  • This article allows increased expertise in the Light Switch Thank you very much,We need more

  • How do you actually get the modal window picker control ? there is only 3 options label,TextBox and custom control...

  • @Nick:

    You need to have a relationship defined between SalesOrder and Customer in the designer.  Open the SalesOrder table in the designer and click on the "Add: Relationship" button at the top.  Select Customer as the "To" side and click OK.

    Now, when you create a new screen on SalesOrder, the Customer will be a modal window picker by default.


  • I would like to filter Sales Order by Created Date, by year and month. How can do it?

  • Marcin, one way is to provide query-parameters and filtering the results based on start and end-dates.  

    Say you want to display a collection of all Sales Orders between two dates you can do the following.  Create an Editable Grid (or other type) screen with the Sales Orders collection. Then edit the query using the link as above.  Supply two new filters, StartDate and EndDate with "CreatedDate < EndDate" and "CreatedDate > StartDate".  Click "Add parameter" to create new screen parameters (mark them as optional in the query designer property grid).  Now go back to your screen designer and drag the "EndDate" and "StartDate" parameters to the screen.

    You should now be able to set the parameters and see the screen collection update accordingly.  If you really want results by month you'll need to write some code to set the query-parameters accordingly.

  • Could you please help me with my problem.

    I have a table of devices and each device have several Properties (table DeviceProperties) and each property can have one PropertyValue (table PropertyValues). There is a list of possible PropertyValues assigned to every DeviceProperty.

    For every two PropertyValues can be created "restriciton" - restricted combination of values cannot be selected for one device at the same time.

    So if user selects a PropertyValue (via Combobox) for the first Property (nothing is saved to DB at this time), it should not be possible to select "restricted" values for the second Property - choices in second combobox should be filtered automatically.

    So far it's working only if I save the data everytime I change some PropertyValue.

    Is it possible to create my own collection of PropertyValues and assign it to combobox or create some query that will use local data (selected but not saved values) and not the data from DB.

    I hope you understand me despite my bad English.

    Thank you


  • Lukas,

    Take a look at Tim Leung's excellent post that describes how one ComboBox can restrict another ComboBox:

    Remember, if you can't model your exact query in the Query Designer, you can always write code to describe your query by using the PreprocessQuery interception method.  See the "Advanced Queries" section in Beth Massi's MSDN Magazine article: for more information on PreprocessQuery.


  • Eric,

    In my properties screen I do not have the 'Choises' property. What could be wrong?

    I do have MS VS lightswitch 2011.

    Thanks in advance for your answer

Page 1 of 1 (13 items)