Filter and Search

• Aug 09, 2019
• 10 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) Classic Filtering Dropdown Menus Automatic Filtering Row Filter Panel Incremental Search Filter Editor Filter Criteria Display Style Find Panel Advanced Filter and Search Concepts

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.
• OptionsColumnFilter - provides other settings that allow you to affect Excel-styled filters.
• ColumnView.ColumnFilterChanged - Occurs when a column’s filter condition changes.

See the Advanced Filter and Search Concepts article to learn how to dynamically 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.
• ColumnView.ColumnFilterChanged - Occurs when a column’s filter condition changes.

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. Beyond this year - dates that follow the current year. Later this year - dates of the current year starting with the following month. Later this month - dates of the current month that follow next week. Next week - dates that belong to the following week. Later this week - dates of the current week starting with the day after tomorrow. Earlier this week - dates of the current week that are prior to yesterday. Last week - dates of the previous week. Earlier this year - dates of the current year that are prior to the current month. Prior to this year - dates that are prior to the current year.	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.	Beyond - dates that belong to the month in three months time and beyond. Earlier - dates that belong to the month seven months ago and earlier.

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

Video Tutorial (Online)

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.
• OptionsColumnFilter.AllowAutoFilter - allows you to exclude specific columns from the automatic filtering row without hiding this entire row.
• GridView.PopupMenuShowing - allows you to hide or remove specific conditions.
• 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 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”.

You can modify the ColumnViewOptionsFilter.DefaultFilterEditorView property to enable the Filter Editor’s “Text” panel\tab, where users can type filter expressions manually.

Starting with version 18.1, the legacy Filter Editor is replaced with its updated version that features the advanced “Text” panel. This panel now supports syntax highlighting and auto-complete to facilitate the input. To revert data-aware controls back to the legacy Filter Editor version, disable the static WindowsFormsSettings.UseAdvancedFilterEditorControl property.

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 Advanced Filter and Search Concepts to learn more.

• 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.

  Show example

  The code below allows end-users to filter the “Price” column’s data by using the SpinEdit and CalcEdit editors.

  

  C#

  
  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;
  }
  

  VB.NET

  
  Private Sub GridView1_FilterEditorCreated(ByVal sender As Object, ByVal e As DevExpress.XtraGrid.Views.Base.FilterControlEventArgs)
    AddHandler e.FilterControl.BeforeShowValueEditor, AddressOf FilterControl_BeforeShowValueEditor
  End Sub
  
  Private Sub FilterControl_BeforeShowValueEditor(ByVal sender As Object, ByVal e As DevExpress.XtraEditors.Filtering.ShowValueEditorEventArgs)
    If e.CurrentNode.FirstOperand.PropertyName <> "Price" Then
        Return
    End If
    Dim item As RepositoryItemTextEdit = Nothing
    If e.FocusedElementIndex = 2 Then
        item = New RepositoryItemSpinEdit()
    Else
        item = New RepositoryItemCalcEdit()
    End If
    item.Mask.MaskType = DevExpress.XtraEditors.Mask.MaskType.Numeric
    item.Mask.EditMask = "c"
    e.CustomRepositoryItem = item
  End Sub
  

• 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.

Filter Criteria Display Style

The following list demonstrates the available display styles of filter criteria in controls.

• Visual -

  Renders filter criteria in an easy-to-read format and uses skin-based colored highlights to differentiate between column names, functions and values. In the Filter Panel, the ‘x’ button is displayed when you hover over a condition. This button allows users to quickly remove individual conditions from the filter.

  

• Text -

  Renders filter criteria in a text-based format.

  

Related API:

• WindowsFormsSettings.FilterCriteriaDisplayStyle - Gets or sets the default display style of filter conditions in a control’s Filter Panel and built-in Filter Editor.
• ColumnViewOptionsView.FilterCriteriaDisplayStyle - Gets or sets the display style of filter conditions in the Filter Panel and built-in Filter Editor.

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”.

Note that the Find Panel does not highlight cells with HTML text.

Related API:

• ColumnViewOptionsFind.AllowFindPanel - Manages the availability of the Find Panel.
• ColumnViewOptionsFind.Behavior - Gets or sets whether records that do not match the query are hidden.
• ColumnViewOptionsFind.ParserKind - If a text entered into the Find Panel contains whitespaces, the Panel splits this string into separate logical blocks. The ParserKind setting specifies how these blocks are combined into a search query.
• ColumnViewOptionsFind.Condition - Gets or sets the condition according to which the Data Grid searches for the string entered into the Find Panel.
• ColumnViewOptionsFind.FindMode - Gets or sets whether a user must press Enter/click the “Find” button to start a search, or it starts automatically after a certain delay (the ColumnViewOptionsFind.FindDelay property).

See Find Panel to learn more.