# IQL Syntax

> Learn the IS Tools IQL syntax for queries, filters, and statements. Covers expressions, parameters, literals, reserved words, operators, and functions.

Let’s take a look at the syntax of IQL queries and filters. We are going to start from the top, and gradually break down what each expression means until we reach the "atoms" that make up IQL.

> **Tip:**
> 
> To manually display predictive text in the IQL editor, tap `Ctrl+Space` on your keybord. Once you find the wished string, hit Enter to add it or click on it.

## Queries

The entry point of an IQL query is:

```
Query ::= <ResultColumn> FROM <Table> WHERE <Filter> GROUP BY <Expr> ORDER BY <Expr>
```

Each keyword is used to indicate how to interpret the next expression. The keywords `WHERE`, `GROUP BY` and `ORDER BY` (and their corresponding expressions) are all optional, but must appear in the order above.

_Table_ above is simply the of the table. It consists of either an identifier (a text string consisting of the letters A-Z and digits 0-9), or any text string within double quotation marks `"`.

Similar identifiers are used in many places within IQL. They are interpreted as table names, field names, list items, rule names, or functions depending on their surrounding context.

## Filters

A _Filter_ can be used as a part of a query, or as an entrypoint itself, and is defined by the following rule:

```
Filter ::= <Comparison> | <Filter> AND <Filter> | <Filter> OR <Filter>
```

A filter is a _boolean expression_, that is, an expression that evaluates to true or false. It is either a comparison, a conjunction (`AND`) or a disjunction (`OR`). Each comparison must be further broken down:

```
Comparison ::= <Expr> <BooleanOperator> <Expr>
             | <Column> <LikeOperator> <Literal>
             | <Expr> IS NOT NULL
             | <Expr> IN ( Query )
             | <Expr> NOT IN ( Query Relation from Table where Relation IS NOT NULL)*
             | <Expr> IN ( ValueList )
             | <Expr> NOT IN ( ValueList )▲
             | <Expr> NOT IN ( ValueList ) OR ValueList IS NULL♦
```

Here _BooleanOperator_ is simply one of the operators `=`, `!=`, `<`, `>`, `>=`, `<=`, while _LikeOperator_ are the keywords `LIKE`, `STARTSWITH`, `ENDSWITH`, and `CONTAINS`. To read more about these operators, see [the reference](iql_references/operators.md).

> **Caution:**
> 
> Be aware that using _NOT IN_, as referred above, has some important remarks:
> 
> \*<Expr> NOT IN ( Query Relation from Table where Relation IS NOT NULL)
> 
> The part that says _where Relation IS NOT NULL_ **must be always included** if _NOT IN_ is used for a related table. Otherwise, the comparison will not work. For example, the following query will result displaying which warehouses are not storing any material items. Without the _where "Warehouse assigned" is not null_, the result will be null.
> 
> ```
> RecordName not in ("Warehouse assigned" from "Material item" where "Warehouse assigned" is not null)
> ```
> 
> ▲<Expr> NOT IN ( ValueList )
> 
> For example,
> 
> ```
> "Invoice approved" not in(Yes)
> ```
> 
> Would result in records where "Invoice approved" drop-down is "No", but it would not show records with the drop-down blank.
> 
> ♦<Expr> NOT IN ( ValueList ) OR ValueList IS NULL
> 
> ```
> "Invoice approved" not in(Yes) or "Invoice approved" is null
> ```
> 
> Would result in records where "Invoice approved" drop-down is "No" or blank.

Filters can be grouped using parentheses.

## Statements

_IQL statements_ is a further generalization of IQL. While filters and queries are declarative, statements are procedures that may cause side effects. Statements are the entrypoint for _Run IQL_ rules, _Behavior_ in forms, and _Run on x_ actions in forms.

```
Statements ::= <empty> | <Statement> <Statements>

Statement ::= SET <AssignOperations>
            | SET <Global variable> = <Expr>
            | SET <Tuple> = <Expr>
            | SET <Array> = <Expr>
            | LET <Local variable> = <Expr>
            | LET <Tuple> = <Expr>
            | LET <Array> = <Expr>
            | AWAIT <Local variable> = <Expr>
            | IF <Expr> THEN <Statements> END
            | IF <Expr> THEN <Statements> ELSE <Statements> END
            | FOR <LoopQuery> DO <Statements> END
            | FOREACH <Variable> IN <Array> DO <Statements> END
            | INSERT <Table> VALUES <AssignOperations>
            | CALL <Function>
            | TRIGGER <Rule>

AssignOperations ::= <AssignOperation> | <AssignOperation> , <AssignOperations>

AssignOperation ::= <Field> = <Expr> | <PseudoField> = <Expr>
```

> **Caution:**
> 
> `TRIGGER`, `FOR` and `INSERT` statements are only valid in [Rules](iql_in_rules.md). `CALL`, `AWAIT` and `FOREACH` are only valid in [Form rules](iql_in_forms.md) configurations.

## Expressions

_Expr_ above is a generic expression. It can be a field name path, a pseudo field, a literal value, a variable, a tuple, an array, a function, or any mathematical expression.

```
Expr ::= <FieldPath>
       | <Literal>
       | <Variable>
       | <Array>
       | <Tuple>
       | <Expr> <MathOperator> <Expr>
       | <Expr> || <Expr>
       | <FunctionCall>
       | ( <Query> )

FieldPath ::= <PseudoField>
            | <Field>
            | <Field> . <FieldPath>
```

Here _MathOperator_ is the arithmetic operators `+`, `-`, `*` and `/`. Double pipe `||` signifies text concatenation. Expressions can also be grouped using parentheses.

`'my text'`, `@param`, `(Salary + 1000) / 2`, `recordname || '_2'` and `substring(Department.Name, 1, 5)` are all examples of expressions. Examples for tuple and array expressions are `(Yes, ‘John Smith’)` and `[Number1, Number2, Number3]`, respectively.

## Literals

A literal is a constant value hard-coded into the IQL. E.g., the expression `Salary + 10` has one literal, the number `10`.

| Literal Type | Examples |
| --- | --- |
| Text | `'marketing'` `'an example text value'` `'10'` `'a ''single quote'' within a text value'` |
| Number | `5` `-1` `6.25` |
| Date (or datetime) | `2024-11-05` `2010-02-22T18:15:00` |
| Boolean (checkbox) | `true` `false` |

> **Note:**
> 
> Date literals are expressed in ISO 8601 format without a timezone designator. The time part is optional and defaults to midnight if omitted.

## Functions

There are a number of [functions](iql_references.md) available to perform calculations or other transformations of the data. A function call is dictated by the grammar rules:

```
Function ::= <IDENTIFIER> ( <ParameterList> )
ParameterList ::= <Expr> | <Expr> , <Parameter>
```

In other words, `today()`, `length(Text)` and `substring('text', start index, length)` are all function calls.

> **Note:**
> 
> Aggregate functions (count, sum, avg, min, max) are syntactically just functions, but they are only valid to be used in the "ResultColumn" part of a query.

## Parameters

A parameter is a part of a function that acts as a placeholder for data. For example, the function 'substring('text', start index, length)' has three parameters, while the function `length(Text)` has only one parameter. Depending on the function, the parameter may be a literal, a variable, a field, or even a more complex expression such as nested function.

```
length('Some string')
length(@variable)
length(Field)
```

## Variables

A variable is a special type of literal which can change value during runtime. A variable is indicated by an identifier that starts with a `@`. Be aware that variables are case sensitive.

For [IQL in forms](iql_in_forms.md) there are two types of variables: global and local. A global variable is declared with the `set` statement, and can be called anywhere within the same form, while a local variable is declared with the `let` or `await` statement to be used in a later action within the same editor.

```
let @localVariable = <Expr>
set @globalVariable = <Expr>
```

For [IQL in rules](iql_in_rules.md), variables are declared with `let` or via the [trigger](iql_in_rules/trigger_a_rule_with_variables.md) function.

In both forms and rules, variables can be used as parameters in functions.

> **Tip:**
> 
> For code completion, type `@<ctrl+space>` to get suggestions of variables.

> **Note:**
> 
> Variables lose their data type when they are transferred between different IQL editors, and may require the use of additional functions to ensure the correct use of the value they store.
> 
> For example, the variable @myDate, declared in a Selector’s IQL editor as _set @myDate = DateField_, would not work correctly if used in a Label’s IQL editor as _set :caption = @myDate + 1_ since it ignores that the variable is a date with an extra day. In this case, the variable will require the use of functions to parse and format the value as a date.

> **Caution:**
> 
> Variables with special characters or keywords are not supported; e.g. `@gör` `@and`

## Tuples

A tuple is a collection of elements of any type that belong to a specific record. You can use tuples in IQL to integrate multiple fields or variables into a single statement, with the syntax:

```
set (resulting element1, resulting element2) = (source element1, source element2)
```

Where the source element may be a field, a variable, a function or a query.

Note that parenthesis are mandatory to collect the resulting elements, and also to collect source elements other than queries.

```
set (<field>, <field>) = (<field>, <field>)
set (<field>, <field>) = (<variable>, <variable>)
set (<variable>, <variable>) = <function>
let (<variable>, <variable>) = (<field>, <field>)
let (<variable>, <variable>) = sum(<field>), max(<field>) from Table where Relation = <variable>
```

## Arrays

An array is a collection of elements of the same type that belong to a specific table. You can use arrays in IQL to handle multiple records, fields, variable or values simultaneously, opening up new workflow possibilities. The syntax may vary depending on the use, but generally:

```
let @array1 = ['string1', 'string2']
let @array2 = <Array function>(@arrayA, @arrayB)
let @array3 = <Array function>([Number1, Number2, Number3])
let @array4 = collect Recordid from <Table> where <Condition>
```

```
for <table> @array do
   set <Field> = <Expr>
end
```

Syntax exclusive to [Form rules](iql_in_forms.md):

```
set @array = @selectedRecords
```

```
foreach @id in @array do
   call create()
end
```

```
foreach @index in range(1, <Number>) do
  call create()
end
```

## Reserved words

The following words have special meanings in IQL. Although written in uppercase here, they are not case-sensitive.

| Keywords | `FROM` `WHERE` `GROUP` `ORDER` `BY` `DESC` `IF` `THEN` `ELSE` `END` `SET` `LET` `CALL` `TRIGGER` `FOR` `DO` `INSERT` `VALUES` `AWAIT` |
| --- | --- |
| Operators | `AND` `OR` `IS` `NOT` `IN` `LIKE` `STARTSWITH` `ENDSWITH` `CONTAINS` |
| [System defined fields](field_types.md#_system_defined_fields) | `CREATEDDATE` `UPDATEDDATE` `CREATEDTIME` `UPDATEDTIME` `CREATEDUSER` `UPDATEDUSER` `CREATEDUSERNAME` `UPDATEDUSERNAME` `CREATEDREALNAME` `UPDATEDREALNAME` `RECORDID` `RECORDNAME` `RECORDGROUP` `RECORDURI` |
| Special values | `NULL` `TRUE` `FALSE` |
| Other | `USER` `NONE` `FORM` |

## Comments

IQL has two styles of comments. Two minus signs `--` is used to comment the remainder of the line. Anything within `/*` and `*/` is also considered a comment.