|
  
|
||
|
|
||
This topic describes how to filter data in the grid controls.
1. Filter Drop down List
2. Custom Filter Dialog
3. Filter Builder Dialog
1. Filter Drop down List
With filter drop downs, you can filter records in tabular and Card Views by the values of grid items (column or card rows). To invoke a filter drop down for a grid item, click its filter drop down button. When a grid item is used to create a filter, its filter drop down button is displayed in blue.
The filter drop down is populated with various filter conditions which are determined by the grid item's unique values. In large datasets, this operation may consume a lot of time since all records are accessed.
The (All) and (Custom…) items are always added to the list. Selecting the (All) item removes all the filter conditions set for the current column. The (Custom…) item invokes the Custom Filter or Filter Builder dialog which allow you to visually construct the filter criteria for the given column or current View, respectively.
(Blanks) and (Non Blanks) elements are added automatically if your column contains NULL and non-NULL values, respectively.
When a user selects a value from the list (any item other than All, Custom, Blanks or Non Blanks), a new filter condition is created and applied to the data in the current View.
This new condition is of the type:
<Column> = <Value>
where <Column> stands for the current column for which the filter list was activated and <Value> defines the selected value. For instance, when selecting the 'A F Electrical Limited' item, the "Company Name LIKE A F Electrical Limited" filter condition is created and applied:
2. Custom Filter Dialog
Selecting the (Custom) item from the filter drop down list activates the Custom Filter dialog.
This dialog allows you to specify the filter criteria for a single column consisting of one or two filter conditions. You can also specify the operator for these conditions.
The following image shows all the possible operators:
Operator |
Text representation |
= |
equals |
<> |
does not equal |
< |
is less than |
<= |
is less than or equal to |
> |
is greater than |
>= |
is greater than or equal to |
LIKE |
like |
NOT LIKE |
not like |
= NULL |
is blank |
<> NULL |
is not blank |
In addition to the operator, values must be specified via the value box. This uses the same editor type as the column (stand-alone and in-place use the same editor classes).
When using the LIKE or NOT LIKE operator, you can use the 'underscore'(_) and 'percent' (%) characters to substitute one or several symbols in a string value respectively.
The second filter condition is specified in the same manner. To combine two conditions, use the AND/OR check box.
3. Filter Builder Dialog
In some cases, however, 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 for a single column. The dialog also does not support some filter operators (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 Builder dialog is activated instead when selecting the (Custom) option from the filter drop down list.
The Filter Builder dialog supports all possible operators. In addition to those described in the above table, 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
Date/time operators: is yesterday is today is tomorrow is last 7 days is last week is last 14 days is last two weeks is last 30 days is last month is last year is past is this week is this month is this year is next 7 days is next week is next 14 days is next two weeks is next 30 days is next month is next year is future
This group of operators is available only for date/time fields. They do not require any operands. For instance, the is today operator selects records with column values equal to the current day. The is this year operator selects records equal to the current year, etc. String representations of these operators are determined by the filter options accessible via a View's DateTimeHandling.Filters property.
The Filter Builder 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 selects records which don't have their PaymentType set to Master or Visa. The text representation is:
NOT (PaymentType=Master OR PaymentType=Visa)
