IQL is IS Tools syntax for advanced filters, charts and logic. From the summer release 2020 it is also possible to control visibility for form components with IQL expressions.

How does it work?

IQL is working as a query language for IS Tools, where structure and syntax have been borrowed from SQL.
In filteres, the condition part is used which can comprise field metadata (for example when it was updated, by whom etcetera) and related information. It is thus possible to filter on more items and specifics with IQL than the basic filter.
In logic it is possible to use selection criteria and/or functions together with the filter condition, and the result is saved to one or several fields.
In forms the behavior of components can be set depending on information from other components.
In IQL charts, the selection criteria and/or functions are used together with the filter criteria.

In the manual

Since IQL is used in several different functions or applications, it is documented in the respective area.

Syntax

From the summer release 2020 the input field is equipped with syntactical aid, which suggests terms and items and colors keywords in the expression.
When text strings are used in filter conditions, they need to be enclosed in single quotes.
System entities, such as field names and list values, need to be enclosed in double quotes if they contain white space or special characters such as å, ä and ö.
Field names in IQL are case sensitive.
Configuration is performed in the language of the account. When several languages are maintained, translations must exist in all languages.
Related information can be expressed with relation fields, where the information from parents are retrieved using a period (.). Filter conditions with information from children use in syntax. Please see the examples below for more information.
Comments can be used in the form /* ... */.

Examples

Below are examples of different usage of IQL.

Filter conditions

Show records from tabel parent having children in the table child through the relation field parent.

/* filter out records without children */
RecordId in (parent from child)

Show interesting time sheets for a project manager from the table timesheet, where the checkbox approved indicates timesheet approved.

/* filter records from other teams */
team in (
  team
  from resource
  where id = currentUser()
)
/* remove approved timesheets */
and approved is null

Business logic

Number of related records, directly or indirectly.

/* aggregate */
count(RecordId)
from object
where entity is not null
/* hierarchy within same table */
and (
  entity = @targetrecord
  or entity.parent = @targetrecord
  or entity.parent.parent = @targetrecord
)

Charts

Pie charts showing activity per status.

status, count(RecordId)
from activity
where active is not null
/* compare with limiting record  */
and wbs.parent.parent = @limitByValue
group by status

Behavior

A label that is shown when the budget has been exceeded.

/* color for visibility */
if cost > budget then
  set :backgroundColor = 'yellow'
else
  set :caption = ''
end