General Query Examples

The Query field, available in the Custom List and other list-style apps, provides you with a way to filter the data displayed using multiple criteria. You can enter SQL-style queries into the field to find specific work items based on their state, owner, or other fields.
Learn more about formatting syntax in the Query field.
Note that field values must be queried using the correct case. For example, if a username in
is capitalized as [email protected], a query formatted as
(Owner.UserName = "[email protected]")
will not work. The value must match exactly in the query.
Queries will not return work items that reside in projects you are not scoped to or do not have permission to view. Ensure that you have permissions in the projects you want to query.
This section includes the following topics:

Examples of General Queries

See the following examples for common queries.
Work Items with Specific Text in the Name
This example is useful if you use various naming conventions to identify different groups of work items:
(Name contains "Technical Debt")
Work Items in a Specific Iteration (by ID)
You must know the iteration OID to use the following query. You can locate the OID by the number that displays at the end of the URL when viewing the detail page of an iteration, such as
(Iteration.OID = "6082450599")
Work Items in a Specific Iteration (by Name)
(Iteration.Name = "September Sprint 2")
Work Items with No Value Selected on a Custom Drop-Down Field
(DropDownField = "")
User Stories Without Parent User Stories
(Parent = null)
User stories that Have Parents That Are Not Portfolio Items
((PortfolioItem = null) AND (Parent != null))
User Stories that have Parents Which Are Either User Stories or Feature-Level Portfolio Items
((PortfolioItem != null) OR (Parent != null))
Open Defects
(State < "Closed")
User Stories Associated with a Parent
(Parent.FormattedID = 18)
User Stories Needing Estimates
((PlanEstimate = null) AND (ScheduleState = "Defined"))
User Stories in an Iteration Needing Estimates
(((PlanEstimate = null) AND (ScheduleState = "Defined")) AND (Iteration != null))
User Stories with a Schedule State Value Between Defined and Accepted
((ScheduleState > "Defined") AND (ScheduleState < "Accepted"))
User Stories without Defects
(Defects.ObjectID = null)
User Stories with Defects
(Defects.ObjectID != null)
Defects that are in Submitted or Open States
(State IN "Submitted,Open")
Retrospective Notes
For this query, use the Notes field on the Iteration object to store your retrospective feedback. If you display just the Notes column, the app will expand to show all of the rich text content. Be sure to select Iteration as the object in the app settings.
(Notes != null)
Set the order to Descending and the page size to 1 to only display the most recent iteration's notes.
My Tasks in the Current Iteration
This query uses the "today" syntax to show only work in the currently active iteration.
(((Owner.UserName = "[email protected]") AND (Iteration.StartDate <= today)) AND (Iteration.EndDate >= today))
Standalone Defects Reported by More Than One Customer
This example uses a custom field called Reporting Customers. The Requirement field is used to identify the user story a defect may be associated to.
(((ReportingCustomers > 1) and (State < "Closed")) and (Requirement = null))
Open Defects, Owned by Bob, Associated with a Specific User Story
(((Owner.UserName = "[email protected]") and (State < "Closed")) and (Requirement.Name = "Story 2"))
Open Defects that George Found
((SubmittedBy.UserName = "[email protected]") and (State != "Closed"))
Open Customer-Specific Defects
This example uses a custom field named Affected Customers.
((AffectedCustomers contains "ShotzBrewery") AND (State != "Closed"))
Defects without Tags
(Tags.ObjectID = null)
Defects that Have Tags
(Tags.ObjectID != null)
Below are some fields that can help you build a specific query. Not all fields are listed.
To see a list of all fields available for each type of work item, check your Web Services API documentation.
The date a work item's schedule state is changed to Accepted.
Identifies if a work item is marked as blocked.
Searches contents of the Description field. You may use contains or !contains conditions.
The number of direct children (does not include grandchildren or deeper levels) associated to a user story.
The iteration a work item is scheduled in. You must know the iteration OID when using this field.
The value present in the work item's Plan Estimate field.
The release a work item is scheduled in. You must know the release OID when using this field.
The state of a work item scheduled in an iteration or release. Standard values are Defined, In-Progress, Completed, and Accepted.
The state of a defect. Not to be confused with the Schedule State field.
The total number of estimate hours of tasks associated to a work item.
The total number of remaining to do hours of tasks associated to a work item.

Common Problems with Queries

If your query is not returning the data you expect, see the suggestions below.

Correct Parentheses Syntax in Queries

Parentheses used in queries with multiple conditions must be balanced. For every condition in the query, you must add one
to the front of the string. You must also end each condition beyond the first with
  • Incorrect
    ((Name = "A") AND (Name = "B") AND (Name = "C"))
  • Correct
    (((Name = "A") AND (Name = "B")) AND (Name = "C"))

Correct Field Name Syntax in Queries

While reviewing the Web Services API documentation, be sure to remember that many fields are objects, which contain child elements. For example, Tags are an object in
, but have an element to locate the Name.
May return inconsistent results
((Tags = "Blue") AND (Tags = "Green"))
Correct syntax
((Tags.Name = "Blue") AND (Tags.Name = "Green"))
Ensure that the correct field name is placed to the left of the operator, and the value or date variable is to the right of the operator. A query such as (AcceptedDate < InProgressDate) is invalid, because it contains field names on both sides of the operator.

Query Fields That Do Not Contain Specified Text

When using the
(does not contain) condition against a text or string field, you may see fewer results than expected.
  • For example, the query below will find work items that
    have a non-empty Notes field, and do not contain the word "blue":
    (Notes !contains "blue")
  • To find work items that contain text other than "blue"
    work items with empty fields, structure your query like this:
    ((Notes !contains "blue") OR (Notes = null))