Filter Items in an Auto Complete Box or Modal Window Picker in LightSwitch

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

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.