In an advanced data grid, advanced filtering allows you to specify complex filtering conditions based on any combination of criteria, to restrict the number of items displayed.
In some cases filter conditions cannot be displayed with the help of the Custom Filter dialog. For instance, the Custom Filter dialog supports only one or two filter conditions to set for a single column. The dialog does not support some filter operators either (BETWEEN, membership test operator and date/time related operators). So if the filter criteria contains more than two conditions bound to a single column or if non-supportable operators are used, the Filter Building dialog is activated instead when selecting the (Custom) option from the filter drop down list.
The filter criteria specified by the above dialog is interpreted as:
(ID=1 OR ID=4 OR ID=7) AND (CustomerID>=1 AND CustomerID<=100)
The Filter Building dialog extends the functionality of the Custom Filter dialog. Unlike the Custom Filter, the Filter Building dialog allows you to specify the filter criteria for any column in the view. You can also invoke the dialog by clicking the Customize button in the filter panel.
The Filter Building dialog supports all possible operators. In addition to those described in the table above, you can create filter conditions using the BETWEEN, membership test operators and date/time related operators:
Operator |
Description |
BETWEEN |
The operator allows you to select records whose column values are included between two operands. To specify the range, use the editors which appear when clicking the operand boxes.
The operator displayed in the picture above is equivalent to the following statement: ID>=1 AND ID<=100 |
NOT BETWEEN |
The NOT BETWEEN operator selects records whose column values are not included in the required range. Operands are specified in the same manner as for the BETWEEN operator.
The equivalent of the operator displayed in the image is: ID<1 AND ID>100 |
IN |
Implements a membership test operator. The IN operator selects records with column values equal to operands from the specified set. To add an operand to the set, click the '+' button.
The equivalent to the IN operator shown in the image above is: ID=1 OR ID=4 OR ID=7 |
NOT IN |
The negation of the IN operator. It selects a record if its column value is not a member of a specified set.
The equivalent to the IN operator shown in the image above is:ID<>1 AND ID<>4 AND ID<>7 |
The Filter Building dialog also supports two more Boolean operators to combine conditions in the list: NOT AND and NOT OR. NOT AND combines conditions by the AND operator and then negates the result:
These criteria select all records except those with the Car column value set to 'BMW 530i' and PaymentType set to Cash. The equivalent text representation is: NOT (Car='BMW 530i' AND PaymentType=Cash).
NOT OR combines conditions by the OR operator and then negates the result:
The criteria select records with PaymentType set neither to Master nor to Visa. The text representation is:
NOT (PaymentType=Master OR PaymentType=Visa)
Note that the currently applied filter will be displayed at the bottom of the Advanced Data Grid, as shown below.
Use the 
button to remove the displayed filter permanently. Alternatively, you can use the 
checkbox to disable the filter temporarily. To modify the displayed filter, use the Customize... button at the right of the filter zone.
Topic 178055, last updated on 18-Apr-2020
