Search for tasks using iWD Query Language

Introduction

The iWD Query Language (QL) provides a flexible means for narrowing down the interactions presented in the Global Task List (GTL), with a number of operators including comparison and logical ones. It is a subset of Structured Query Language.

Constructing iWD QL queries

A simple query in QL (also known as a 'clause') consists of 3 parts, one following another: field, operator and value. Each of them is described on this page. Here's an example query:

Department = 'TEST'

This query will find all tasks in the TEST department. It uses the Department field, the EQUALS operator, and the value TEST.

Clauses can be combined by AND and OR operators and can be wrapped with round brackets. You can use brackets in complex iWD QL statements to enforce the precedence of operators.

For example, to find all tasks from either the Finance Department or with media type email and, from that selection, all tasks that have Business Value more than 100, use the following query:

(Department = 'Finance' OR Media Type = 'e-mail') AND Business Value > 100

Fields

All tasks in the system have attributes and most of them can be used for filtering using iWD QL. The easiest way to find available attributes and their names is by accessing the Build custom filter menu by pressing the 100px button. You can also use the column names displayed in the GTL.

Operators Task attributes contain data of specific type: String, Number or Date. Depending on the data type only specific set of operators is supported in query clauses. Relation between attribute type and operators it supports is presented below:

Type Supported Operators Number =, !=, >, >=, <, <= String =, !=, LIKE Date same as Number List of supported operators

EQUALS: =

The "=" operator is used to search for tasks where the value of the specified field exactly matches the value provided.

Important String values should be wrapped in single quotes.

To find tasks where the value of a specified field exactly matches multiple values, use multiple "=" statements with the AND operator.

Example:

Find all tasks in the "TEST" department

Department = 'TEST'

NOT EQUALS: != The "!=" operator is used to search for tasks where the value of the specified field does not match the specified value.

Example:

Find all tasks that are not in the "TEST" department

Department != 'TEST'

GREATER (LESS) THAN (OR EQUAL): >, >=, <, <= The ">" (greater than) operator is used to search for tasks where the value of the specified field is greater than the specified value. The ">=" (greater than or equal) does the same as ">" but including tasks where its value is equal to the one specified as well.

The "<" and "<=" operators ('lesser than' and 'lesser than or equal' respectfully) work as the opposite for 'greater' ones, i.e search for tasks where the value of the specified field is lesser than (or equal) to the specified value.

Important Greater and lesser operators cannot be used with text values.

Examples: Find all tasks where field "Priority" more than 40 (exclusive) and lesser than 45 (inclusive), i.e.: tasks with priority being any of the following: 41, 42, 43, 44 and 45.

Priority > 40 AND Priority <= 45

Find all tasks where field "Business Value" more or equal to 100 (inclusive) and lesser than 105 (exclusive), i.e.: tasks with Business Value being any of the following: 100, 101, 102, 103 and 104.

Business Value >= 100 AND Business Value < 105

LIKE: like Determines whether a specific character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string. However, wildcard characters can be matched with arbitrary fragments of the character string. Using wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators.

Important LIKE operator cannot be used with integer and date fields.

Wildcard characters Char Description % Any string with zero or more characters _ Any single character search

Examples Find all tasks in "TEST" Department

`Department like 'TEST'`

Find all tasks where Department starts with "TEST"

`Department like 'TEST%'`

Find all tasks where Department ends with "TEST"

`Department like '%TEST'`

Find all tasks where Department contains "TEST"

`Department like '%TEST%'`

Find all tasks where Department starting with T, ending with 'T' and contains two any characters between them

`Department like 'T__T'` Reserved characters The following characters are forbidden in string values: |, \, ?, ], }, {, [.

Important You can still use the \ symbol in order to escape wildcard characters % and _ to use them literally.

Here's an example of valid backslash use:

Department like '%TEST\%20' this query will find all tasks where Department ending with "TEST%20". The same goes for the _ symbol.

If tasks contain any of forbidden characters you can use single character wildcard '_' to omit their explicit presence in iWD QL query.

Absolute Dates iWD QL allows searching by date. You can search for tasks that were created on, before, or after a particular date (or date range). Date value must be written in the following format: 'YYYY-MM-DD', where YYYY is a full year, MM - month with leading zero, and DD is a date with leading zero. Wrapping date value in quotation marks is not needed.

Examples:

Find all tasks where Task Due D/T is 31 Dec 2019

Task Due D/T = 2019-12-31

Find all tasks where Task Due D/T is 31 Dec 2019 OR ask Due D/T is 1 Jan 2019

Task Due D/T = 2019-12-31 OR Task Due D/T = 2019-01-01

Find all tasks where Task Due D/T between two dates 31 Dec 2019 and 1 Jan 2019

Task Due D/T >= 2019-01-01 AND Task Due D/T =< 2019-12-31 Relative Dates Along with absolute dates, you can specify a date relative to the current time. For example, you want to find all tasks with "Task Due D/T" in next 7 days

Task Due D/T > 0d AND Task Due D/T < 8d Let's assume today is 2019-12-12, the query above will find all tasks between now and 2019-12-21

the following example will find all tasks with "Task Due D/T" in the previous 7 days

Task Due D/T < 0d AND Task Due D/T > -8d Supported relative values Letter Description Example y year Date > 1y m month Date > 1m d day Date > 1d h hour Date > 1h Relative values can be combined with each other. For example

Task Due D/T > -3h8d2m1y Values can be placed in any order. For example, "3h1m" does the same as "1m3h".

Zero value will do nothing. For example,

Task Due D/T > 0d will find all tasks where "Task Due D/T" is past the current time.