Bitrix24 Self-hosted administration

  Complex filter logic

Most system forms display information as report tables. System provides a filter feature to define the subset of data to be displayed in a format of report table. With filters, a user can provide numeric, textual and other kind of information used as the criteria to match against data selected from the database. If the data selected satisfies the provided criteria, it is displayed in the report. Filter is the part of the page body; it is mostly positioned on top of the form.

How to use filter queries

You can enter queries inside specific filtering fields (mainly text or character fields).

A query is a logic expression. For example, you want to filter a table with user data by field Name. If you type in the following text:

John & Smith

the output will contain all users having both words in their names, either partially or in full. This query is equivalent to typing these two words separated with a space: John Smith.

If you need to display all users having either John or Smith in their names, you would have to type the following query:

Kennedy | Smith

In case you need to use the negation, a special logical operator is provided: "~". For example: 

(Kennedy | Smith) & ~Kennedy.

This query means to: display all users having either John or Smith in their names, but not Kennedy.

The last query made use of round brackets. The use of brackets is stipulated by the priority of operator "|" which is lower than that of operator "&" (see table Logic Operators Priority). On the other hand, if you do not use round brackets here, this query would mean to display all users John | Smith & ~Kennedy having Smith in their names but not Kennedy, or all users having John in their names.

Round brackets can be used to build more complex queries.

In addition to the above described operators, the system provides wildcard operators. For example, you cannot remember the name of a person but sure that the last name starts from "Wil" and ends with "son", while the first name is either "Blythe" or "Blithe". The following query will match these conditions: Wil%son & Bl_the. In this query, symbol: "%" (percent sign), means "zero or more symbols, and symbol "_" (underscore), means "any one symbol".

If you need to include any reserved symbol in the query, enclose the query phrase in single or double quotation marks. For example: "~NICK~" | '&Leon&'. Similarly, if you need to find a phrase containing space(s), enclose it in single or double quotation marks, for example: "Microsoft Internet Explorer" | 'Netscape Navigator'.

In all cases, the search is case insensitive.

Query operators

1. "&" - Logical operator "and"  can be omitted. A query John & Smith is equivalent to John Smith.
2. "|" - Logical operator "or" allow searching for records containing at least one of operands.
3. "~" - Logical operator "not" searches for records not containing an operand stated after the operator. Additionally, it negates expression after the operator.
4. "( )"- Round brackets define the logical operators precedence.
5. "%" - Wildcard operator. Means zero or more symbols".
6. "_" - Wildcard operator. Means any "1 one symbol".

Logical operators priority

1. "~" - 1 priority
2. "&" - 2 priority
3. "|" - 3 priority