Filtering using API like queries

In Configuring advanced settings it is possible to enable advanced filter query to get access to API like filters in the Versions / Tasks Spreadsheet and the Latest versions widget. This feature is geared towards advanced users with programming experience that have good knowledge of the ftrack API query language.

The query syntax is identical to the filter part of the query. So anything after the where keyword can be used:

select id from Task where .

Let us now have a look at a few examples of what can be done in the Tasks Spreadsheet, Add filter ‣ Task ‣ Query. Let us start with something easy and find all tasks named Animation:

name is Animation

Here we use a relation to filter out all tasks that have a status that is Approved:

status.name is Approved

Now let us try finding all Shots that have notes from our colleague John. Use Add filter ‣ Shot ‣ Query to add the query field for Shot:

notes any (author.first_name is John)

If we use the query filter in the Versions spreadsheet, Add filter ‣ Version ‣ Query we can find versions that have some metadata attached to them:

metadata any (key is foo and value is bar)

Or versions that have components in our custom Location named studio.foo:

components.component_locations.location.name is "studio.foo"

Any issues with syntax, usage of relations or attributes in the Query will be presented as feedback below the query input field.

Note

Take care when filtering based on relations. There are currently no restrictions on what relations are available and certain queries on nested data might cause performance problems.

Using variables

Filters also supports a set of predefined variables for dynamic usage:
CURRENT_USER_ID
User id of the current user.
CURRENT_USERNAME
Username of the current user.
LAST_MONTH
First date of last month.
LAST_WEEK
First date of last week.
THIS_MONTH
First date of this month.
THIS_WEEK
First date of this week.
NEXT_MONTH
First date of next month.
NEXT_WEEK
First date of next week.
YESTERDAY
Yesterday's date.
TOMORROW
Tomorrow’s date.
TODAY
Today’s date.
NOW
Date and time right now.
DAYS(N)
Date relative to today. N can be a positive or negative integer. E.g. DAYS(14) or DAYS(-14)

Example of how to filter out all tasks that has notes added this week:

notes.date > "{THIS_WEEK}" and notes.user_id != "{CURRENT_USER_ID}"

Note

A variable must be inside quotation marks, see the example above. If a variable is misspelled or does not exist it will be silently ignored.

Known limitations

The date comparison will be in UTC, which can cause confusion. This limitation applies if timezone support is enabled (enabled on all hosted instances).

Did this answer your question?