Complex filters. EQL language
EQL (ELMA Query Language) – is a language for requesting data, stored in ELMA. It extends the system capabilities to create complex data selections, i.e. create a filter with criteria, not included in the basic filter settings.
An EQL query – is a structured request in the EQL language to the ELMA data, which allows creating complex data selections.
A Query field is created for all the filters in the system, in which you can specify the selection conditions. To check a selection condition and immediately see the result, in ELMA 3.9 and higher you can run a query in Administration - System - Filtration of objects. In this section, select an object type, enable EQL search, and enter a selection condition:
.png)
Fig. 1. Filtration of objects section
In this section, you can create a correct EQL query before using it in scripts or modules.
Note:
- When comparing strings, the case of letters is taken into account depending on the database settings.
- Names of properties, objects, and functions are case-sensitive.
- Keywords are not case-sensitive, e.g. and, or, empty, not, parent, root, from, select, where, count.
- Operators are not case-sensitive, e.g. in, is.
- When comparing fractions you must put a dot between the integer and the decimal parts.
Syntax
1. Comparison operators.
1.1. Equal =
The Equal operator should be used to check if the first operand equals the second. It is not recommended to use this operator for dates since the exact match of date and time is unlikely. To compare dates, use the inequation operator and the in, is operators.
Tasks search example:
Executor = CurrentUser() – search tasks, whose executor is the current user.
1.2. Inequation operator <>
Check if values are unequal. Comparison of strings is case-sensitive.
Tasks search example:
Executor <> CurrentUser() – search tasks, whose executor is not the current user.
1.3. Less than operator <
The less than operator indicates that the left operand is less than the right operand.
Contractors search example:
AnnualIncome < 2500000 – search contractors whose annual income is less than 2500000.
1.4. More than operator >
The more than operator indicates that the left operand is greater than the right operand.
Contractors search example:
AnnualIncome > 2500000 – search contractors, whose annual income is more than 2500000.
1.5. Less than or equal operator <=
The less than or equal operator indicates that the left operand is less than or equal to the right operand.
Tasks search example:
CreationDate <= DateTime(2016, 10, 14) – search tasks, whose creation date is less than or equal to 14.10.2016.
1.6. More than or equal operator >=
The more than or equal operator indicates that the left operand is more than or equal to the right operand.
Tasks search example:
CreationDate >= DateTime(2016, 10, 14) – search tasks, whose creation date is more than or equal to 14.10.2016.
1.7. IN operator
This operator defines a correspondence between the specified field value and each element, specified in parentheses after it. This operator is used for executing sub-queries (examples are provided below).
Tasks search example:
Priority in (’High’,’Medium’) – search tasks, whose priority is high or medium.
Note: If the field is an object, then you need to pass field values as IDs, e.g. Contacts in (1, 2, 3). If this field is a simple type, then values can be passed as specific values, e.g. Name in (’Name1’, ’Name2’).
1.8. IS operator
This operator is used only with NULL or EMPTY.
IS NULL is used for objects with the single selection.
IS EMPTY is used for objects with the multiple selection.
Contractors search example:
Industry IS NULL – search contractors, whose Industry field is empty.
Contacts IS EMPTY – search contractors, whose list of contacts is empty.
1.9. LIKE operator
This operator is used to compare only string variables. The operator compares the specified string field with a mask (template) and if the condition is met, the object is added to the selection. The LIKE operator should be used with a mask (template), otherwise, it will be the same as the equal operator.
Mask (template):
1) % - means any number of arbitrary characters.
Tasks search example:
Subject like ’%Subject%’ – search tasks, whose subject includes the word Subject, which can be surrounded by any other characters.
2) _ -stands for one arbitrary character. Can be used several times, to signify two and more characters.
Tasks search example:
Subject like ’_ubject%’ – search tasks, whose subject can contain an arbitrary first character, followed by "ubject" and any characters after that.
Similarly, you can use several characters, e.g. Subject like ’__bject%’.
3) ‘ – stands for quotation marks. Example: a task name includes quotation marks and you need to find it. To use quotation marks in a query, they must be duplicated, i.e. escaped.
Tasks search example:
Subject like ’%’’Contractor LTD’’%’ – search tasks, whose subject includes the ’Contractor LTD’ string.
2. Logical operators.
2.1. AND – logical AND.
In a query, two sub-conditions must be executed, which are connected by this logical operator.
Tasks search example:
Subject like ’%Subject%’ AND Executor = CurrentUser() – search tasks, whose subject includes the word Subject and whose executor is the current user.
2.2. OR – logical OR.
In a query, one of the sub-conditions, connected by this logical operator, must be executed.
Tasks search example:
Subject like ’%Subject%’ OR Subject like ’%task%’ – search tasks, whose subject includes the word Subject or task.
2.3. NOT – logical NOT.
A sub-condition must not be executed in a query.
Tasks search example:
NOT Subject like ’%Subject%’ – search tasks, whose subject does NOT include the word Subject.
3. Priority management
To indicate priority among expressions in complex queries, use parentheses.
Contractors search example:
(Name LIKE ’%LLC%’ OR Name LIKE ’%LTD%’) AND (Region = 6) – search contractors, whose region ID is 6 and whose name includes the words "LLC" or "LTD".
Parentheses are used with the OR operator, to indicate its priority over the AND operator.
4. Sub-queries
Sub-query – is an EQL query, nested in another EQL query (is a part of another EQL query). The maximum nesting level of operators is 1000. Sub-queries must be enclosed in parentheses.
Sub-queries allow filtering root objects by properties of related objects.
Sub-queries can be composed according to one of two rules:
FROM entity SELECT property WHERE expression
SELECT property FROM entity WHERE expression,
where entity – entity, with which the sub-query works;
property – field of the current entity;
expression – expression for filtering entity instances, can also be a nested query.
Contacts search example:
Contractor in (from Sale select Contractor where Contractor = PARENT.Contractor and SaleStatus in (Enum(’Postponed’))) and Contractor in (Responsible = CurrentUser()) – search contacts, with postponed deals for the current user.
There is a simpler way of creating sub-queries.
Contractors search example:
Contacts in (Surname = ’Smith’) – search contractors, which have contacts with the surname Smith.
PARENT – address to the parent query properties.
Contacts search example:
Contractor in (from Sale select Contractor where Contractor = PARENT.Contractor) – search contacts, whose contractor is a contractor in deals.
ROOT – address to the root query properties.
Tasks search example:
Contractor in (FROM Contact SELECT Contractor WHERE Name = PARENT.Subject AND Contractor in (FROM Sale SELECT Contractor WHERE Name = ROOT.Subject)) – search tasks, where the deal name matches the task name.
Important: if a property name matches a reserved word (not case-sensitive), then this property must be in brackets. [Parent] = 1.
5. COUNT() – function for creating complex sub-queries, whose result is an integer.
Rules for writing functions:
5.1. COUNT(property), where property is a list-type field.
Contractors search example:
COUNT(Contacts) > 0 – search contractors that have contacts.
5.2. COUNT(query), where query – is a sub-query, written according to the following rule:
FROM entity WHERE expression, where
entity – entity, with which the sub-query works;
expression – expression for filtering entity instances, can also be a nested query.
Contractors search example:
COUNT(FROM Contact WHERE NOT Skype IS NULL AND Contractor = PARENT.Id) >= 1 – search contractors, which have at least one contact, with the condition that the Skype field of the contacts is filled in and the contractor's contact matches the searched one.
5.3. COUNT(function), where function – the QueryInFunction function, which returns a list as a result.
Search example:
COUNT(GroupUsers(1)) – the number of users in the user group with the ID 1.
6. Functions.
There are two types of functions:
QueryFunctionResult – function for getting a specific value.
QueryInFunction – function for getting multiple values, used after the in operation.
6.1. CurrentUser() – (QueryFunctionResult) function for getting the current user. Converts a user into a user ID, i.e. an integer, in a query.
Tasks search example:
Executor = CurrentUser() – search tasks, whose executor is the current user.
6.2. Enum(’enumeration value name’) – (QueryFunctionResult) function for getting an enumeration.
Tasks search example:
Priority = Enum(’Low’) – search tasks with low priority.
An enumeration can be acquired easier: pass the value in quotation marks.
Tasks search example:
Priority = ’Low’ – search tasks with low priority.
6.3. DateTime(year, month, day, [hour], [minute], [second], [millisecond]) – (QueryFunctionResult) function for getting a date. Optional parameters are in parentheses.
Tasks search example:
CreationDate <= (DateTime(2016, 10, 17, 1, 30)) – search tasks created on October 17, 2016 at 1:30 or earlier.
In this function you can use the following parameters:
1) ’Now’ – get the current date to the second.
Example: CreationDate <= DateTime(’Now’)
2) ’Today’ – get the current date (only date, without time).
Example: CreationDate <= DateTime(’Today’)
3) ’MinValue’ – to get the minimum date.
4) ’MaxValue’ – to get the maximum date.
6.4. RelativeDateTime(’relative period start date’, ’relative period end date’) – (QueryInFunction) get a time interval between relative dates. One of the function parameters can be empty. If the first parameter is empty, then the restriction will apply only to the second parameter, if the second parameter is empty, then the interval will start with the first parameter. To learn more about the string expression of a relative date, read Help.
Tasks search example:
CreationDate in RelativeDateTime(’-1m’, ’-1m’) – search tasks, whose creation date is in the last month.
6.5. Guid(’string representation of a UID’) – (QueryFunctionResult) function for getting a UID.
Tasks search example:
Uid = Guid(’EE90CE1D-0591-466B-87FF-06C4D4282EB1’) – search tasks with UID = EE90CE1D-0591-466B-87FF-06C4D4282EB1.
There is an easier way to specify a Guid: pass a value in quotation marks.
Tasks search example:
Uid = ’EE90CE1D-0591-466B-87FF-06C4D4282EB1’
6.6. TypeReference(’object class name’) – (QueryFunctionResult) link to an object type. You can find the object class name on its page in the Designer.
Tasks search example:
TypeUid = TypeReference(’SMARTTask’) – search tasks of the SMART task type.
There is an easier way to specify an object type: pass a value in quotation marks.
Tasks search example:
TypeUid = ’SMARTTask’
Note that you can specify a task type using a Guid:
Tasks search example:
TypeUid = ’7EC44AA8-CB49-4CEF-882A-D4752608B663’
TypeUid = Guid(’7EC44AA8-CB49-4CEF-882A-D4752608B663’)
6.7. SubTypes(’object class name’) – (QueryInFunction) links to object types and their child types. You can find the object class name on the object page in the Designer.
Tasks search example:
TypeUid in SubTypes(’Task’) – search tasks of the Task type and the child types, e.g. SMARTTask, KpiTask.
6.8. Reference(object type ID, object ID) – (QueryFunctionResult) object value. A Guid or object class name can be the first parameter. Different ways of specifying an object class or a Guid are described in 6.6 TypeReference.
Questions search example:
RefObject = Reference(’298b2c71-619f-463c-95b2-8e029085680d’, 204) – search questions, defined in the ’298b2c71-619f-463c-95b2-8e029085680d’ (task) object type with the ID of this object - 204. A similar way to write this: RefObject = Reference(’Task’, 204)
6.9. DropDownItem (’drop-down list value’) – (QueryFunctionResult) drop-down list value. As the parameter, specify one of the drop-down list values, specified when creating the property.
Important: the drop-down list value must be identical to the value in the database! For instance, if the value is specified with a key in the database, then you must pass the key as well as the value.
Object search example:
List = DropDownItem(’value1’) – search objects, whose List property value is "value1".
There is an easier way to specify a drop-down list value: pass a value in quotation marks.
Object search example:
List = ’value1’
6.10. WorkTime(hours, minutes) – (QueryFunctionResult) working time interval.
Object search example:
Interval > WorkTime(1, 0) – search objects with interval more than one hour.
6.11. CurrentUserGroups() – (QueryInFunction) returns groups of the current user.
6.12. GetChiefByUser(user ID) – (QueryInFunction) search all the superiors of the user according to the organizational structure.
Contractors search example:
Responsible in GetChiefByUser(1) – search all the contractors, for which superiors of the user with ID=1 are responsible.
6.13. GetUserSubordinate(user ID) – (QueryInFunction) search all the subordinates according to the organizational structure.
Contractors search example:
Responsible in GetUserSubordinate(101) – search all the contractors, for which subordinates of the user with ID=101 are responsible.
6.14. GroupUsers(group ID) – (QueryInFunction) search all the participants of the user group.
Contractors search example:
Responsible in GroupUsers(109) – search all the contractors, for which users of the group with ID=109 are responsible.
6.15. UserGroups(user ID) – (QueryInFunction) search all the user groups, participated by the user, whose ID is specified in the function.
Contractors search example:
Id in UserGroups(102) – search all the user groups, participated by the user with ID=102.
6.16. UserOrganisationItems(user ID) – (QueryInFunction) search users according to job positions in the organizational structure.
Contractors search example:
CreationAuthor in (FROM User SELECT Id WHERE OrganizationItems in UserOrganisationItems(1)) – search all the contractors, created by the user on a specific job position (with ID=1).
7. Parameters (You cannot use parameters in the web applications)
Parameters let you insert values to your query at runtime without editing the base query. The values are defined in scripts/processes. While running the query, the parameter is replaced by a real value. This allows you to use the same query over and over without having to constantly edit it by adding specific datasets.
Use the @ character to add a parameter to your EQL query. The parameter name can be any string containing Latin or Russian characters, numbers, as well as the sign "_". The parameter name must begin with a letter.
I.e.: @ParameterName
To use a parameter:
- Create EQL query with a parameter and functions or logical operators.
- Match the parameter and the value that it should take. Values can be defined through variables, context variables, constant values, etc.
Example:
filter.Query = "Contact in (@Conacts)"; - Parameterized EQL-query. Serch of contacts that are defined through the @Conacts parametr.
filter.QueryParameters.Set("@Conacts", context.Kontakty); - link to a variable whose value will be use an input for the @Conacts parameter.
Example of using PublicAPI in EQL:
1. Create a filter-builder and pass the required EQL query to Query.
Contractors search example:
PublicAPI.CRM.Contractor.Filter().Query("Id in (1, 2)").Find(); - search contractors with ID=1 or 2.
2. Pass EQL to the filter.
Contractors search example:
PublicAPI.CRM.Contractor.Find("Id in (1, 2)"); - search contractors with ID=1 or 2.