Understanding the Filter Builder

Updated in 8.4

The Filter Builder is a tool for creating and editing the advanced filters. It provides easy access to the field names and operators helping you to build expressions for the filter criteria. The Filter Builder is available when you are customizing your grid and configuring the Advanced Filter or a highlighting rule in the Design mode - on the Advanced Filter tab of the Customize View window and in the Conditional Formatting Rule window.

The basic elements forming a filter criteria are as follows:

  • Condition

    A condition establishes the rules to test the value of a single field of a record. Each condition consists of the field name to be compared, a comparison operator and, if the operator requires so, field value(s) to compare with. The Filter Builder shows conditions in separate rows. If the comparison operator is either like or not like, in the value you can use the wildcards: "_" representing any single character or "%" representing any sequence of characters.

  • Condition group

If you want to use more than one condition, you can combine several conditions into a condition group. For each condition group, a conjunctive logical operator (all matched, any matched, one failed, or all failed) is set. Any group can include both conditions and subgroups. Conditions in a subgroup are processed the same way as math expressions with parentheses. The number of group nesting levels is unlimited. Each group is shown in a separate row, which displays the logical operator of the group.

    Each criteria has the root group that is shown in the upper row. By default, the root group has the all matched logical operator, you can change it if you want.

ClosedClick here to show an explanation for the advanced filter example.

The filter criteria shown in the picture above consists of three conditions marked as "Condition A", "Condition B", and "Condition C".

Condition A tests the likeness of the Requester field and the value %Derek%, according to the rules established by the like operator.

Condition B tests the equality of the Category field and Network by using the equals operator.

Condition C is designed to check if the Assignee field is blank; the is blank operator requires no arguments, therefore there is no value to compare with.

There are two groups: the root group and the group marked as "Group 1". The root group includes Condition A and Group 1, its logical operator is any matched. The Group 1 consists of Condition B and Condition C, which are combined under all matched. The whole expression can be written as follows:

(Requester like (%Derek%) any matched ((Category = 'Network') all matched ((Assignee IS BLANK))

For details about handling advanced filters in the Filter Builder, see Designing an Advanced Filter with the Filter Builder. For details on using the Filter Builder for highlighting, see Highlighting records.

For more information about using the logical and comparison operators, see Using operators in the Filter Builder.

TIP: If the Filter Builder functionality is not enough for your advanced filter, you can switch to the SQL mode and directly specify the SQL WHERE clause. However, the SQL mode is recommended for advanced SQL users only. For details, see Designing an Advanced Filter by specifying an SQL WHERE clause.