Filter

The Filter Module facilitates the creation of powerful, dynamic filters for any column in AdapTable.

Filtering is designed to be intuitive and easy to use, but that simplicity of use 'hides' a great deal of complexity in terms of the filtering options and functonalities on offer.

note

Users can create Filters for as many Columns as required and the grid will automatically update so that it only shows rows that match the filters set for the column.

Predicate

Ultimately, a Filter is essentially a single Adaptable Predicate - an object which wraps a boolean function.

A Predicate contains a number of properties of which the most important are:

  • scope - where the Filter is applied, i.e. to one or more columns or to columns of a particular DataType.

  • handler - the boolean function to run when the filter is applied.

  • inputs - any additional values the Predicate requires (e.g. if its a 'GreaterThan' or 'LessThan' Predicate it will need a number, or if it is 'StartsWith' it will need a string).

    tip

    If the Predicate is 'IN' then the inputs will be an array of all the Column Values selected in the list.

For more information on Predicates see Predicate

UI Filter Controls

There are 2 UI 'controls' which provide filtering capabilities in AdapTable.

important

Both controls remain fully in sync with the other, and both automatically update Filter State.

Filter Form

This is powerful control that allows users to buld a Column Filter through the UI.

Every column in AdapTable - which is marked as filterable - will have a Filter Form to enable quick filter selection and creation.

The dropdown can appear in 2 places:

  1. When the Filter tab in the Column Header Menu is selected
  2. In the 'Filter' ToolPanel in the 'sidebar' (which appears by default at the right hand side of the grid)

The Dropdown contains 2 tabs:

  • Predicates - This lists all the available Predicates for this column (System and Custom) as radio buttons. Where a Predicate requirs an input (e.g. 'GreaterThan') this will appear when the Predicate is selected.

  • Column Values - This lists all the distinct Column Values for that Column (or any Permitted Values if they have been set). Each value is a checkbox enabling multiple items to be selected.

    tip

    Column Values become an 'IN' Predicate.

important

By default AdapTable's Filter Form will be used (in preference to one provided by the underlying Grid). To change this behaviour, set useAdaptableFilterForm to false in Search Options in order to use the underlying vendor grid's filter form instead of the one provided by AdapTable.

Quick Filter Bar

This is a textbox that is displayed in the row between the Column Header and the first data row in the grid - allows Filters to be created through text entry.

caution

In version 23 of AG Grid the Quick Filter Bar is set at column level and not grid level (through the floatingFilter property) so the Quick Filter bar will only appear if at least one column has this property set to *true** (and it is enabled just for those columns).

The left hand side of the Quick Filter Bar contains a dropdown showing all the System and Custom Filters with scope for that column. If the relevant predicate has inputs then the textbox will be enabled for these to be entered; otherwise it merely displays the name of the current predicate.

tip

If the predicate type is 'IN' (i.e. column values) then a list of all distinct values for that column will display.

AdapTable provides wildcard support to make it easy to switch between predicates in the textbox itself, e.g. typing '>' will switch to 'GreaterThan'. (See the full list in the Predicate documentation).

Default Predicate

Each DataType has a default predicate - which is active when the grid loads or the filter is cleared:

  • Number - Equals
  • String - Contains
  • Date - On

These can be changed in Search Options as follows:

PropertyOptions
defaultNumericColumnFilter'GreaterThan', 'LessThan', 'Equals', 'NotEquals'
defaultStringColumnFilter'Is', 'IsNot', 'Contains', 'NotContains', 'StartsWith', 'EndsWith', 'Regex'
defaultDateColumnFilter'After', 'Before', 'On', 'NotOn'

Setting Quick Filter Bar Visibility

The Quick Filter Bar can be hidden / displayed at any time you want by selecting the 'Show / Hide Quick Filter' menu option from any Column menu, or clicking the Show/Hide button in the Filter Toolbar.

important

By default AdapTable's Quick Filter Bar will be used (in preference to one provided by the underlying Grid). To change this behaviour, set useAdaptableQuickFilter to false in Search Options in order to use the underlying vendor grid's quick filter bar instead of the one provided by AdapTable.

The IsQuickFilterVisible property in Filter State allows you to show / hide the Quick Filter Bar at startup.

tip

This is only relevant, of course, if you have at least one column set to show Quick Filter.

This is a persistable property meaning that if the User hides the Quick Filter Bar then it will show as hidden the next time the Grid loads.

Opening Quick Filter Dropdown

By default the Quick Filter dropdown will automatically appear when the mouse hovers over a button.

However if this behaviour is unwanted then setting the quickfiltertrigger property in Search Options to 'click' will ensure the dropdown only appears when it is required.

Managing Column Filters

There are a number of options provided by AdapTable to help users configure and manage filters.

Seeing Filtered Columns

AdapTable makes it easy for users to see at a glance which columns have filters applied to them through the indicateFilteredColumns property in Search Options. This defaults to true and will distinctly render the Column Header for any filtered columns.

Clearing Filters

To remove the filter for a column click the 'Clear' button in the Filter Form or in the Column Filter Toolbar.

Auto-Applying Filters

By default the filter gets applied automatically - and the grid updated accordingly - each time a change is made to any predicate (e.g. another column value is added to an 'IN' predicate).

Where this is not the desired behaviour (e.g. if you are peforming filtering on the server and want to apply the final filter only), set autoApplyFilter to false in Search Options and a button will appear in the filter form; only on clicking that will the Filter be applied.

Saving Column Filters

Any active column filters when the system closes are saved to state and immediately re-applied on startup.

If this behaviour is not desirable set clearFiltersOnStartUp to true in Search Options and they will be cleared when AdapTable next loads.

Showing Column Values in Filters

By default AdapTable will display a list of current distinct column values in the Filter Form and the 'In' popup of Quick Filter.

However if Permitted Values have been provided in User Interface Options, they will be used instead.

The number of distinct column values displayed is specified by the maxColumnValueItemsDisplayed property in Search Options (the default value is 2,000).

Sorting Column Values in Filters

By default AdapTable will display the list of distinct column values in alphabetical order.

If you wish to sort these items according to how they are currently sorted in the grid, set the showColumnValuesUsingCurrentSortOrder property in Search Options to true.

tip

This is useful if a Date Column is sorted in reverse order and you wish the filter values to reflect that.

Other UI Elements

Filters also include the following UI Elements:

  • Popup - Shows a list of existing Column Filters with clear buttons

  • Toolbar - When any Column Filters are applied, it shows an Info icon which, when clicked, provides details of the Filters together with a clear option

    note

    The Toolbar also contains a Check Box to enable the Quick Filter Bar to be shown / hidden

  • Tool Panel - Same as Toolbar above

  • Column Menu contains 2 Menu Items:

    • Clear Filter Menu Item opens enables all Filters to be cleared (it is only visible if Filters are currently applied)
    • Show / Hide Quick Filter Bar Menu Item enables the Quick Filter Bar to be easily made visible or invisible (only available when a Quick Filter Bar is active)
  • Context Menu - contains a Filter on Cell Value(s) Menu Item which creates a Column Filter using the selected cell values (from a single column)

Entitlements

Filter Entitlement Rules:

  • Full: Everything is available to the User

  • Hidden: The Column Filter related Column and Context Menu items are hidden but the Column Filter itself is still available - this can never be hidden.

  • ReadOnly: N/A

System Filters

AdapTable provides a large number of 'out of the box' filters to apply to most use-cases.

tip

By default all the System Filter Predicates provided by AdapTable will be available, so only set the systemFilters property in Search Options if you don't want to use the full range:

export default {
searchOptions = {
systemFilters: ['Positive', 'Today', 'Blanks'],
};

The full list of System Filter predicates shipped by AdapTable can be found in the Predicate documentation.

Column Filters

The Filter section of Adaptable State contains a collection of Column Filters.

These are Predicates which have been applied to a particular column - either created by User at run-time and persisted automatically by AdapTable, or provided by a developer at design-time for first use.

important

Any active Column Filters when the system closes are automatically persisted and then immediately re-applied on startup.

note

This can be provided in Filter Predefined Config or, more typically, saved by AdapTable at run-time so its available when the system next restarts.

The Predicate can be either a System Filter Predicate (see above) or a Custom Predicate.

Column Filter Example

// Create 3 column Filters using the following predicates:
// Values ('Employee' column) which is basically an 'IN' predicate
// Between ('InovicedCost' column) which takes 2 numeric inputs
// InPast ('OrderData' column) which requires no inputs
Filter: {
ColumnFilters: [
{
ColumnId: 'Employee',
Predicate: {
PredicateId: 'Values',
Inputs: ['Janet Leverling', 'Margaret Peacock', 'Nancy Davolio'],
},
},
{
ColumnId: 'InvoicedCost',
Predicate: {
PredicateId: 'Between',
Inputs: [10, 300],
},
},
{
ColumnId: 'OrderDate',
Predicate: {
PredicateId: 'InPast',
},
},
],
},

Custom Filters

Developers are able to provide their own Filter Predicates at design-time.

This is done by creating an AdaptablePredicateDef and providing it to AdapTable via the Custom Predicate Defs collection in Adaptable Options.

/// Create 3 Custom Predicate Defs
// 'High Invoice' which will have a column scope of the 'OrderId' Column
// 'New Starter' which will have a column scope of the 'Employee' Column
// 'Post Takeover' which will have a column scope of all Date Columns
const adaptableOptions: AdaptableOptions = {
customPredicateDefs: [
{
id: 'high_invoice',
label: 'High Invoice',
columnScope: {
ColumnIds: ['OrderId'],
},
moduleScope: ['filter'],
handler(params: PredicateDefHandlerParams) {
const invoiced: number = params.node.data.InvoicedCost;
const itemCount: number = params.node.data.ItemCount;
return invoiced > 100 && itemCount > 10 ? true : false;
},
}
{
id: 'new_starter',
label: 'New Starter',
columnScope: {
ColumnIds: ['Employee'],
},
moduleScope: ['filter', 'conditionalstyle'],
handler(params: PredicateDefHandlerParams) {
return (
params.value == 'Robert King' ||
params.value == 'Laura Callahan' ||
params.value == 'Andrew Fuller'
);
},
},
{
id: 'post_takeover',
label: 'Post Takeover',
columnScope: {
DataTypes: ['Date'],
},
moduleScope: ['filter', 'alert'],
handler(params: PredicateDefHandlerParams) {
const takeOverDate = new Date('2019-09-21');
return (params.value as Date) > takeOverDate;
},
},
],
};

Using Custom Filters

To use a Custom Filter Predicate in a Column Filter is trivial:

Filter: {
ColumnFilters: [
{
ColumnId: 'Employee',
Predicate: { PredicateId: 'new_starter' },
},
{
ColumnId: 'LastUpdatedTime',
Predicate: { PredicateId: 'after_work' },
},
],
},
const adaptableOptions: AdaptableOptions = {
customPredicateDefs: [
{
id: 'new_starter',
label: 'New Starter',
columnScope: { ColumnIds: ['Employee'] },
moduleScope: ['filter'],
handler(params: PredicateDefHandlerParams) {
return (
params.value == 'Robert King' ||
params.value == 'Laura Callahan' ||
params.value == 'Andrew Fuller'
);
},
},
{
id: 'after_work',
label: 'After Work',
columnScope: { ColumnIds: ['LastUpdatedTime'] },
moduleScope: ['filter'],
handler(params: PredicateDefHandlerParams) {
return (params.value as Date).getHours() > 17;
},
},
],
};

FAQ

Can we do AND or OR in the Quick Filter Bar?

No, but this is available via a Query.

Do you offer wildcards in the Floating Filter e.g. for Greater Than?

Yes, there are wilcard options for most Filter types. See the Appendix below.

Can a user include column values in a Query that are not present in the grid when the Query is created?

Yes. The easiest way to do this is via Permitted Values. You can set the permitted vales for each column, and can include any values you want.

Additionally, if the Query is built at design time and shipped with the product then it can include any Column Values that the developers want to include - they don't need to be present in the grid at the time of creation.

The Predicate for string columns is always 'Contains'. Can we change this to 'StartsWith'?

Yes. Use the defaultStringColumnFilter property in Search Options (other settings allow you to change the defaults for numeric and date columns also).

Appendix: Quick Filter Bar Wildcards

SymbolPredicateColumns
=EqualsText, Number
>Greater ThanNumber
<Less ThanNumber
:BetweenNumber
[IN / ValuesAll
#IN / ValuesAll

More Information