Filtering Data
- 6 minutes to read
This topic covers filtering the data displayed in a grid View (using the data controller’s functionality). Do not confuse our filtering with the standard dataset functionality provided by the TDataSet.Filter property. To learn more about this type of filtering, refer to the corresponding Delphi help section.
An end-user has access to quick and simple filtering at runtime and this is discussed in the Runtime Filtering topic. This includes a reference to the Custom Filter dialog and Filter Builder dialog which show how to combine at most two simple filter conditions. To construct more complex filter criteria, it needs to be done in code and that is what will be covered here.
Data filtering functionality is provided by the TcxFilterCriteria class. You will use its descendants such as TcxDataFilterCriteria and TcxDBDataFilterCriteria. These extend the base class’s functionality for unbound and bound modes, respectively. TcxFilterCriteria and its descendants, provide filter conditions which relate to all the columns in a View.
Let us consider the FilterByCodeDemo which shows you how to implement data filtering by code. This demo displays data from the Customers table of the CustomersDB database.
Each record in the Customers table stores information on a customer and the product they have purchased. This product is specified by the PRODUCTID field, which represents the numeric product identifier.
Suppose we want to know the customers who only bought grid components. In this case, you have to filter data by the Product column bound to the PRODUCTID field. The filter criteria can be constructed as follows:
(Product = ExpressQuantumGridProductID) OR
(Product = XpressQuantumGridProductID) OR
(Product = XtraGridProductID) OR
(Product = ASPXGridProductID)
This filter cannot be set by end-users via the Custom Filter dialog because it consists of four simple filter conditions for a single column, which exceeds the limit of two conditions.
So, applying such filter criteria can be performed via the Filter Builder dialog and via code. Consider how this can be done via code. The View‘s DataController.Filter.Root property manages all the filter conditions applied to the current View. It allows you to construct complex nested filter criteria by combining filter conditions with logical operators (AND, OR). The Root property represents a list (TcxFilterCriteriaItemList) of filter conditions (simple and/or aggregate).
A simple filter condition (TcxFilterCriteriaItem) addresses:
the column for which the condition is applied;
the comparison operator represented by the TcxFilterOperatorKind enumerator;
the comparison value;
the display value.
An aggregate filter condition is a list (similar to Root) of simple and/or aggregate conditions combined by a Boolean operator. It represents an instance of the TcxFilterCriteriaItemList class.
For the filter criteria listed above, only simple filter conditions are used. They are combined by the OR operator and added to the Root list.
const
cExpressQuantumGridID = 3; //ProductID of ExpressQuantumGrid
cXtraGridID = 7; //ProductID of XtraGrid
cASPXGridID = 9; //ProductID of ASPXGrid
cXpressQuantumGridID = 14; //ProductID of XpressQuantumGrid
procedure TFilterByCodeMainForm.SetOnlyGridsFilter;
begin
with tvCustomers.DataController.Filter.Root do
begin
//clear all existing filter conditions
Clear;
//set the logical operator
//by which new conditions are combined
BoolOperatorKind := fboOr;
//add new filter conditions
AddItem(tvCustomersPRODUCTID, foEqual, cExpressQuantumGridID, 'ExpressQuantumGrid');
AddItem(tvCustomersPRODUCTID, foEqual, cXpressQuantumGridID, 'XpressQuantumGrid');
AddItem(tvCustomersPRODUCTID, foEqual, cXtraGridID, 'XtraGrid');
AddItem(tvCustomersPRODUCTID, foEqual, cASPXGridID, 'ASPX Grid');
end;
end;
On executing this code, a grid control will display data that meets the filter criteria set.
You can enable an end-user to run this code, for instance, by adding a button onto a form and handling its OnClick event in which you call the SetOnlyGridsFilter procedure. The ExpressQuantumGrid provides another way of applying such custom-defined filters. It allows you to customize the filter dropdown list activated when a user clicks the filter arrow in a column header. You can customize this list by deleting individual elements and/or by adding new ones. Suppose we want to add an ‘ONLY GRIDS’ item to this list. Selecting this item will call the SetOnlyGridsFilter procedure thus displaying customers who bought only grid components.
To accomplish this, handle the following column events:
The OnGetFilterValues event, in order to add a new item;
The OnUserFiltering and OnUserFilteringEx events in order to handle item selections.
The following code shows an OnGetFilterValues event handler in which the ‘ONLY GRIDS’ item is added to the filter list. The filter list is provided by the AValueList parameter. To add a new list item, call the AvalueList.Add method. The first parameter set to fviUser indicates that this item will generate the OnUserFiltering event when selected.
The second parameter specifies the value associated with the new item. If you add several elements of the fviUser kind, you will need to know which item has been selected when implementing the OnUserFiltering event. Use the value associated with an item for this purpose (see code below).
The third parameter represents a string displayed in the filter list. The last parameter affects the position within the filter list in which the new item is inserted. See the Add method description for details.
const
cOnlyGrids = 100;
procedure TFilterByCodeMainForm.tvCustomersPRODUCTIDGetFilterValues(
Sender: TcxCustomGridTableItem; AValueList: TcxDataFilterValueList);
begin
AValueList.Add(fviUser, cOnlyGrids, 'ONLY GRIDS', True);
end;
The OnUserFiltering event implementation is quite simple. To ascertain that the ‘ONLY GRIDS’ item was selected, the AValue parameter is compared to the cOnlyGrids constant. If True, the SetOnlyGridsFilter procedure is called which applies the filter criteria discussed.
procedure TFilterByCodeMainForm.tvCustomersPRODUCTIDUserFiltering(
Sender: TcxGridColumn; const AValue: Variant;
const ADisplayText: String);
begin
if AValue = cOnlyGrids then
SetOnlyGridsFilter;
end;
In addition to the fviUser filter items, you can use fviUserEx filter items and handle the OnUserFilteringEx event to apply appropriate filter criteria in response to selecting these items in filter dropdowns.
Note
Unlike the fviUser items, the fviUserEx items can be combined – more than one filter criteria that corresponds to a fviUserEx item can be simultaneously applied to the View. That’s why a filter dropdown displays check boxes for these items in multi-selection mode. For more information, refer to the OnUserFilteringEx event description.