Filter and Search
- 9 minutes to read
When filtering is applied to a View, displayed records are restricted to those that meet the current filter criteria. You can filter data against single or multiple columns. |
Filtering Dropdown Menus (Excel Style)
To invoke a filtering dropdown menu for a column, click the filter icon within the column header. In the “Values” tab, end-users can select specific cell values from those that are currently displayed by the Data Grid.
The “Filters” tab gives users a wider pool of filtering options. For example, when filtering by dates, you can only show those records that correspond to the previous week.
The content of a filtering dropdown menu depends on the type of data displayed by the related grid column. For instance, the figure below illustrates what this menu looks like when filtering by a numeric column.
This type of filtering dropdown menus is called Excel-style dropdowns and is the default filtering menu type starting with version 17.1.
Note
Related API * GridOptionsCustomization.AllowFilter - disables filtering for the entire Data Grid.
- OptionsColumnFilter.AllowFilter - disables filtering for this specific column.
- ColumnViewOptionsFilter.ColumnFilterPopupMaxRecordsCount - allows you to limit the number of Data Grid records that are scanned to populate filter dropdowns with unique filter values.
- ColumnViewOptionsFilter.ColumnFilterPopupMode - if a version of your DevExpress installation is older than 17.1, switch this setting to Excel to enable Excel-style filtering dropdowns.
- ColumnView.ShowFilterPopupExcel - handle this event to customize Excel-styled filters.
See the Filtering in Code article to learn how to customize Excel-style menus.
Classic Filtering Dropdown Menus
Instead of Excel-style dropdowns, the Data Grid can display classic filtering menus. These are calendars for the DateTime columns and regular dropdowns for columns of other types.
Note
Related API * ColumnViewOptionsFilter.ColumnFilterPopupMode - set this property to Classic in order to enable classic filtering dropdowns for DevExpress installations of versions 17.1 and newer. In older versions, classic menus are enabled by default.
- WindowsFormsSettings.DefaultSettingsCompatibilityMode - setting this property to v16 is the alternative way to enable classic filtering dropdowns for versions 17.1 and newer.
- ColumnViewOptionsFilter.ColumnFilterPopupRowCount - gets or sets the maximum number of items that regular dropdowns can display simultaneously.
- ColumnViewOptionsFilter.AllowColumnMRUFilterList - if this property is enabled, regular dropdown lists will remember values, most recently selected by end-users. These values will be shown before the default “Custom” item.
- OptionsColumnFilter.ShowBlanksFilterItems - gets or sets whether or not the “(Blanks)” and “(Non Blanks)” items are available within filtering dropdowns.
You can opt for checked list menus instead of regular dropdown menus.
Note
Related API * OptionsColumnFilter.FilterPopupMode - set to CheckedList to enable this filtering menu style.
- ColumnViewOptionsFilter.ShowAllTableValuesInCheckedFilterPopup - gets or sets whether or not checked filter dropdown lists must display unique values from all data source records, or only from those records that meet the current filter criteria.
- ColumnView.ShowFilterPopupCheckedListBox - this event allows you to modify items within the checked list filtering menu, including the standard “Select All” item.
Calendar filtering menus can also be replaced with checked lists. Additionally, you can select between three types of calendar-based menus: DateSmart (default menu type), DateAlt and Date. The figure below illustrates the DateAlt menu.
All three menus feature the same calendar for selecting a specific date, but with different sets of check boxes to select non-intersecting date intervals. The table below lists some of these intervals.
DateSmart | Date | DateAlt |
---|---|---|
Check boxes are visible only if there are Data Grid records that fall into corresponding intervals.
| Same time intervals as for the DateSmart type. All check boxes are available, even if there is no data that falls into a corresponding date range. |
|
Note
Related API * OptionsColumnFilter.FilterPopupMode - set this property to either Date, DateSmart or DateAlt to choose the required calendar-based dropdown menu.
- OptionsColumnFilter.ImmediateUpdatePopupDateFilterOnCheck - if this setting is enabled, toggling calendar-based menu check boxes immediately applies filtering.
- OptionsColumnFilter.ImmediateUpdatePopupDateFilterOnDateChange - if this setting is enabled, selecting a date (or a date range) within a calendar-based menu immediately applies filtering.
- OptionsColumnFilter.ShowEmptyDateFilter - gets or sets whether or not dropdown menus should display the “Show Empty” filter. End-users can check this filter to view records that have no values in the related DateTime column.
Automatic Filtering Row
Another way to filter data at runtime is to utilize the automatic filtering row. This row is displayed above regular data rows and allows end-users to type in filtering values (including ‘*’ and ‘%’ wildcards) and select filter operators (NotLike, Equals, Between, etc.).
Note
Related API * GridOptionsView.ShowAutoFilterRow - manages the visibility of the automatic filtering row.
- GridOptionsFilter.AllowAutoFilterConditionChange - specifies whether or not the auto-filtering row’s condition selector is visible. If this selector is disabled, entered values will be compared using the “Equals” (for CheckEdit, LookUpEdit and ImageComboBoxEdit columns) or “Like” (for other columns) operator.
- OptionsColumnFilter.AutoFilterCondition - specifies the filter criteria operator for this column (“Equals”, “Like”, “Greater”, etc.).
- OptionsColumnFilter.ImmediateUpdateAutoFilter - if this setting is enabled, the Data Grid will filter its records immediately as end-users modify the filter condition. Otherwise, filtering will not be applied until a user presses the Enter key or moves focus to another cell.
- GridView.ShowAutoFilterConditionsMenu - call this method from code to invoke the automatic filtering row popup menu for the specific column.
- GridView.GetAutoFilterValue, GridView.SetAutoFilterValue - methods that allow you to retrieve the currently applied auto-filtering row condition and apply a new one from code.
Filter Panel
When you apply filtering, a View shows a filter panel. End-users can utilize this panel to modify and remove current filters.
Note
Related API * ColumnViewOptionsView.ShowFilterPanelMode - manages the filter panel visibility.
- ColumnViewOptionsFilter.AllowMRUFilterList - allows you to hide the button that invokes the list with recently applied filters.
- ColumnViewOptionsFilter.AllowFilterEditor - controls the “Edit Filter” button visibility.
Incremental Search
End-users can focus a grid column and start typing. The Data Grid will focus and highlight the first record matching record. To find other records, press Ctrl+Up and Ctrl+Down hotkeys.
Note
Related API * GridOptionsBehavior.AllowIncrementalSearch - enables or disables this feature.
- ColumnView.GetIncrementalText - returns the text being searched during an incremental search.
- GridView.StartIncrementalSearch, GridView.StopIncrementalSearch - call these methods from code to manually trigger and cancel incremental search.
Filter Editor
The Filter Editor dialog allows end-users to build complex filtering criteria, combined from multiple expressions. To invoke this dialog end-users can do one of the following:
- click “Edit Filter” in Filter Panel;
- select “(Custom)” in a regular dropdown filter menus
- right-click a column header and select “Filter Editor”.
Note
Related API * ColumnViewOptionsFilter.AllowFilterEditor - gets or sets whether or not the Filter Editor is available.
- ColumnView.CustomFilterDialog - handle this event to alter the default behavior for end-users clicking the “Custom” filter drop-down menu item. See Filtering in Code to learn more.
- ColumnViewOptionsFilter.DefaultFilterEditorView - allows you to switch between textual and visual Filter Editor modes.
ColumnView.FilterEditorCreated - this event fires when the Filter Editor is about to be shown. Allows you to customize the Editor dialog or prevent it from being displayed.
The code below allows end-users to filter the “Price” column’s data by using the SpinEdit and CalcEdit editors.
private void GridView1_FilterEditorCreated(object sender, DevExpress.XtraGrid.Views.Base.FilterControlEventArgs e) { e.FilterControl.BeforeShowValueEditor += FilterControl_BeforeShowValueEditor; } private void FilterControl_BeforeShowValueEditor(object sender, DevExpress.XtraEditors.Filtering.ShowValueEditorEventArgs e) { if (e.CurrentNode.FirstOperand.PropertyName != "Price") return; RepositoryItemTextEdit item = null; if (e.FocusedElementIndex == 2) item = new RepositoryItemSpinEdit(); else item = new RepositoryItemCalcEdit(); item.Mask.MaskType = DevExpress.XtraEditors.Mask.MaskType.Numeric; item.Mask.EditMask = "c"; e.CustomRepositoryItem = item; }
- ColumnViewOptionsFilter.UseNewCustomFilterDialog - enable this property to allow end-users to filter values of a GridColumn not only against a constant custom value, but also against values of other GridColumns.
Find Panel
The Find Panel allows your end-users to apply a temporary filter that filters data across all Data Grid columns at once. To invoke this panel, press “Ctrl+F” at runtime. Type in text you need to search for, then press Enter or click “Find”.
The Find Panel provides the search syntax that allows end-users to apply complex find criteria. Examples in the following table illustrate this syntax.
Search Criteria | Description |
---|---|
register | Selects records that contain the “register” string in any search column. |
check register Dave | Selects records that contain the “check” or “register” or “Dave” strings in any search column. |
“check register” | Selects records that contain “check register” in any search column. |
screen +”Richard Fisher” | Selects records that contain “screen” and “Richard Fisher” in search columns. |
Product:Tofu Seattle | Selects records that contain “Tofu” in the column that starts with “Product”, and also contain “Seattle” in any search column. |
data +entry -mark | Selects records that contain “data” and “entry” in search columns, excluding records that contain “mark”. |
menu mask -file | Selects records that contain “menu” or “mask”, excluding records that contain “file”. |
From:Roller Subj:”currency mask” | Selects records that contain “Roller” in the column that starts with “From”, and also contain “currency mask” in the column that starts with “Subj”. |
import -From:Steve | Selects records that contain “import” in any search column, excluding records that contain “Steve” in the column that starts with “From”. |
Note
Related API * ColumnViewOptionsFind.AllowFindPanel - manages the availability of the Find Panel.
- ColumnViewOptionsFind.AlwaysVisible - allows you to make the Find Panel permanently visible.
- ColumnView.FindPanelVisible, ColumnView.ShowFindPanel, ColumnView.HideFindPanel - allow you to manually invoke and hide the Filter Panel.
- ColumnViewOptionsFind.FindMode - if this property equals Always, the Find Panel will start looking for the entered text automatically after a short delay.
- ColumnViewOptionsFind.HighlightFindResults - gets or sets whether or not found text blocks should be highlighted.