Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Collapse]Data Grid
   [Expand]Getting Started
   [Expand]Binding to Data
   [Expand]Grid View
   [Expand]View Technology
   [Expand]Grouping
   [Expand]Sorting
   [Expand]Summaries
   [Expand]Data Editing
   [Collapse]Filtering and Locating Rows
    [Collapse]Filtering
     [Expand]Filter Dropdowns
     [Expand]Tutorials
      Filtering Overview
      Filter Settings
      MRU Filters
      Automatic Filtering Row
      Filter Editor
      Custom Filter Dialog
      Filtering in Code
     Locating Rows in Code
     Incremental Search
     Searching via Find Panel
     Tutorial: Incremental Search
     Tutorial: Search/Find Panel
   [Expand]Focus and Selection Handling
   [Expand]Processing Rows
   [Expand]Formatting Cell Values
   [Expand]Master-Detail Relationships
   [Expand]Asynchronous Image Load
   [Expand]Export and Printing
   [Expand]Appearance and Conditional Formatting
   [Expand]Batch Modifications
   [Expand]Hit Information
   [Expand]Hints
   [Expand]Popup Menus
   [Expand]Saving and Restoring Layouts
   [Expand]Visual Elements
   [Expand]Design-Time Features
   [Expand]Examples
   [Expand]End-User Capabilities
    Included Components
  [Expand]Vertical Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Expand]Spreadsheet
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Filtering in Code

This topic covers information on filtering data in code and contains the following subsections.

For general information on filtering, see Filtering Overview.

Expanded Online Video

This video shows you how to filter and locate rows in code.

Expanded General Information

In code, you can apply filters to individual columns and Views. The difference between filters applied to a column and a View is in the way filters can be cleared. If a filter is applied to a specific column, this column's dropdown contains the (All) option, and clicking on it clears the filter.

Note that in code, you can create a filter expression that involves multiple columns and then apply this filter to a specific column. Only the column to which the filter is applied will contain the (All) option.

If a filter is applied to a View, filter buttons of columns that are involved in the filter expression are highlighted. Filter dropdowns of these columns, however, do not contain (All) options.

To clear this type of a filter, an end-user can click the Enable or Close button in the Filter Panel.

Expanded Filter Expressions

The simplest text filter expression has the following notation: "[FieldName] = Value". This selects records that have a Value in the column with the specified field name.

In filter expressions, you can use various comparison operators that are dependent upon the types of field names. For instance, if a FieldName is of the numeric or string type, you can use these operators: >, <, <>. For fields of the string type, you can also use the 'like' operator to implement a partial string comparison. The following table covers available comparison operators.
Comparison Operator Description Example
= Equal to.

The operator can be applied to numeric, date-time, string and Boolean values.

[ProductID] = 999
> Greater than.

The operator can be applied to numeric, date-time and string values.

[ProductName] > 'Uncle'
< Less than.

The operator can be applied to numeric, date-time and string values.

[UnitsInStock] < 100
>= Greater than or equal to.

The operator can be applied to numeric, date-time and string values.

[BirthDate] >= #01/01/1980#
<= Less than or equal to.

The operator can be applied to numeric, date-time and string values.

[CreatedDate] <= #10/2/2006#
<> Not equal to.

The operator can be applied to numeric, date-time, string and Boolean values.

[Priority] <> 'Low'
LIKE Operator 'LIKE'.

The operator is used for a partial string comparison. It returns TRUE if the value being tested matches the specified pattern.

You can use the '%' and '_' symbols as wildcards in patterns. The '%' wildcard substitutes any number of characters. The '_' character is used to represent any single character.

[ProductName] LIKE '%Sauce%'
NOT LIKE Operator 'NOT LIKE'.

The operator is used for partial string comparison. It returns TRUE if the value being tested doesn't match the specified pattern. See the description of the LIKE operator for more information.

[IssueSubject] NOT LIKE 'Help:%'
IS NULL Checks whether the value being tested is the NULL value. [Region] IS NULL
IS NOT NULL Checks whether the value being tested is not the NULL value. [Region] IS NOT NULL

String constants must be enclosed within single quote characters. If a single quote character needs to be included as a literal to a filter, it must be doubled. Example:

[ProductID] LIKE 'Uncle Bob''s%'

Date-time constants must be wrapped with the '#' characters and represented using a culture-independent (invariant) format. The invariant culture is based on the English culture, but some of the idiosyncratic English formats have been replaced by more globally-accepted formats. Below are some of the culture-independent formats for representing date-time values.

MM/dd/yyyy    07/30/2008
dd MMM yyyy    30 JUL 2008
yyyy-MM-dd    2008-07-30
yyyy-MM-ddTHH:mm:ss    2008-07-30T22:59:59
yyyy-MM-dd HH:mm:ssZ    2008-07-30 15:59:59Z

Example:

[CreatedDate] <= #07/30/2008#

To learn more about invariant cultures, please refer to MSDN.

If the '%', '_' or '[' character must be used as a literal in a pattern for the LIKE operator, enclose it with square brackets ('[]'). Example:

[Note] LIKE '%[_]%'

Filter expressions can contain multiple clauses combined by logical AND, AND NOT, OR and OR NOT operators. Clauses can be grouped using parentheses. Example:

[Type] = 'Request' AND NOT ([Status] = 'Fixed' OR [Status] = 'Rejected')

In specific instances, grid column edit values and displayed values may not match (for instance, if a column uses an ImageComboBoxEdit or LookUpEdit in-place editor, or if the values are formatted in a specific manner, or if custom display text is provided using the ColumnView.CustomColumnDisplayText event). By default, when creating a filter expression, columns must be compared with edit values, but not with displayed values. If column edit values and displayed values do not match, and you want to compare the column with a displayed value in a filter expression, set the GridColumn.FilterMode property to DisplayText.

Expanded Apply a Filter to a View

To apply a filter to a View in code, use one of the following properties:

These properties accept strings that specify filter expressions. Assigning a string to the ViewFilter.NonColumnFilter doesn't clear any filters applied to individual columns. The ColumnView.ActiveFilterString property specifies the overall filter expression applied to the View and its columns. So, assigning a string to the ColumnView.ActiveFilterString property clears the filter specified by the ViewFilter.NonColumnFilter property and all the filters assigned to the View's columns.

The following code applies a filter that selects records that contain value 3 in the Category column and false in the Discontinued column.

The result is displayed below.

In this example, the Category column uses an ImageComboBoxEdit in-place editor, which represents underlying integer values as strings with images. For instance, value 3 is represented by the 'Dairy Products' string. By default, the column must be compared with an edit value. So, to select records that contain the 'Dairy Products' string, the "Category = 3" filter is used.

If you want to create a filter expression using displayed values of the Category column, set the column's GridColumn.FilterMode property to ColumnFilterMode.DisplayText. The following code has the same result as the code above.

The syntax of using the ViewFilter.NonColumnFilter property is the same as using the ColumnView.ActiveFilterString property. The following code selects records in a CardView that have 'Saloon' or 'Sports' values in the CarCategory column.

Expanded Apply a Filter to a Column

To assign a filter to a column you need to create a ColumnFilterInfo class object. This specifies the value to compare against, or a custom filter string. Then, you need to associate the created ColumnFilterInfo object to a specific column using one of the following methods.

Note that a ColumnFilterInfo object can represent a filter expression that involves multiple columns (not only the one that the ColumnFilterInfo object is associated with).

Properties of the ColumnFilterInfo object are read-only, so it is not possible to change object settings after it has been created. If you need to modify a column filter, create a new ColumnFilterInfo object and assign it to the column.

Below are some examples of using ColumnFilterInfo constructors.

Expanded Example - Filtering by a Single Value

If you need to filter a column by a specific value, you can use ColumnFilterInfo class constructors that have an 'object value' parameter.

  • ColumnFilterInfo(GridColumn column, object _value)

    This constructor creates a filter of the "[ColumnFieldName] = _value" type, in which the ColumnFieldName is the field name of the column. Note that like other filters, this filter is only applied to a View after the created ColumnFilterInfo object has been assigned to a column.

  • ColumnFilterInfo(string filterString, object _value)

    The _value parameter is in effect if the filterString parameter is set to an empty string. After the created ColumnFilterInfo object is assigned to a specific column, the "[ColumnFieldName] = _value" filter is created and applied to this column. If the filterString parameter is not empty, the _value parameter is ignored and the filter is specified by the filterString.

  • ColumnFilterInfo(ColumnFilterType type, object _value, string filterString)

    The _value parameter is in effect if the filterString parameter is set to an empty string and the type parameter is set to ColumnFilterType.Value.

The following code creates a filter that selects records, whose ShipCountry field values are equal to 'Poland', but applies the created filter to a Customer column.
The result is displayed below.

You can also filter a column by a single value by building a filter expression that performs such a filtration. Use ColumnFilterInfo constructors that have the 'string filterString' parameter. The following code is equivalent to the code above.

When using constructors with the filterString parameter, you can use any comparison operator, not only the EQUAL operator.

Expanded Example - Building Complex Filters

You can apply filters of any level of complexity by using the ColumnFilterInfo constructors with the 'string filterString' parameter.

  • ColumnFilterInfo(string filterString)

The filterString parameter should specify a filter expression. Again, the filter is only applied after the created ColumnFilterInfo object is assigned to any column.

The following code selects records that have the ShipCountry field's value set to 'Norway' or 'Sweden'.

The following code selects records whose shipping country names start with 'F'.

It is also possible to create filter criteria involving multiple columns. For instance, the following code selects orders from USA with a ShippedDate greater than the RequiredDate.

In this example, the filter contains conditions for the ShippedDate, RequiredDate and ShipCountry fields. The filter is assigned to the GridColumn.FilterInfo property of the RequiredDate column. That is why only that column's filter button is drawn as "active".

Expanded Controlling the Filter's Textual Representation

The Grid Control automatically generates text used to represent filters within the Filter Panel. It is generated based on display values of cells and settings of in-place editors. It is possible to override the automatically generated filter display text when applying filters to a View and a column.

To provide custom filter display text when applying a filter to a column in code, use a ColumnFilterInfo class constructor that takes the 'string displayText' parameter. This parameter specifies the string that will represent this filter within the filter panel.

The ColumnView.CustomFilterDisplayText event allows you to provide a custom filter display text when a filter is applied to a column or a View in code or by an end-user at runtime.

Expanded Clearing Filters

There are several methods to clear filters.

The GridColumn.ClearFilter method allows you to clear filter settings for a particular column. Alternatively, you can assign an empty ColumnFilterInfo object to the GridColumn.FilterInfo property.

Setting the ViewFilter.NonColumnFilter property to an empty string clears the filter expression applied to a View (that was previously assigned to the ViewFilter.NonColumnFilter property), but doesn't affect filter settings of columns.

To clear filter settings for a View and its columns, set the ColumnView.ActiveFilterString property to an empty string or call the ColumnView.ClearColumnsFilter or ColumnView.ActiveFilter.Clear method.

Expanded See Also

How would you rate this topic?​​​​​​​