# Column Filters

Canonical page: https://www.adaptabletools.com/docs/handbook-column-filter

- AdapTable offers very rich, powerful **column filtering** capability
- Each Column Filter can contain multiple conditions (using Expressions) together with AND / OR functionality
- These can either be Expression Functions (provided by AdapTable) or Custom Functions (defined by users)
- Column Filters complement (and can be used together with) the [Grid Filter](https://www.adaptabletools.com/docs/handbook-grid-filter/index.md)
- There are 2 Custom Filter Components provided:
  - Filter Bar - between Column Header and first row of Grid
  - Filter Form - available from Column Menu, Filter Tool Panel and other places

Column Filtering in AdapTable is extremely intuitive, rich and extensible.

Column Filters complement, and work in conjunction, with the [Grid Filter](https://www.adaptabletools.com/docs/handbook-grid-filter/index.md); both can be active at the same time

Users can create as many Column Filters as required and AdapTable will automatically update AG Grid so that only rows which match **all** Column Filters will be displayed

Filtering is a <b>constant operation</b> - Column Filters will be continually re-applied as data in the grid changes

## Filter Components

AdapTable offers 2 [Filter Components](https://www.adaptabletools.com/docs/handbook-column-filter-components/index.md) to enable users to perform run-time Column Filtering:

- **Filter Form**, a rich, [configurable](https://www.adaptabletools.com/docs/technical-reference-filter/index.md) component that provides full filtering capability
- **Filter Bar** (underneath Column Header), designed to facilitate instant filtering (including wildcards)

Both components offer full, advanced, filtering capability including:

- displaying all available Column Filter Expression Functions (for that Column) and any inputs required
- support for Custom Expression Functions
- dedicated UI for `IN` (and `NotIn`) Expression Function enabling [multiple values to be displayed and selected](https://www.adaptabletools.com/docs/handbook-column-filter-in-filter/index.md)
- automatically updating the [Layout](https://www.adaptabletools.com/docs/handbook-layouts/index.md) (and [Layout State](https://www.adaptabletools.com/docs/technical-reference-layouts/index.md)) as Column Filters are applied

- The Filter Form and Filter Bar **replace** AG Grid's Filter Form and Floating Filter respectively
- They are the **only** instances where AdapTable provides alternative, not complementary, functionality to AG Grid

### Other Filter Components

In addition to the 2 Filter Components, run-time users can create and edit Column Filters in the [Layout Wizard](https://www.adaptabletools.com/docs/ui-settings-panel-wizards/index.md).

AdapTable also provides 4 other filter-related components.

These allow Users to *clear* or *suspend* some or all current Column Filters and are available as:

- [Dashboard Toolbar](https://www.adaptabletools.com/docs/ui-dashboard/index.md#tabs-and-toolbars)
- [Tool Panel](https://www.adaptabletools.com/docs/ui-tool-panel/index.md)
- [Status Bar Panel](https://www.adaptabletools.com/docs/ui-status-bar/index.md)
- [Settings Panel](https://www.adaptabletools.com/docs/ui-settings-panel/index.md)

## Expressions

Column Filters are essentially a simple combination of 2 objects:

- an [Adaptable Column](https://www.adaptabletools.com/docs/dev-guide-columns-adaptable-column/index.md) - **where** the Column Filter is applied
- an [Expression](https://www.adaptabletools.com/docs/adaptable-ql-expression/index.md) which is evaluated by [AdapTableQL](https://www.adaptabletools.com/docs/adaptable-ql-expression/index.md) - **what** is applied

- The Expression is always limited to a **single column**
- Use the [Grid Filter](https://www.adaptabletools.com/docs/handbook-grid-filter/index.md) to create Expressions that reference, or compare, multiple columns

**Example: Filters**

Basic Filtering using Expressions

- This example shows 5 Column Filters applied across 2 Layouts:
- The `Filtered` Layout contains 3 Filters:
  - `Language` is 'TypeScript' or 'JavaScript' - using the `IN` function (similar to SQL): `[language] IN ("TypeScript", "JavaScript")`
  - `Name` ends with '.js' - using the `ENDS_WITH` function: `ENDS_WITH([name], ".js")`
  - `Published` since 2016 - using a date comparison: `[pushed_at] > DATE("2016-01-01")`
- The `MIT` Layout contains 2 Filters:
  - `License` equals 'MIT License' - using `=`: `[license] = "MIT License"`
  - `Github Stars` between 20k and 90k - using `>=` and `<=`: `[github_stars] >= 20000 AND [github_stars] <= 90000`
- We have also chosen to display the Column Filter [Toolbar](https://www.adaptabletools.com/docs/ui-dashboard/index.md#tabs-and-toolbars) in the Dashboard, and set the Clear and Suspend Buttons to be displayed

- Switch between Layouts and see that the different Layouts display different Filters
- Change the Column Filter for the `Language` Column to include 'JavaScript' and 'HTML' projects

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts       [template]
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Basic Filtering',

  initialState: {
    Dashboard: {
      ModuleButtons: ['ColumnFilter', 'Layout'],
      PinnedToolbars: ['Layout', 'ColumnFilter'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'ColumnFilter'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Filtered',
      Layouts: [
        {
          Name: 'Filtered',
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'license',
            'pushed_at',
          ],
          ColumnFilters: [
            {
              ColumnId: 'language',
              Expression: '[language] IN ("TypeScript", "JavaScript")',
            },

            {
              ColumnId: 'name',
              Expression: 'ENDS_WITH([name], ".js")',
            },
            {
              ColumnId: 'pushed_at',
              Expression: '[pushed_at] > DATE("2016-01-01")',
            },
          ],
        },
        {
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'license',
            'pushed_at',
          ],
          ColumnFilters: [
            {
              ColumnId: 'license',
              Expression: '[license] = "MIT License"',
            },
            {
              ColumnId: 'github_stars',
              Expression: '[github_stars] >= 20000 AND [github_stars] <= 90000',
            },
          ],
          Name: 'MIT',
        },
      ],
    },
  },
};
```

### Custom Filters

As seen above, most Column Filters use the built-in [Expression Functions](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions/index.md) that AdapTable provides.

However Column Filters can also leverage bespoke, **user-defined** [Custom Expression Functions](https://www.adaptabletools.com/docs/handbook-column-filter-custom-filters/index.md) - for instance to wrap a lookup over many values in a single, reusable, named function.

### Combining Clauses with AND / OR

A single Column Filter Expression can contain **multiple clauses** if required.

These are combined using the `AND` and `OR` keywords (with parentheses for grouping).

The same AND / OR logic is available in [Alerts](https://www.adaptabletools.com/docs/handbook-alerting/index.md), [Conditional Styling](https://www.adaptabletools.com/docs/handbook-column-formatting-conditions/index.md) and [Flashing Cells](https://www.adaptabletools.com/docs/handbook-flashing-cell/index.md)

Use `AND` when **all** clauses must be true for a row to pass, and `OR` when **any** clause passing is enough.

- Expressions with multiple clauses are created in the Filter Form
- The Filter Bar is designed for instant, single-clause filtering

**Example: Filters using AND / OR**

Filtering using multiple clauses (AND / OR)

- This example shows 2 Column Filters, each combining two clauses with a different operator:
  - `Name` - `CONTAINS` 'e' **AND** `ENDS_WITH` 's': `CONTAINS([name], "e") AND ENDS_WITH([name], "s")`
  - `Github Stars` - less than 10,000 **OR** greater than 20,000: `[github_stars] < 10000 OR [github_stars] > 20000`

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts       [template]
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Multiple Expression Clauses',

  initialState: {
    Dashboard: {
      ModuleButtons: ['ColumnFilter'],
      PinnedToolbars: ['Layout', 'ColumnFilter'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'ColumnFilter'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Filtered',
      Layouts: [
        {
          Name: 'Filtered',
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'license',
            'pushed_at',
          ],
          ColumnFilters: [
            {
              ColumnId: 'name',
              Expression: 'CONTAINS([name], "e") AND ENDS_WITH([name], "s")',
            },
            {
              ColumnId: 'github_stars',
              Expression: '[github_stars] < 10000 OR [github_stars] > 20000',
            },
          ],
        },
      ],
    },
  },
};
```

### Formatted Cells

AdapTableQL evaluates the Expression based on the underlying value in the Cell.

AdapTable will evaluate Filters using the **raw** value of the cell (rather than the display value)

If a column uses an AG Grid [Cell Component](https://www.ag-grid.com/javascript-data-grid/cell-rendering/), an AG Grid [Value Formatter](https://www.ag-grid.com/javascript-data-grid/value-formatters/) or an Adaptable [Display Format](https://www.adaptabletools.com/docs/handbook-column-formatting-display-format/index.md), the filter is evaluated using the cell's underlying (i.e. raw) value.

AdapTable will not evaluate based on what is actually displayed in the Grid.

- One potential exception to this rule is the `IN` function which shows the cell's display value
- But only if an AG Grid [Value Formatter](https://www.ag-grid.com/javascript-data-grid/value-formatters/) or Adaptable [Display Format](https://www.adaptabletools.com/docs/handbook-column-formatting/index.md#display-formats) are used (and not an [AG Grid Cell Component](https://www.ag-grid.com/javascript-data-grid/cell-rendering/))

## Using Column Filters

Run-time users are able to create and edit Column Filters using all the available Filter Components.

In addition, users can leverage the Column Filter [Toolbar](https://www.adaptabletools.com/docs/ui-dashboard/index.md#tabs-and-toolbars), [Tool Panel](https://www.adaptabletools.com/docs/ui-tool-panel/index.md), [Status Bar Panel](https://www.adaptabletools.com/docs/ui-status-bar/index.md) and [Settings Panel](https://www.adaptabletools.com/docs/ui-settings-panel/index.md) to clear, suspend or share some or all current Filters.

Column Filters are persisted in the Layout section of the Adaptable State and then automatically re-applied on application re-start when the relevant Layout is loaded.

### Creating Filter via Context Menu

The [Context Menu](https://www.adaptabletools.com/docs/ui-context-menu/index.md) has a "Filter on Cell Value(s)" option (available only if the selected cells are in one Column).

This will create a new `Equals` Filter based on the value of what is the currently selected cell.

If there is more than one distinct cell value in the Column's selection range, an `IN` filter is created instead

### Seeing Current Filters

AdapTable shows users the currently applied Column Filters in AG Grid in the Filter [Toolbar](https://www.adaptabletools.com/docs/ui-dashboard/index.md#tabs-and-toolbars), [Tool Panel](https://www.adaptabletools.com/docs/ui-tool-panel/index.md), [Status Bar](https://www.adaptabletools.com/docs/ui-status-bar/index.md) and [Settings Panel](https://www.adaptabletools.com/docs/ui-settings-panel/index.md) .

Each provides a description of the Expression and Column together with a _Clear_ button

- AG Grid indicates which Columns are filtered in the Column header and Filter Tool Panel (in Sidebar)
- AdapTable changes the style of the Column Header for any currently filtered columns

### Suspending Filters

Column Filters - like all AdapTable Objects - can be [suspended](https://www.adaptabletools.com/docs/dev-guide-adaptable-state-initial-state/index.md#adaptable-objects#suspending-objects).  

When a Column Filter is suspended, the Filter Bar and Filter Form show details of, but do not run, the associated Expression.

You need either to un-Suspend or Clear a suspended Filter before setting a new Filter for that Column

## Filtering Dynamic Columns

Columns created dynamically by AG Grid can be filtered (click the links to see demos):

- [Row Grouped Columns](https://www.adaptabletools.com/docs/handbook-grouping-rows/index.md#filtering) (a filter icon in Row Grouped column opens the [Filter Form](https://www.adaptabletools.com/docs/handbook-column-filter-components/index.md) when clicked)
- [Pivot Result Columns](https://www.adaptabletools.com/docs/handbook-layouts-pivot-filtering/index.md) 
- [Tree Grid Key Columns](https://www.adaptabletools.com/docs/handbook-tree-data-grid/index.md#filtering) (the Tree column uses an `IN` Filter with a tree structure)

## Configuring Filters

Developers are able to configure Filters at design time as follows:

- Use [Layout Initial State](https://www.adaptabletools.com/docs/technical-reference-layouts/index.md) to **define** Column Filters
- Use [Column Filter Options](https://www.adaptabletools.com/docs/technical-reference-filter/index.md) section of Adaptable Options to **configure** Filters behaviour and display

See [Defining Column Filters](https://www.adaptabletools.com/docs/handbook-column-filter-defining/index.md) and [Configuring Column Filters](https://www.adaptabletools.com/docs/handbook-column-filter-configuring/index.md) for more details

### FAQ

**Can I filter Row Group, Pivot or Tree columns?**
Yes. See [Filtering Dynamic Columns](#filtering-dynamic-columns) — Row Grouped, Pivot Result and Tree Grid Key Columns all use the AdapTable Filter Form. The Tree column defaults to an `IN` Filter with a tree structure.

**Why doesn't the Column Filter work with my AG Grid Cell Component?**
AdapTable always filters on a cell's **underlying** (or raw) value than the display value.
This is because the column could be numeric but the cell could display a string (e.g. '£24K'), so to ensure filters always work they are always applied on the underlying value.

**Can we evaluate Column Filters on the server?**
Yes, via the `evaluateAdaptableQLExternally` property in [Expression Options](https://www.adaptabletools.com/docs/technical-reference-expression/index.md) (see [Server Evaluation](https://www.adaptabletools.com/docs/adaptable-server-evaluation/index.md) for more details)

This option is only available if you are using the AG Grid **Client Side Row Model**.

**Can we include AND or OR logic in a Column Filter**
Yes, this is available in Column Filters - and also in the Grid Filter

**Why don't my Filters get applied when the data changes in my grid?**
AdapTable re-applies active Column Filters and the Grid Filter when cell values change — both user edits and ticking data. There is no option to skip ticking updates. If nothing changes, check that a Filter is actually active on the Column that updated.

**The filter for string columns defaults to `CONTAINS`. Can we change this to `STARTS_WITH`?**
Yes. Use the `defaultTextColumnFilter` property in [Column Filter Options](https://www.adaptabletools.com/docs/technical-reference-filter/index.md).
Similar settings allow you to change the defaults for numeric and date columns.

**Are we only able to set the Filter for the Current Layout?**
If you are using the Filter Bar or Filter Form to create Filters, then they do, indeed, only update the Current Layout.
However you can use the Layout Wizard to set Column Filters for any Layout (and not just the Current one)
