The Query Editor

Finding the tasks that matter

Because our three screens show all available entities, it’s easy to imagine that the screens in their current state will become useless pretty fast as they are filled with hundreds of old and already completed tasks. Most of the time, we will not be interested in all tasks, but only in the ones that have to be completed next.

For any entity, LightSwitch automatically generates a Select all and a Select by ID query, but to filter out the old and unimportant tasks successfully, one has to create a custom query. Like most things in LightSwitch, this will only take a couple of seconds once you get used to the Query Editor.

In the Solution Editor, right-click on the Tasks entity and select Add Query.

Figure 31: Adding a query for an entity from the Solution Explorer

The first thing you’ll want to do is to rename the query as something more meaningful, UrgentTasks for example. This can be done in the Properties Window.

Figure 32: A query has extended properties too

Adding filter criteria

The first thing we’ll want to do is to add a new Single filter.

Figure 33: Add a new Single filter

In this query, we do not want to see any tasks that are in the distant future. The first filter criteria should be that the DueDate is less than the end of the month.

Figure 34: Filtering based on DueDate

We’ll also want to filter out any tasks that have been completed and any tasks with a priority less than 4. You might remember that the business type of the Priority property is an integer even though we represent it to the user as a choice list with “Low,” “Medium,” “High,” and “Very High” labels.

Figure 35: Filtering based on DueDate, PercentageComplete, and Priority

Adding some logical sorting

While we’re in the Query Designer, finish the query by adding a default sort for our urgent tasks based on Priority, and then by DueDate.

Figure 36: Adding a default sort to the query

Add a screen directly to this query

To test your query, you can create a fourth List and Details screen called AllUrgentTasks. Because the query is the default global query, you can select it as the Screen Data to use. (LightSwitch also allows queries to be defined at screen level, called local queries.)

Figure 37: Adding a screen directly on a global query

With my test data, it seems to work correctly. Out of my two tasks, Do the dishes has a low priority, so it is not included in the All Urgent Tasks screen.

Figure 38: The new screen only shows the urgent tasks

Finding the important tasks per user

The query in Figure 38, however, displays all urgent tasks. This screen is great for me, as a manager of my own personal life, and the only user of this application so far. But if you were to use this application to manage several people, tasks, and projects simultaneously, you may be interested in an urgent-tasks-per-user query rather than the screen in Figure 38.

Create a second query by right-clicking your Tasks in the Solution Explorer, and selecting Add New Query.

Rename the query as MyUrgentTasks.

Query inheritance

Note that this query uses the complete Tasks list as the source, but we already have a query to separate the important tasks from the trivial ones. As an avid fan of avoiding repetition, I am thrilled that LightSwitch offers the possibility to base one query on the results of another. To do so, change the Source from Tasks to UrgentTasks at the top of the Query Editor.

Figure 39: Inheriting a query from a previously made query

By doing so, this query will inherit the UrgentTasks’ filtering and sorting criteria, allowing us to not worry about sorting or filtering out only the important tasks anymore. Our only concern then is finding the tasks assigned to a particular Person.

Adding parameters to a query

To do so, we’ll add a new filter.

Figure 40: Filtering based on the person to which the task is assigned

Then select Parameter from the drop-down as shown in Figure 40, and add a new query parameter as shown in Figure 41.

Figure 41: Picking a source for the assignment

Figure 42: Setting the source to a new parameter

Rename the parameter as AssignedPerson.

To allow even more detailed control over which Tasks will be included, I added two additional filters and matching parameters: MinimumPercentageComplete and MaximumPercentageComplete. For these two however, I checked the Is Optional check box in the Properties window.

Figure 43: An overview of the query

To view this data, we’ll have to add a new screen. This time the default screen generated by the List and Details Screen Template, which we’ve used three times now, won’t be sufficient. It’s time to have a closer look at the Screen Editor.