Page Comparison

The query language ressembles the SQL language and allows powerful queries. A typical example would be:

...

General features

=

==

Compare field with a value. You can use both "=" and "==". These and other operators can be used with most field types.

Code Block
{reference} = "1223-82" {id} = 10234

Use double quotes with strings.

Inside a string literal, a double quote is written as two consecutive double quotes:

Code Block
{reference} = "Reference ""123"""

>, <

>=, <=

To compare numeric field values and dates.

Code Block
{count} >= 100

!=

<>

Not equal operators. Both have the same function.

AND

&&

Boolean "and" to combine conditions.

Code Block
{count} >= 100 && {count} < 1000 {count} >= 100 AND {count} < 1000

OR

||

Code Block
{count} < 100 \|\| {count} > 1000 {count} < 100 OR {count} > 1000

!=

Not equal.

Code Block
{count} != 100 {reference} != "alpha"

( )

Brackets can be used to combine multiple conditions:

Code Block
({count} == 100 OR {count} == 101) AND {status} == 10

!

NOT

Negation:

Code Block
(NOT {count} == 100) OR {count} == 101 is equal to: {count} != 100 OR {count} == 101

x ? y : z

Evaluates y if x is true, evaluates z if x is false.

Code Block
{count} >= ({status} = 1 ? 100 : 1000)

null

Literal to evaluate if a field is null or not:

Code Block
{deadline} == null nullNOTNOT {deadline} == null

Numbers

+ - * /

Numeric operators.

Code Block
({words} * {document} > 1000)

Decimals

Decimal fields use the optional '.' (dot) decimal point:

Code Block
{vat} > 18.5

Strings

.StartsWith

.EndsWith

.Contains

Various operators to search string fields by prefix, suffix or infix.

Use NOT to negate.

Code Block
{reference}.StartsWith("123") {reference}.EndsWith("123") {reference}.Contains("123") NOT {reference}.Contains("123")

.Length

To query by string length:

Code Block
{reference}.Length < 10

Dates

You can use the DateTime object to compare dates.

To compare a date:

Code Block
{deadline} >= DateTime(2007, 1, 1)

Or with hours, minutes and seconds:

Code Block
{deadline} < DateTime(2007, 1, 1, 10, 30, 0)

...

It takes away most of the complexity of properly formatting and evaluating different field types, including dates, multi-select custom fields, labels and advanced string searches.

...

.

Examples:

Code Block
{myfield}.Matches("hello", "prefix") {myfield}.Matches("2018-1-1", ">=") {myfield}.Matches(10, ">=", 100, "<") {myfield}.Matches("world") AND {mydate}.Matches("2018-1-1", ">=", "2018-1-2", "<") ...

...

Basic

Exact numeric match:

Code Block
{words}.Matches(100) {words}.Matches(100, "=") // "=" is the default operator {words}.Matches(100, "!=") // Different from 100

Range match:

Code Block
{words}.Matches(100, ">=", 500, "<=")

The operators can be any of: >, <, >=, <=, =, !=

Null values

Find Any of

This construct matches if the field matches any one of the pipe separated values:

Code Block
{status}.Matches("1,2,5", "anyof")

Null values

Find null values with fields that are nullable:

Code Block
{words}.Matches(null) {words}.Matches(null, "!=")

...

Decimals filtering

Basic

Like with numeric fields, you can do different comparisonsSummary

You can use all the features also available with number filters.

Please always use the "." (dot) character for the decimal point. The decimal point is optional.

Examples:

Code Block
{deadlinevat}.Matches("2018", ">="19.5) {deadlinevat}.Matches("2018-10-10"18, ">=") {deadline} // "=" is the default operator

Date filtering

Basic

Like with numeric fields, you can do different comparisons:

Code Block

{deadline}.Matches("2018-10-10", ">=", "2019-2-2", "<")
{deadline}.Matches("2018-12-10-10", ">=")
{deadline}.Matches("2018-10-10", ">=", "2019-2-2", "<")
{deadline}.Matches("2018-12-25:10:45:20Z", "!=")

Important:

Dates are always interpreted as UTC.
Partial date/times are automatically expanded. Example: "2018" is converted to 2018-01-01 00:00
See below for permitted date formats

Date formats

Formats that are supported are:

- 2018
- 2018-10
- 2018-10-25
- 2018-10-25 22:30:00 or 2018-10-25T22:30:00 or 2018-10-25T22:30:00Z
- 2018-10-25 22:30:00.123 (append fraction of seconds) + variants as before
- Full round trip format: 2009-06-15T13:45:30.0000000Z

Note: Partial date/times are automatically expanded. Example: "2018" is converted to 2018-01-01 00:00

Null values

Find null values with fields that are nullable:

Code Block
{deadline}.Matches(null) {words}.Matches(null, "!=")

Filter day or month

All deadlines on a day (UTC):

Code Block
{deadline}.Matches("2018-10-25", ">=") AND {deadline}.Matches("2018-10-26", "<")

All deadlines in a month (UTC):

Code Block
{deadline}.Matches("2018-10", ">=") AND {deadline}.Matches("2018-11", "<")

Boolean filtering

Basic

Valid examples:

Code Block
{mybool}.Matches(true) {mybool}.Matches(false) {mybool}.Matches(null) // Field is not set (null) {mybool}.Matches(null, "!=") // Fiield is set and either true or false

Permitted operators are: "=" and "!=".

Custom field multi select picklists

Multi-select pick list custom fields store selected values in a very specific format:

"|Austria|France|Germany|"

Selected options are pipe delimited.

Find all options

Write like thisDate offsets

Sometimes it is easier to filter by a date offset.

For example: Find jobs with a deadline of tomorrow. This can be expressed with "1d". More examples:

"1y 2m" : 1 year and 2 months into the future
"-2d": 2 days into the past
"+4 M": 4 minutes into future
"24H 30M 20S": 24 hours, 30 minutes and 20 seconds into the future

You can thus use one or more combinations of:

y, m, d: Year, Month, Day
H, M, S: Hours, Minutes, Seconds

Deadline within +/- 6 hours:

Code Block
{deadline}.Matches("-6H", ">=") AND {deadline}.Matches("+6H", "<=")

All objects created since 1 year and 6 months:

Code Block
{created}.Matches("-1y 6m", ">=")

Date offset filtering

Date offsets

Sometimes it is easier to filter by a date offset.

For example: Find jobs with a deadline of tomorrow. This can be expressed with "1d". More examples:

"1y 2m" : 1 year and 2 months into the future
"-2d": 2 days into the past
"+4 M": 4 minutes into future
"24H 30M 20S": 24 hours, 30 minutes and 20 seconds into the future

Deadline within +/- 6 hours:

Code Block
{deadline}.Matches("-6H", ">=") AND {deadline}.Matches("+6H", "<=")

All objects created since 1 year and 6 months:

Code Block
{created}.Matches("-1y 6m", ">=")

Units

You can thus use one or more combinations of:

y, m, d: Year, Month, Day
H, M, S: Hours, Minutes, Seconds

You can prefix offset with "-" for dates into the past: -1y stands for one year ago.

You can prefix with optional "+" for dates into the future: +1y (or 1y) stands for one year from now.

Boolean filtering

Basic

Valid examples:

Code Block
{mybool}.Matches(true) {mybool}.Matches(false) {mybool}.Matches(null) // Field is not set (null) {mybool}.Matches(null, "!=") // Fiield is set and either true or false

Permitted operators are: "=" and "!=".

Custom field multi select picklists

Multi-select pick list custom fields store selected values in a very specific format:

"|Austria|France|Germany|"

Selected options are pipe delimited.

Find all options

Write like this:

Code Block
{cffield}.Matches("o1\|o2\|o3", "allof") // All 3 options must appear in the field

Find any option

Write like this:

Code Block
{cffield}.Matches("o1\|o2\|o3", "anyof") // Any one of the options must appear

Comments: Whenever the field is a multi-select picklist, the system will automatically adjust the query. For the example above the query wil be:

Code Block
{cffield}.Matches("o1\|o2\|o3", "anyof") == translated to ==> {cffield}.Contains("\|o1\|") OR {cffield}.Contains("\|o2\|") OR {cffield}.Contains("\|o3\|")

Labels count field

The labels count field returns the total labels assigned to an object such as a job or project.

It contains 2 properties:

cnt: Total assigned labels.
xp: Total explicitly set labels. This excludes labels that are configured to automatically show up on ALL objects with a default value.

The available query options are:

Filter for xp > 0

This filter returns all objects that have at least one label that was explicitly assigned by a user.

Code Block
{field}.Matches(true) // Value xp must be greater than 0

Find for xp = 0

This filter returns all objects with no label assigned by a user.

Code Block
{field}.Matches(false) // Value xp must be 0

Label field

The available query options are:

Filter label options

Write like this:

Code Block
{labelfield}.Matches(0) // First label value option selected {labelfield}.Matches(2) // Third label value option selected {labelfield}.Matches("1,2,3") // Any of options 1, 2 or 3 selected

You can also use the optional operator:

Code Block
{labelfield}.Matches(2, "=") // Third label value option selected {labelfield}.Matches("1,2,3", "=") // Any of options 1, 2 or 3 selected

If a label is not shown by default, it can also have the "null" option:

Code Block
{cffield}.Matches(null) // No label set

Exclude label options

Write like this:

Code Block
{cffield}.Matches("3,4,5", "!=") // None of the options must appear

If a label is not shown by default, it can also have the "null" option:

Code Block
{cffield}.Matches("o1\|o2\|o3null, 0", "allof!=") // AllNo 3label optionsor mustjust appear in the field

Find any option

Write like this

default label

Any label option

To filter for data where the label has any of the options:

Code Block
{cffield}.Matches("o1\|o2\|o3"null, "anyof!=") // Any one of the options must appear

...

label

Comments

When specifying a single value, the quotes are optional.

Code Block
{cffield}.Matches("

...

3", "

...

")

...

or
{cffield}.

...

Matches(3, "=")

Query tree

When running queries the results may include a JSON representation of your query.

...

Versions Compared

Old Version 23

New Version Current

Key

Decimals filtering

Date filtering

Boolean filtering

Custom field multi select picklists

Date offset filtering

Boolean filtering

Custom field multi select picklists

Labels count field

Label field

Query tree