EQL syntax

To find an app item by its parameters, its properties are assigned certain values in an EQL query. The structure of an EQL query follows the rules:

1. To access app properties, the codes assigned to them when they were created are used. The variable code is required to be specified in square brackets: [property_name].
2. After that, the search operation is specified. Standard mathematical signs or keywords of the EQL365 language are used.
3. The property is then assigned the value you are looking for. In doing so, you need to follow the rules for assigning values for properties of different types.
4. You can use functions to assign a value or calculate it in a query, for example, to set a specific date or search for a current employee in the Users property.
5. To search by several conditions, logical operators are added to the query. You can also specify the condition that will be checked first.
6. You can use subqueries to make complex data selections. Within a subquery, you can access the properties of any app in the system.
7. All components of the query should be separated by spaces.

начало примера

Examples of searching for items using EQL queries:

• [company_name] = 'WoodsBricks' or [company_name] like 'Woods' — search for Company app items in which the Name field matches the first specified value completely or contains the second;
• [__createdAt] = Datetime(2023, 1) and [price] > 400 search for records of the Orders app that have January 2023 in the Created field and have a value greater than 400 in the Price field.
• [contract] in (from [documents.contracts] select [__id] where [total] > 10000) search for records of the Contractors app, where the Contract field specifies the app item with the amount exceeding 10,000.

конец примера

Assigning values to properties of different types

For the correct processing of an EQL query, values for different types of properties used in the context of the apps are assigned in specific ways. See the table below for more details on the rules for assigning values:

You can search for items with no values in their fields. For this purpose, the Is null operation is used in the query. Exceptions are Phone number and Email address properties.

Search operations

Search operations are used to assign a value in an EQL query. They can be indicated by mathematical signs or keywords of the EQL365 language.

Operations using signs

These operations follow a similar principle. The first operand is a property code. A certain value is assigned to it. The second operand is a given value, another property, or a function. Such operations include:

1. Equal is represented by the symbol =. This operation is used to check for an exact match. It is not recommended to use it with properties of Date/Time type, because an exact match to the field value is unlikely.

начало примера

Examples of queries:

• [client] = 'Smith' — search for a client with the specified name;
• [payment] = [budget] — search for Payment fields that match the Budget field;
• [responsible] = CurrentUser() — search for items with the current user as the responsible user.

конец примера

2. Not equal is represented by the symbol <>. This operation is used to check the absence of an exact match.

начало примера

Examples of queries:

• [product] <> 'Equipment' — search for orders where the Product field does not contain the specified value;
• [payment] <> [budget] — search for orders where the Payment field does not match the Budget field;
• [responsible] <> CurrentUser() — search for items that do not specify the current user as responsible.

конец примера

3. Greater than  is represented by the symbol >. This operation is used to for a strict comparison of greater values.

начало примера

Examples of queries:

• [price] > 10000 — search for orders with the cost exceeding the specified value;
• [payment] > [budget] — search for orders where the value in the Payment field is greater than the one in the Budget field;
• [shipping_date] > Datetime(2023, 1, 31, 12) — search for orders with a shipping date later than the specified date and 12 PM time.

конец примера

4. Greater than or equal to  is represented by the symbol >=. This is a non-strict comparison operator.

начало примера

Examples of queries:

• [price] >= 10000 — search for orders with the cost exceeding or equal to the specified value;
• [payment] >= [budget] — search for orders where the value in the Payment field is greater than the one in the Budget field or is equal to it;
• [shipping_date] >= Datetime('Today') — search for orders with shipping on or after 00:00:00 on the current date.

конец примера

5. Less than is represented by the symbol <. This a strict comparison operator.

начало примера

Examples of queries:

• [price] < 10000 — search for orders with a cost lower than the specified value;
• [payment] < [budget] — search for orders where the value in the Payment field is less than one in the Budget field;
• [shipping_date] < Datetime('Today') — search for orders with shipping earlier that 00:00:00 on the current date.

конец примера

6. Less than or equal to is represented by the symbol <=. This is a non-strict comparison operator.

начало примера

Examples of queries:

• [price] <= 10000 — search for orders with a cost exceeding or equal to the specified value;
• [payment] <= [budget] — search for orders where the value in the Payment field is greater than one in the Budget field or is equal to it;
• [shipping_date] <= Datetime('Today') — search for orders with shipping on or earlier than 00:00:00 on the current date.

конец примера

Operations with EQL365 keywords

1. Partial string match is represented by the value LIKE. This is a case-insensitive partial string match operation. Determines whether the specified value is present in the selected app property.

начало примера

Example:

[responsible] like 'Alex' — search for all items with this value in the Assigned to field.

конец примера

2. Check for a missing value is represented by the value IS NULL. РегистронезависимаяThis is a case-insensitive operation to check an append property for an empty value.

начало примера

Example:

[budget] is null — orders where no budget is specified.

конец примера

3. Inclusion in a set is represented by the value IN. A case-insensitive operation to check the contents of an app property. Values to be searched are enclosed in parentheses, separated by commas, and not separated by spaces. In addition, the value can be a subquery.

начало примера

Examples:

• [order_number] in (6,7,8,9) — search for all orders with numbers containing the listed figures;
• [client] in ('Maria', 'Henry', 'Smith') — search for all clients with the listed names;
• [orders] in (from [documents.contracts] select [__id] where [total] > 100) — select all orders with the contracts having a value of more than 100 in the Amount field. To read more about making such expressions, see Subqueries.

конец примера

Logical operators (connectives)

Logical operators are used to check multiple conditions in a single query:

1. Logical AND: the AND operator combines several conditions. In this case, all conditions must be met.

начало примера

Examples:

• [prepayment] = 1000 and [budget] < 3000 — search for orders for which prepayment is equal to 1,000 and the budget exceeds 3,000;
• [__createdAt] >= Datetime(2023, 1) and [__createdAt] < Datetime(2023, 3) — finds all items that were created during January and February 2023, i.e., the date is equal to or greater than January and not greater than March.

конец примера

2. Logical OR: the OR operator combines several conditions and requires at least one of them to be met.

начало примера 

Examples:

• [order_number] in (6,7) or [client] is null — the search result will show the orders with the given numbers or for which the client is not specified;
• [client] like 'Алекс' or [orders] in (from [documents.contracts] select [__id] where [total] > 10000) — search for orders where the client name contains the specified value or the contract amount exceeds 10,000.

конец примера

3. Logical NOT: the NOT operator is used for one condition. It must not be met.

начало примера

Examples:

• not [payment] is null — search for all items for which any status is set, i.e. the status contains a non-empty value;
• not [client_name] in ('Петров', 'Иванов') — search for orders where the client’s name does not match the specified value.

конец примера

Priorities of logical operations

When using more than two operations in one query, you can specify which condition is prioritized. It is checked first and will be displayed in the search results above the others. In complex queries, parentheses are used to indicate the prioritized condition.

начало примера

Examples:

• not ([client_name] like 'Aisha' or [client_name] like 'Albert') — search for orders with client names that do not contain the first or the second indicated value;
• (not [client_name] = 'Alex') and [client_name] like 'Al' — search for orders with client names that do not contain the first value—Alex—but have the second value—Al.  

конец примера

Functions

Functions in EQL queries can be used to assign a value to app properties as well as to calculate it:

1. Get date function: Datetime() defines the representation of the date specified in the function parameters. Numeric values of the year, month, day, hour, minute, and second, as well as the string representation of the time zone, are specified as parameters. The only mandatory parameter is the year, the others are specified optionally. If no time is selected, the request accepts the value of zero hours, minutes, and seconds. You can also specify a runtime-calculated current date Today or current time Now. Parameters are enclosed in parentheses and separated by commas.

начало примера

Examples:  

• [__createdAt] > Datetime(2022) — search for items with a creation date later than 2022;
• [__createdAt] > Datetime(2023, 1, 31) — search for items with a creation date later than January 31, 2023;
• [finish_date] > Datetime(2023, 2, '+09:00') — search for orders that were assembled later than February 2023 given the time zone;
• [__createdAt] < Datetime('Now') — search for items with creation date earlier than the current time;
• [closing_date] > Datetime('Today') — search for deals, the closing date of which is later than the current date.

конец примера

2. Get time function: Time() defines the representation of the time specified in the parameters. Numerical values of the hour, minute, and second are specified as parameters. The only mandatory parameter is the hour. Parameters are enclosed in parentheses and separated by commas.

начало примера

Examples:  

• [closing_time] < Time(17) — search for deals closed later than 5 PM;
• [closing_time] < Time(12, 30, 00) — search for deals closed later than 12:30 PM.

конец примера

3. Function for obtaining a relative date: RelativeDatetime('start', 'end') determines a time interval calculated relative to the current date, taking into account the specified time zone in the system. The = or IN operators are used to calculate the function.

The search period is specified in parentheses. The function always specifies the start and end parameters of the period, enclosed in single quotes and separated by a comma. To calculate each parameter, a alphanumeric expression or their combination is used:

• Numeric designation: a positive or negative quantitative range for calculating the period. A minus sign is used for searching past periods, and a plus sign for future dates. Zero denotes the current date and does not require the use of a plus sign;
• Letter designation: a unit of time interval such as hour, day, year, etc.

The available letter designations for the time interval are:

The parameters are calculated sequentially. In this case, the calculation is not based on units from the current date, but on a calendar period of time.

For example, if the current date is December 11, 2023, and the start of the period in the function is specified as '-1m' (minus one month), then it will not subtract 30 days from the current date, but rather a calendar month. Thus, the start of the period in the search results will be considered as November 1, 2023.

When specifying period parameters, it is important to consider the peculiarities of their calculation:    

• the parameter start represents the start of the period relative to the current date. The calculated value is included in the search result. Examples of parameter input:
  • '0h' — search from the current date and the beginning of the current hour;
  • '+1m' — search from the beginning of the next month;
  • '-1w+1d' — he start date of the search is calculated as follows: subtract one calendar week from the current day and add one day. Thus, if the current day is Monday, the search starts from the beginning of last Tuesday.
• the parameter end represents the end of the period relative to the current date. When calculating the smallest time interval specified in the function, one unit is added. The resulting time value is not included in the search, i.e., all values ​​less than but not equal to the calculated result are considered. Examples of parameter input:
  • '0h' — search until the end of the current hour;
  • '0d' — search until the end of the current day;
  • '+1m' — until the end of the next month;
  • '-1w+1d' — the end date of the search is calculated as follows: regardless of the current day of the week, the calculation starts from Monday of the previous calendar week. Then, the day specified in the parameter is added, as well as one unit of the smallest time interval — in this case, another day. The calculation results in the previous week's Wednesday. Since all values less than but not equal to the calculated parameter are included in the end of the period, the search is performed until the end of last Tuesday.

Note that the start of the period after calculation should not be greater than or equal to its end, and the end should be less than or equal to the start. When composing such a request, you will see an error indicating an incorrectly specified format for relative dates.

начало примера

The most common examples of queries:

• [date] IN RelativeDatetime('0d', '0d') — search for the current date;
• [date] IN RelativeDatetime('-1d', '-1d') — search for the previous day;
• [date] IN RelativeDatetime('0w', '0w') — items for the current week;
• [date] IN RelativeDatetime('-1w', '-1w') — search for the previous week;
• [date] IN RelativeDatetime('-7d', '-1d') — items for the past seven days;
• [date] IN RelativeDatetime('+1w', '+1w') — considers the entire upcoming week;
• [date] IN RelativeDatetime('-1w+2d', '-1w+3d') — search from last Wednesday to the end of last Thursday;
• [date] IN RelativeDatetime('-1m', '+1m') — search from the beginning of last month to the end of next month;
• [date] IN RelativeDatetime('0y+3q','0y') — items for the third and fourth quarters of the current year, i.e., search from the third quarter to the end of the year;
• [date] IN RelativeDatetime('0y-2m', '0y-1m') — search for the last and second-to-last month of the previous year;
• [date] IN RelativeDatetime('+1y', '+1y0m') — considers the entire first month of the next year.

конец примера

4. Count function: Count() is a case-insensitive function that determines the number of elements of a parameter. The parameter can be a property containing a multiple value or a subquery. In comparison operations, the function is specified before the calculated parameter without a space.

начало примера

Examples:

• count([orders]) > 3 — search for companies for which more than three orders have been created;
• count(from [documents.contract] where parent.[__id] in [client] and [total] > 10000) > 2 —search for companies for which more than two contracts were created, with the total amount exceeding 10,000.

конец примера

5. Function for retrieving the current user: CurrentUser() returns the identifier of the current user. Used to determine the value of an app property.

начало примера

Example:

[responsible] = CurrentUser() — orders assigned to the current user.

конец примера

6. Function for retrieving an item from a field of the Arbitrary app type: Refitem() returns items whose fields reference a specific item from another application. The function specifies the path to the item, including the section code and the application where it is created, and the string representation of its identifier.

Parameters are specified in two ways. Each value is enclosed in single quotes and separated by a comma. Also, the entire path can be specified in single quotes and parameters can be combined with a colon.  

начало примера

Examples:

• [bill] = Refitem('documents', 'bills', '018a8dbb-04cd-7798-a363-aae245148b10') or [bill] = Refitem('documents:bills:018a8dbb-04cd-7798-a363-aae245148b10') —  equivalent queries where a search is conducted based on the Bill field in items of the Contracts app. As a result, the contract associated with a specific bill from another workspace and app will be displayed;

• [bill] is null — search for contracts where the bill is not specified, i.e., there is no reference to an item from the Bills app.

конец примера

Subqueries

A subquery is a nested EQL query that is part of another query. When composing an expression from several queries, the subquery is highlighted with parentheses.

начало примера 

Example:

not ([__name] like 'Alex' or [__name] like 'An') — items whose name does not contain the specified values.

конец примера  

In a subquery, you can access the app elements from other workspaces of the system. For this purpose, the operator of data selection by criteria is used: FROM _ SELECT _ WHERE.

To make an expression, you first need to specify the source of the selection—the app. Then, a property in this app is defined. Next, you specify the condition on which the subquery is filtered. Thus, all three operators are present in the subquery.

начало примера

Example:

[clients] in (from [clients.orders] select [__id] where [total] > 1000) — selection of clients for whom orders with the sum of 1,000 were created.

конец примера

Only FROM _ WHERE operators are specified in the subquery for the Count function.

начало примера

Example:

count(from [bookstore.book] where parent.[__id] in [authors]) > 0 — search for authors with at least one book in their profile.

конец примера

Subqueries can include other subqueries. To indicate the level of nesting, operators are used to access app properties:

• PARENT — used to access the properties of the parent app;
• ROOT — accesses the fields of the root app, which is specified at the beginning of the query. For example, it can be the application on the page on which the EQL search is performed.

начало примера

Examples:

• count(from [clients.contacts] where parent.[__id] in [contact]) > 1 — search for orders with more than one client specified;
• count(from [bookstore.book] where root.[__id] in [authors] and (count(from [bookstore.copyright] where parent.[__id] = [book] and [finish_date] > Datetime('Now')) > 0)) > 1 — search for book authors with more than one valid book rights agreement.

конец примера

Was this helpful?

Thanks for your feedback!

Please specify why:

Recommendations did not help meArticle is hard to understandDidn`t answer my questionContent does not match the topicOther

How we can improve it?

YesNo

Please specify whyRecommendations did not help meArticle is hard to understandDidn`t answer my questionContent does not match the topicOther

Found a typo? Highlight the text, press ctrl + enter and notify us