Querying tutorial

When you use the ProjectManager API, you can search for your information using the query language OData. All data types within ProjectManager can be retrieved using OData queries.

So let’s break this down - what is an OData query? It is a type of query that allows you to:

• Filter records based on criteria you specify in the $filter parameter.
• Paginate your results using $top and $skip.
• Sort your query results using $orderby.
• Fetch additional data using $expand.
• Reduce unwanted data using $select.

Let’s walk through the basics for an OData query.

Example OData queries for tasks

Let’s begin by dMany users want to begin by fetching a specific subset of tasks. In this case, we’ll write a query to fetch a list of tasks that have specific tags. Our developer wants to retrieve a list of tasks that have been tagged with the customer-issue tag. But, we only want to find tasks from a specific swimlane. How do we do this?

• To fetch a task that has a specific tag, we need to examine the tags collection.
• To find a tag with a specific name, we use the OData any method since any particular task could have lots of tags.
• We want to look for a tag that has a specific name; in this case, the name is customer-issue.
• So our tag filter is tags/any(o: o/name eq 'customer-issue').

Next, we want to identify the swimlane. The swimlane is stored in the field name within the status object, so to query it we use the statement status/name followed by a filter such as equals. We want to look for the swimlane “Waiting”.

Combining these two queries together, we get this query:

tags/any(o: o/name eq 'customer-issue') and status/name eq 'Waiting'

Filtering records using OData

Whenever you want to find a specific record in the ProjectManager system, you can use an OData filter statement to search. Microsoft provides a nice tutorial page on learning OData filter expressions which goes into more detail, but let’s summarize it here.

• Search for projects with a specific where a field value matches using an $filter={field-name} eq {value} statement, like $filter=shortCode eq MyNewProject
• Search for tasks more recent than a specific time using an $filter={field-name} gt {date} statement, like $filter=createDate gt 2023-03-01
• Search for a resource with a comment in its “notes” field using an $filter=contains({field-name}, '{substring}') statement, like $filter=contains(notes, 'test').

Comparators and Functions within OData filtering

You can combine multiple comparisons using parenthesis and AND / OR clauses. Some examples:

• Find all tasks within a project that are complete: (projectId eq 8aff412f-f072-479a-837e-eb0d96c6904a AND percentComplete eq 100)
• Find all tasks with the word ‘wash’ in their name that have not yet been started: (contains(name, 'wash') AND percentComplete eq 0)

Filtering tips

When specifying values in your query, keep in mind these things:

• Numeric values are presented as-is, for example count eq 7
• String values are enclosed in single quotes, for example name eq 'Bob Smith'
• GUID values are written without single quotes as if they are numbers, for example projectId eq 8aff412f-f072-479a-837e-eb0d96c6904a
• Date values are always written in ISO-8601 format, also known as YYYY-MM-DD. For example, createDate gt 2023-01-01

Pagination using OData

The standard for OData pagination uses the concept of top and skip. Here’s how it works.

1. The server begins to produce a list of all records matching your $filter statement in the order specified by the $orderby parameter.

2. The server will omit the number of records specified by the $skip parameter, if it is present.

3. If there are still more records remaining after the $skip parameter has been exhausted, the server will begin delivering records up until the $top value is reached.

This allows you to paginate records easily. If you want to retrieve the top 50 records in a table, you specify $top=50. To retrieve the second page of results, specify $skip=50 and $top=50.

Expanding data

Some OData query endpoints allow you to fetch additional data using the $expand parameter. The documentation for the API will explain what options are available and how to use them on each endpoint.

Complex query examples