[Inside TeamCompanion 2.1] Query by Example (QBE)

TeamCompanion features the standard grid based work item query editor (see Picture 1) since the version 2.0. This editor is powerful, very flexible and supports lots of options, which makes it overly complex for most standard everyday queries. Although there are only a handful of work item fields usually used in the queries, user needs to pick them from a long list of fields each time over and over again. From the names in the list it is not always even clear which is the right field for specific criteria. These are only some of the issues with the standard query editor that led us to the idea to make better querying experience possible.

Picture 1: Standard Work Item Query Editor

In order to enable easy and efficient work item query editing and work item search in general we have decided to implement a new kind of work item query editor in TeamCompanion v 2.1 - Query by Example (usually referred to just as QBE). In the QBE, the standard fields are already predefined and user only needs to fill in the values. Defining search criteria this way is easier, faster, and more straightforward – most of the queries can be defined with just few clicks.

In QBE, fields are grouped in 5 different groups and presented in as many tabbed dialogs:
• General (for general query filter fields like Project name, State, Work Item Type, Area and Iteration path, Id etc.),

• State transitions (for the fields related to the work item state transitions like Created on, Created by, Resolved on etc.),

• Text (for full text search over different text fields),

• Historical Queries (for the ASOF wiql operator)

• Additional criteria (for the fields that are not provided in previous dialogs; in the picture below we have arbitrarily added two fields: Discipline and Triage).

So, how would you define an ordinary Query like “give me all active bugs and tasks assigned to me in the current project”? Actually, all you would need are exactly six mouse clicks. Check the checkboxes for the work item types Bug and Task and select @Me from the list in the “Assigned to” control and Active from the “Status” control. It can’t be simpler than that.

Picture 7: A simple Work Item Query in QBE – criteria described above are highlighted

We could add some additional criteria from the State transitions tab (e.g. search for work items that “I have created in the last 30 days”). Another four clicks.

Picture 8: A simple Work Item Query in QBE contd. - State transitions

We can even have full text search criteria included in the query.

Picture 9: A simple Work Item Query in QBE contd. – Full text search

Now, you are in for a surprise. Just switch tabs from “Query by Example” to “Query Editor” and the same query we have just defined in QBE is available in the standard grid based editor and can be further refined there. We can change any of the criteria just defined in QBE or add some completely new criteria. In QBE there is a constrained set of fields available. In the grid there are no restrictions: you can choose any field you like and as long as you choose AND operator while adding new fields, you will be allowed to switch back to QBE and continue editing the query there.

If QBE were just a fancy way to define queries using hardcoded set of fields, it would clearly be of limited use. But if you add custom fields to the query in the Query Editor and switch back to QBE, these fields will be available there too. That is what Additional criteria tab is used for. If there are additional fields in the work item query, that are not provided by default in any other tab, as long as the query structure is such that the QBE can present it, the fields will be available in the Additional criteria tab.

Picture 10: A simple Work Item Query in QBE contd. – Query opened in the standard Query Editor

Picture 11: A simple Work Item Query in QBE contd. – Custom field added in the Query Editor

Picture 12: A simple Work Item Query in QBE contd. – Custom field editable in QBE

This way with QBE and grid based Query Editor you get the best of both worlds: efficiency and ease of use of QBE and flexibility of Query Editor.
Following are some best practices regarding QBE that will further improve your usage experience with QBE.

If you want to remove a clause regarding some field on the General tab that comes together with the combo for the operator (like State, ID or Priority etc.), instead of removing the value first and then the operator, clear the operator directly by choosing empty string from the combo – it will empty the value as well.
In the QBE’s State transitions tab, by default After/On (meaning greater than or equal) or Before/On operators are used. In order not to clutter the UI too much, these operators are read only and cannot be changed here. But, if you switch to the grid based Query Editor and replace ‘>=’ with ‘>’ or ‘=’ or replace ‘<=’ with ‘<’ or ‘=’ we will honor it. Switch back to QBE and the operator will be changed appropriately.

Picture 13 to 15 – Workflow when changing temporal operators

You may be wondering what kinds of queries QBE does support. There is one basic criterion a query needs to satisfy to be editable in QBE: each filter clause (at the root level) has to be connected using AND operator. Additionally there are some operators QBE doesn’t support. Usually you really don’t need to worry about it – if the query isn’t supported by (editable in) QBE, QBE tab will instantly be disabled. That is not needed all too often though; QBE supports all queries from both standard Microsoft process templates and from Conchango’s Scrum template.
One closing note: after you define the search filter in QBE and/or in the grid based Query Editor, you should go to the third tab and configure the column options for the query: columns displayed, sorting order and grouping. The first two are standard part of any work item query. The third one, grouping, is supported because in TeamCompanion we are leveraging Outlook grouping features.
That’s it, grab your copy of TeamCompanion and start playing with Query by Example.

Summary
Query by Example (QBE) is a simplified Work Item Query editor that makes it easier to define usual everyday Work Item Queries. Queries are defined by entering values in already available placeholders for specific most frequently used Work Item Fields (like State, Priority, Iteration or Area Path etc.). QBE also supports full text search in any text or html work item field. There is full two way synchronization between QBE and standard grid based Query Editor so that you can edit search criteria in any of these two editors and freely switch between them.