The easiest way to create modern business applications for the Cloud and Office 365
LightSwitch has a powerful search data screen template to allow you quickly create a search screen in your LightSwitch application. You can watch Beth’s How Do I video to get started: How Do I: Create a Search Screen in a LightSwitch Application?
In this blog, I am going to answer one frequently asked question: How do I create a search screen that can search properties of related entities?
Let’s start with an example to illustrate the question. Assuming that in your application you have two entities, Category and Product, from the sample Northwind database as shown below (Figure 1).
Figure 1. The entities in the application
The Category and Product has a one-to-many relationship as shown in the connecting line from Category to Product. It means a Category can have many Product instances while a Product is associated with one Category. Now let’s create a default search screen on the Product table, the screen designer will look like Figure 2 below:
Figure 2. The default search screen designer view
You can see that the related entity Category (highlighted) is shown in the Data Grid’s row. For demonstration purpose, I delete some fields (‘Units In Stock’, etc) from the screen designer.
Now run the app and you can see the search screen in action as show in Figure 3, where I have typed ‘ch’ in the search box located on the top right of the screen (highlighted ).
Figure 3. Search screen in action
Even though the Category field is shown in the result (as a summary field), it is not searchable. For example, if you type ‘_chen’ in the search box, you will not get any result even though you might think that it would have results where the Category is Beverages_chen.
This is because the built-in search function does not cover the related entity fields. Fortunately, it is not difficult to overcome this by using queries (please see MSDN topic Filtering Data with Queries). There are multiple approaches depending on your usage scenarios. How to Create a Screen with Multiple Search Parameters in LightSwitch is one example of how to define your own search parameters in your screen. Alternatively, you can create a screen with one search parameter to search multiple fields including the related entity fields like this example:
Figure 4. Query multiple fields with one parameter
In the following, I will demonstrate one simple approach by modifying the query in the search screen. My goal is to use one search screen to search the product entities as well as filtering the result with the Category Name, which is a related entity field.
Let’s start with the SearchProducts screen in the screen designer in the previous example (see Figure 2).
First, I would edit the SearchProduct screen’s query by clicking on the Edit Query command on the top left portion of the designer (Figure 5, highlighted). Notice that this modifies only the query related to the screen itself. You could also create a global query that you could use across screens (see MSDN topic How to: Add, Remove, and Modify a Query)
Figure 5. Edit query on the screen designer
When the query designer shows up, I will click on the Add Filter button. Then leave the first filter column as ‘Where’ (Figure 6 - 1) and click on the second column’s drop down (Figure 6 - 2). Now you will see the list of fields available for the Product entity. You will also see that the related entity Category is in the list as a group. Expand the Category node (Figure 6 - 3), and choose CategoryName (Figure 6 - 4).
Figure 6. Editing the query expression
Set the condition column(Figure 6 - 5) as ‘contains’, the parameter type column (Figure 6 - 6) as ‘@Parameter’ and the parameter column (Figure 6 - 7) as ‘Add New’. Leave the new parameter name as CategoryName. The final query expression should look like Figure 7 below.
Figure 7. The final query expression
Now click on the Back to SearchProducts link (on the top left of the query designer) to return to the SearchProducts screen designer. Notice that in the left pane, there is a new group called Query Parameters (Figure 8 -1); expand it if it is collapsed. You can see the CategoryName parameter (Figure 8 -2) under Query Parameters, Drag the CategoryName parameter and drop it under the Screen Command Bar node in the content tree. (Figure 8-3).
Figure 8. Drag and drop CategoryName parameter to the content tree
Now you will notice that a Text Box called Category Name (Figure 9 -1) is created on the drop location. Additionally, you will see a screen property called CategoryName is created (Figure 9-2). The Category Name Text Box is data bound to the CategoryName screen property, which is consumed by the query as well.
Figure 9. The final look of the search screen designer
That is all the design work to setup a search text box for the CategoryName parameter. Let’s run the app. You should see the similar screen shot as in Figure 3 except now we have an additional Category Name search box on the top of the screen. You can then filter the category name first (Figure 10-1) then search again with the default search box (Figure 10-2) to search native fields in the product. Figure 10 shows a search result in action.
Figure 10. The final search screen in action
Furthermore, you can also hide the built-in search box and use just your own search box. In the example above, you can go back to the SearchProducts screen designer and click on the Products table (Figure 11-1). Hit F4 to ensure the Properties pane is shown. From the Properties pane, uncheck the Support search check box (Figure 11-2).
Figure 11. Hide the built-in search box
You can then modify the query and modify the screen designer accordingly to add as many search fields in Products and its related fields in Categories as needed.
· Filtering Data with Queries
· How to use lookup tables with parameterized queries (Karol Zadora-Przylecki)
· How to Create a Screen with Multiple Search Parameters in LightSwitch
In many situations I find it more elegant to use just one search field instead of one search field per related field. It's quite easy to implement: lightswitch.com.br/.../Full-Text-Search-with-LightSwitch.aspx.
Thank you for sharing this procedure and the effort to make the description. Although in these cases I prefer to filter through a combox and a button.
If anyone is interested can see it on this topic: Filtering Data in a datagrid with a combobox