EQL

EQL (ELMA Query Language) – is a language for requesting data, stored in ELMA, which extends the capabilities for creating complex data selections, i.e. create a filter even with criteria, which are not included in the basic filter settings.

EQL-query – is a structured query composed in EQL for requesting ELMA data, which allows creating complex data selections.

The structure of an EQL-query is similar to the structure of SQL-queries. Later in the text you can read about the main operators and features of EQL-queries.

The contents of this section:

1. Syntax

General guidelines for creating queries
Logical operators
Comparison operators
Other operators

2. Functions

3. Subqueries

Syntax

General guidelines for creating queries

Simple fields (string, number) can be compared with other fields of the same type or with constants.
The fields containing a link to an object, can be compared only to the ID of this object (E.g. Contacts = 2 – search contractors, which have contacts with the ID 2).
There are only three types of constants:
Numbers (integers, fractions);
Strings;
Boolean set elements (true, false).
The other constants are created with functions (e.g. dates: StartDate= DateTime(2016, 03, 02, 11, 00)) or stings in functions (e.g. OR TypeUid = TypeReference('Task') OR Uid = Guid('xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')).
String constants are put in single quotes (Name LIKE ’%LTD%’).
A full period (".") separates the integral and fractional part in a number.
To add a "’" (apostrophe) to the string, type it twice (e.g.: Name LIKE ’%’’Sign document package’’ task%’ – search all the tasks, whose name contains the words "’Sign document package’ task").
To create queries, you need the data about the system objects structure. You can find the structure of an object in ELMA Designer if you open the Objects tab, select an object and go to the Properties tab of its page. It contains the list of all the object properties, their names, and types.

Logical operators

Logical operators (AND, OR, NOT) connect several conditions.

Priorities of the logical operators

If a query contains several operators in a row, they are executed in the following order:

Expressions in parentheses.
Complementary operator NOT.
Logical operators AND.
Logical operators OR.

Parentheses

These operators are used for setting priority among expressions in complex queries:

(Name LIKE ’%LTD%’ OR Name LIKE ’%JCS%’) AND (Region = 6) – search contractors in a specific region, whose names contain "LTD" or "JSC".

Parentheses are used along with the OR operator to signify its priority over the AND operator.

AND

When you use this operator, both subconditions, connected by the operator, have to be met:

Name LIKE ’%Jefferson%’ AND Name LIKE ’%Brian%’ – search contractors, whose names include "Jefferson" and "Brian", while any characters can be on either side of these words.

When you use this operator, only one of the subconditions has to be met:

Name LIKE ’%LTD%’ OR Name LIKE ’%JSC%’ – search contractors, whose names include "LTD" or "JSC", while any characters can be on either side of these words.

NOT

This operator is used to deny the condition, which follows it:

NOT (Name LIKE ’%LTD%’ OR Name LIKE ’%JSC%’) – search contractors, whose names do not include "LTD" or "JSC".

Comparison operators

Comparison operators allow comparing several object fields, expressions values or constants. If the condition is true and is met, the value of the entire expression is TRUE, otherwise it is FALSE.

Equal to =

Operation for defining whether the first operand equals the second. The the letter case is taken into account when comparing.

If you don't need an exact comparison of strings, but to check if the string contains a substring, use the LIKE operator.

It is not recommended to apply this operation to dates, since the exact match of a date and time is unlikely. You should apply the following operations to dates: <, >, <=, >=.

Responsible = CurrentUser() – search contractors, the current user is responsible for.

Not equal to <>

Check if the operands are not equal, case-sensitive.

Responsible <> CurrentUser() – search contractors, the current user is not responsible for.

Less than <

The left part of the expression has to be less than the right part.

AnnualIncome < 2500000 – search contractors, whose annual income is less than 2500000.

Greater than >

The left part of the expression has to be greater than the right part.

AnnualIncome > 500000 – search contractors, whose annual income is greater than 500000.

Less than or equal <=

The expression will be true, if the left part is less or equal to the right part.

CompanyDay <= DateTime(1989, 01, 21) – search contractors, whose date of establishment is less than or equal to 21.01.1989.

Greater than or equal >=

The expression will be true, if the left part is greater or equal to the right part.

CompanyDay >= DateTime(1989, 01, 21) – search contractors, whose date of establishment is greater than or equal to 21.01.1989.

Other operators

The operators are not case-sensitive, i.e. the request results will be the same, regardless of the case of the text (for the query Name LIKE ’%World%’, the results will be "World" and "world").

LIKE

This operator is used only for string variables. The operator compares a string field with the specified mask (template), and an object is included in the search results if the condition is fulfilled.

You should use this operator with string masks, e.g. ’%World%’. If you specify a particular string in single quotes, the action of this operator will be the same as the one of the equal to operator.

For example, the following queries are equal:

(Name LIKE ’%LTD%’ OR Name LIKE ’%JSC%’)

(Name = ’%LTD%’ OR Name = ’%JSC%’)

Mask (template) can include the following characters:

% – stands for any number of symbols.

Name LIKE ’%LTD%’ – search contractors, whose names include "LTD", with any characters on either side;

_ – stands for one or several characters (depending on the position in the text).

Name like ’_A%’ – search contractors, whose names have "A" as the second letter;

Name like ’__A%’ – search contractors, whose names have "A" as the third letter;

quotes – search strings, which include only single quotes ("'").

Name LIKE ’%’’Sign document package’’ task%’ – search tasks, whose name include "’Sign document package’ task".

This operator sets correspondence between the value of a certain field and each element, specified after it in parentheses. The IN operator can be used for comparing a field with a few elements.

CreationAuthor in (CurrentUser(), 1) – search contractors, created by the current user or by the user with ID 1.

IS NULL, IS EMPTY

IS NULL – check if the left part of the operator equals Null. Applied to objects with single selection. For example, you can select only one Industry for a contractor.

Industry IS NULL – search contractors with the Industry field empty.

IS EMPTY – applied to objects with multiple selection.

Contacts IS EMPTY – search contractors with the list of contacts empty.

Functions

To learn more, see the respective page.

Subqueries

To learn more, see the respective page.