Filtering

When a grid contains thousands of records, it may be hard to find the needed ones using scrolling or pager navigation. For those times, the dxDataGrid widget offers its filtering capabilities.

An end-user performs filtering using a filter row. To filter records by a column, the end-user must type a value or a part of it (depends on the data type) in a corresponding filter row cell. If any records are found, then the grid will contain only them. Otherwise, it displays the "No Data" message.

NOTE: Filtering is performed ignoring the letter case.

Usually, a filter row cell is a text box. But in some cases, a filter row cell is represented by a control more suitable for filtering in the particular column. For example, when the dataType option of a column is 'date', its filter row cell contains a calendar allowing a user to choose a date. Another example is a select box that appears in a filter row cell if the column's dataType is 'boolean', or if a lookup column is used.

Different filter operations may be applied to a column. To change the current filter operation, the user must choose a required item from a filtering menu. This menu is invoked when the user hovers the mouse pointer over the magnifying glass icon of a filter row cell. Operations available using this menu depend on the data type of a column and vary from type to type, as well as the default filter operation. After a filter operation is applied, the default operation can be restored by choosing the "Reset" item in the filtering menu. The following table represents filter operations by data types.

dataType Available filter operations Default filter operation
'string' 'contains' | 'notcontains' | 'startswith' | 'endswith' | '=' | '<>' 'contains'
'number' '=' | '<>' | '<' | '>' | '<=' | '>=' '='
'date' '=' | '<>' | '<' | '>' | '<=' | '>=' '='

To try filtering in action, see the example below. To discover more about dxDataGrid filtering capabilities and how to configure them, see the following subtopics.

Show Example:
jQuery
<div id="gridContainer" style="height:400px; max-width:800px; margin: 0 auto"></div>
$(function () {
    $("#gridContainer").dxDataGrid({
        dataSource: ordersNew,
        columns: [{
            dataField: "OrderNumber",
            width: 130,
            caption: "Invoice Number"
        }, {
            dataField: 'OrderDate',
            dataType: 'date'
        }, {
            dataField: "SaleAmount",
            format: "currency"
        }, {
            dataField: 'CustomerStoreCity',
            caption: "City"  
        }, {
            dataField: 'CustomerStoreState',
            caption: "State"
        }],
        pager: { visible: false },
        scrolling: { mode: 'virtual' },
        filterRow: { visible: true }
    });
});

Filtering in UI

Filtering in a UI is considered enabled when the filter row is made visible by setting the filterRow | visible option to true.

See Also

For information on how to configure the filter row, refer to the Filter Row topic.

If the filter row is visible, filtering is enabled for all grid columns. However, you can disable it for individual columns by setting their allowFiltering option to false.

JavaScript
$("#gridContainer").dxDataGrid({
    // ...
    filterRow: { visible: true },
    columns: [{
        allowFiltering: false,
        // ...
    }, //...
    ]
});

The set of filter operations appearing in the filtering menu may be corrected. For this purpose, assign an array of required operations to the filterOperations option of a column. To apply one of these operations initially, specify the selectedFilterOperation option of the same column.

JavaScript
$("#gridContainer").dxDataGrid({
    // ...
    columns: [{
        filterOperations: [ 'contains', 'startswith', '=' ],
        selectedFilterOperation: 'contains',
        // ...
    }, //...
    ]
});

There may be cases when a user must be able to apply a filter, but not to change the filter operation. In these cases, assign an empty array to the filterOperations option and specify the current filter operation using the selectedFilterOperation option.

JavaScript
$("#gridContainer").dxDataGrid({
    // ...
    columns: [{
        filterOperations: [],
        selectedFilterOperation: 'contains',
        // ...
    }, //...
    ]
});

Filtering may operate incorrectly in a column with calculated values. To restore this functionality, implement the calculateFilterExpression callback function. This function must return an array of the following items.

  1. The data source field or a function providing actual values for a grid column. Commonly, the calculateCellValue function is passed here.
  2. The comparison operator. The following operators are available: "=", "<>", ">", ">=", "<", "<=", "startswith", "endswith", "contains", "notcontains".
  3. The value typed into a filter row cell. The actual values will be compared to this value.

The following code snippet demonstrates a common example of implementing the calculateCellValue and calculateFilterExpression functions.

JavaScript
$("#gridContainer").dxDataGrid({
    // ...
    columns: [{
        calculateCellValue: function (rowData) {
            return rowData.Amount * rowData.Cost;
        },
        calculateFilterExpression: function (filterValue, selectedFilterOperation) {
            return [this.calculateCellValue, selectedFilterOperation || '=', filterValue];
        },
        // ...
    }, //...
    ]
});

Filtering in Code

To apply a filter in code, call the filter(filterExpr) method. Pass an array with the following members to this method.

  1. The data source field by which data items are filtered.
  2. The comparison operator. The following operators are available: "=", "<>", ">", ">=", "<", "<=", "startswith", "endswith", "contains", "notcontains".
  3. The value with which data source field values should be compared.

For example, the following call leaves only those records that have a value of more than 20 in the 'Amount' field.

JavaScript
dataGridInstance.filter(['Amount', '>', 20]);

To clear a programmatically applied filter, call the clearFilter() method.

JavaScript
dataGridInstance.clearFilter();

A user-specified filter can also be cleared from code. For this purpose, assign undefined to the filterValue option of the required column using the columnOption method.

JavaScript
dataGridInstance.columnOption('columnId', 'filterValue', undefined);

View Demo

Setting Initial Filtering

If a filter must be applied to a column initially, set the filterValue option of this column. This value is applied using a filter operation specified by the selectedFilterOperation option. Note that the allowFiltering option of this column must be true. The following code leaves only those records, whose value in the "Data" column equals 10.

JavaScript
$("#gridContainer").dxDataGrid({
    // ...
    columns: [{
        dataField: 'Data',
        selectedFilterOperation: '=',
        filterValue: 10
        // ...
    }, //...
    ]
});

To make initially set filtering impossible for a user to change, hide the filter row by setting the filterRow | visible option to false.

Searching

Filtering can be performed in one or several columns at a time. In addition, dxDataGrid provides you with the capability to filter records by all columns at once. In dxDataGrid, searching is performed using a search panel. To search through records, a user must type in text in the search panel. Records that match this text are displayed in a grid.

See Also

To find information on configurable features of the search panel, refer to the Search Panel topic.

Searching is conducted differently in columns with different data types. To find a record by a numeric, boolean or date value, a user must enter the full value into the search panel. To find a record by a string value, entering a part of this value is sufficient.

You can also specify an initial search string using the searchPanel | text option.

JavaScript
$("#gridContainer").dxDataGrid({
    // ...
    searchPanel: {
        text: 'Search string'
    }
});

Although by default searching is performed in all columns, you can disable it in certain columns. Since searching is filtering, both these operations are controlled by the same allowFiltering option.

Searching can also be conducted in code using the searchByText(text) method. To clear search settings, call the same method with an empty string as the argument.

To try searching in action, open the example below and type in text in the search panel.

Show Example:
jQuery
<div id="gridContainer" style="height:400px; max-width:800px; margin: 0 auto"></div>
$(function () {
    $("#gridContainer").dxDataGrid({
        dataSource: orders,
        columns: [
            { dataField: 'OrderID', width: 100 },
            'CustomerID',
            { dataField: 'OrderDate', dataType: 'date' },
            { dataField: 'ShippedDate', dataType: 'date' },
            'ShipName',
            { dataField: 'ShipCountry', caption: 'Ship To' }
        ],
        pager: { visible: false },
        scrolling: { mode: 'virtual' },
        searchPanel: { visible: true }
    });
});