# 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](field_types.md#%3Cem%3Esystem_defined_fields) 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](iql_syntax.md#_reserved_words) are case-insensitive in IQL, but field and table names are case-sensitive. In this document, we use camelcase simply for readability. > **Tip:** > > 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 records where the field named _End_ is earlier than today’s date. > > Single quotes are however only used for text values, like this: > > ``` > Name like 'Smith' > ``` > > to filter records where Name is `Smith`. > > In case the text value includes single quotes, e.g., you want to filter records where Name is `'Smith'`, then use two consecutive single quotes, besides the required ones, like this: > > ``` > Name like '''Smith''' > ``` ## 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 steps If you wish to dive deeper into IQL, or if you require information about a specific use case, try visiting the following: - How to use [IQL in forms](iql_in_forms.md) - How to use [IQL in rules](iql_in_rules.md) - Read about the [IQL syntax](iql_syntax.md) - Read about how IQL is executed in the [IQL semantics](iql_semantics.md) section - Diving into the [IQL reference](iql_references.md) - Test yourself with our [IQL challenges video](https://www.youtube.com/watch?v=0KLwLNagX_U)