IQL (IS Tools Query Language)

IQL (IS Tools Query Language) is an SQL-like language used to express conditions, relationships and rules on top of a data model. There are many areas where IQL may be used when configuring an application, such as deciding what data to show in a form, or what should happen when a button is clicked.

Introduction

To start with, we are going to take a look at how IQL can be used to filter records.

Example

Salary > 100000

Above is a basic example of an IQL filter, which matches all records where the field Salary is larger than 100 000. If you are familiar with SQL, this should look familiar, and you may realize that this corresponds to the WHERE clause of a SQL query.

You can combine multiple conditions using the logical operators AND and OR:

Examples

Salary > 100000 AND Role = 'Engineer'
RecordName = 'Adam Smith' OR RecordName = 'John Smith'

The first example means that both conditions must be fulfilled, so it finds engineers whose salary is at least 100 000. The second example will find records that match either (or both) condition: the name of the record must either be Adam Smith or John Smith.

RecordName is a reserved keyword that selects the system defined field record name. Other system defined fields used as keywords/functions are _RecordId , RecordName, RecordGroup, CreatedTime, UpdatedTime, CreatedDate, UpdatedDate, UpdatedUser, CreatedUser, CreatedUsername, UpdatedUsername, CreatedRealname, UpdatedRealname. All keywords are case-insensitive in IQL, but field and table names are case-sensitive. In this document, we use camelcase simply for readability.

If there is a field name that contains spaces, special characters or overlaps with a reserved keyword in IQL, you can use double quotes to indicate you are referring to the field. For example, since end is reserved, you can use

"End" < today()

to filter on a field named End. Single quotes are however only used for text values.

Related data

What we have looked at so far can already be expressed using basic filters. Where IQL shines though, is its ability to express conditions on related data.

In SQL, there is something called a join - a way to combine data from several tables for either retrieval or filtering purposes. IQL does not have joins, but you can still express conditions on related tables using the dot operator:

Example

Department.Name = 'Marketing'

Here Department must be relation field pointing to another table, and Name is a text field that belongs to that table. This filter would match all employees that belong to the marketing department.

The dot operator may be chained to traverse multiple relations:

Example

Department.Location.Country = 'Sweden'

You can also express conditions based on tables that point towards you. For example, here is a filter that matches all managers who have at least one employee whose name starts with an 'A':

Example

RecordName IN (Manager FROM Employee WHERE recordname LIKE 'A*')

There is a lot of things going on here. The IN operator is something we haven’t seen before, and what is that thing within parentheses? To explain this, we have to take a look at IQL queries.

Queries

An IQL query is a generalization over an IQL filter that not only contains what data to match, but also what data to return.

Example

RecordName, Salary FROM Employee WHERE Department.Name = 'IT'

Running this query would return a list of all employees from the IT department, and their salaries.

IQL queries are used to retrieve data for charts. The type of chart affects how the data is interpreted. If for example a bar chart is used, each bar would correspond to an employee and the height of the bar would be the employee’s salary.

For advanced usage, you can also use aggregate functions to condense multiple rows into single values. For example, there are functions to count, sum, or compute the max, min, or mean of a field value for a set of records.

Example

avg(Salary) FROM Employee WHERE Department.Name = 'IT'

This would compute the average salary of the IT department. Aggregate functions can also be used in conjunction with the GROUP BY keyword, to compute partial aggregates:

Example

Department.Name, avg(Salary) FROM Employee GROUP BY Department.Name

Result

Department.Name avg(Salary)

IT

200000

Marketing

150000

The value of the second column is the average salary within that department.

Subqueries

As we saw in the last section, queries can be nested inside a filter or another query. The IN operator is used to match a single value against the result of another query. One of its many uses is to traverse a relation field in the opposite direction.

Say that you want to find all departments with employees making at least $100 000. You might come up with this query:

Example

Department FROM Employee WHERE Salary > 100000

However, this has a problem: it matches employees and not departments. An implication of this is that the same department may be listed twice. It can also not be used as a filter for a section that lists departments.

To fix this shortcoming, we need to use the IN operator and a subquery:

Example

RecordId IN (Department FROM Employee WHERE Salary > 100000)

The above expression is a valid IQL filter that filters departments, not employees.

Subqueries can be used with any operator, not just the IN operator. Below are examples of two filters, one that matches employees that have a higher than average salary, and one that finds the employee with the highest salary:

Examples

Salary > (avg(Salary) FROM Employee)
Salary = (max(Salary) FROM Employee)

Next step

The previous section was a brief introduction to IQL. If you want to learn more about any specifics, consider checking out any of these: