TreeGrid v6.0

Filtering rows

TreeGrid documentation index

 

TreeGrid supports filtering rows by one or more columns by setting the value to filter and filter operator to use.

It is similar to MS Excel simple filter.

TreeGrid also supports advanced filtering by any formula or special search, see Search.

Strings can be filtered case sensitive or insensitive. Can be also filtered according to actual browser language or by special settings. See comparing strings.

 

Filtering can be changed by a user in special Filter row by setting cell value or filter operator.

Filtering can be preset in XML in the special Filter row by setting cell value and filter operator.

Filtering can be changed by API by ChangeFilter method.

 

<Cfg>                bool       Filtering   [1]                                                                                                                                                            .

If rows can be filtered.

Setting it to 0 disables all filtering functions, including Search and SetFilter filters.

 

<Cfg>                bool       Filtered     [1]         Saved to cookies, to not load it, set FilteredLap=’1’                                                  chg 7.0 .

If basic filtering is enabled by user.

A user can enable / disable filtering by click to Filter row left panel.

Since 7.0 it affects only filters done by <Filter> rows, not the Search and SetFilter filters.

 

<Cfg>                bool       FilterEmpty       [0]                                                                                                                         deleted 7.0   .

Since 7.0 the CanFilter&2 is tested always, like FilterEmpty = 1

If set to 1 when filtering, hides all rows with CanFilter=2 (or 3) that have all children filtered/hidden.

 

<Cfg>                int          StandardFilter            [0]                                                                                                                                 .

Filter behavior in tree

 

0           The filter is applied to parents before children to speed up filtering.

 

1           The filter is applied to children before filtering parent. Use it if parent is filtered according to visibility of its children, when uses aggregate functions. It can be much slower.

 

2           It shows all rows that fulfill the filter condition in tree. It shows all parent rows if some their children fulfill the condition.

In tree by default the filter hides all rows that do not fulfill the filter condition, to do oppose behavior, to show all rows that fulfill the condition, set StandardFilter=’2’

 

<C>                   int          CanFilter              [1]                                                                                                                                              .

Rows can be filtered by this column.

If set to 2, it is also tested cell’s CanFilter attribute.

 

<cell>                bool       CanFilter              [1]                                                                                                                                              .

If the row can be filtered according to this column. Used only when the column has set CanFilter=’2’.

 

<I>                     int          CanFilter              [1]                                                                                                                                               .

How row can be filtered (hidden) by any filter, bit array.

&1 – if row can be hidden by any filter

&2 – if row is hidden if all its children are hidden

 

<cell>                string    FilterValue                                                                                                                                              chg 7.0 .

Cell value used for filtering and searching, useful for special non editable cells like Html, Icon, Abs, Gantt, ..., see also OnGetFilterValue event.

 (Deleted in 7.0) For searching it is not used for Int and Float types and also not for SearchMethod = 2.

It is also used by FilterTextRange and FilterDateRange methods, but not by SetFilter method.

 

API event          type      OnGetFilterValue    (TGrid grid, TRow row, string col, type val)                                                             .

Called to get cell value for filtering and searching.

val is predefined value, returns val or new value.

 

API variable      int          FilterCount                                                                                                                                            new 7.0 .

Count of visible variable rows after filter is applied.

It is null if the filter was not run.

 

<Cfg>                bool       SaveFilters        [0]                                                                                                                                 new 6.7 .

If set to 1, saves to cookies the custom filters set by SetFilter method.

It does not affect the standard or search filters.

 

Comparing strings

 

You can also specify these settings for whole grid in default column <Def><D Name=’C’/></Def>.

 

<C> <cell>        bool       LocaleCompare        [0]                                                                                                                   new 6.0 .

If strings are compared according to browser’s locale settings. For sorting, filtering and grouping.

For Filter it affects just case sensitivity if set also CaseSensitive=’0’

If set to row cell, it affects row’s children for sorting and grouping. For filter it can be set only to Filter cell and affects also all values in the column.

 

<C> <cell>        bool       CaseSensitive             [1]                                                                                                                   new 6.0 .

If strings are compared case sensitive. For sorting, filtering and grouping.

If set to row cell, it affects row’s children for sorting and grouping. For filter it can be set only to Filter cell and affects also all values in the column.

 

<C> <cell>        string    WhiteChars                                                                                                                                            chg 6.0 .

List of characters to ignore when sorting, filtering or grouping.

It can be set for example to “ “ (space) to ignore spaces (it means that for example “a b   c” and “abc” strings are the same.

Setting this attribute can slow down sorting, grouping and filtering.

If set to row cell, it affects row’s children for sorting and grouping. For filter it can be set only to Filter cell and affects also all values in the column.

 

<C> <cell>        string[*] CharCodes                                                                                                                                             upd 6.0 .

It changes characters in comparing strings for sorting, filtering and grouping.

It is first character separated array of pair of characters to replace the first character by the second when comparing strings.

For example “|y|i|Y|I|.|,” – replaces ‘y’ by ‘i’, ‘Y’ by ‘I’ and ‘.’ by ‘,’

– it means that when comparing strings does not differ between letter ‘y’ and ‘i’, both cases and also does not differ between comma and point.

The first character must be one letter, but the second in fact can be any letter or string or even empty string, like “|a|xxx|b||c|ff”

If you use TreeGrid server DLL/SO, there must be the second character one letter only too.

Setting this attribute can slow down sorting, grouping and filtering.

If set to row cell, it affects row’s children for sorting and grouping. For filter it can be set only to Filter cell and affects also all values in the column.

 

User interface to choose filter

 

User interface for filtering is shown by fixed <Filter> row. The <Filter> can be placed to <Head> or <Foot> section.

The <Filter> row is usually only one, but there can be also more filter rows placed to <Head> and / or <Foot> tags to provide more filters by one column.

The <Filter> row is standard data row with cells and can have also all standard row and cell attributes.

 

The default filter cell differs according to the filter cell / column type.

Text, Lines        Left filter menu shows all operators including strings. String filter is done

Int, Float           Left filter menu shows only basic comparing operators, not the strings. Number filter is done

Date                   Left filter menu shows only basic comparing operators, not the strings. Number filter is done, the dates are compared as numbers. Right side calendar is shown.

Enum                 Left filter menu is hidden. User chooses one item from enum list to filter by. The first item in list is FilterOff. It is possible to set Range to choose more values to filter by.

Radio                 Left filter menu is hidden. User chooses check one radio button to filter by. The last radio button is FilterOff. It is possible to set Range to choose more values to filter by.

Bool                   Left filter menu is hidden. The check box has three states – on, off, filteroff

Pass                   Filter is disabled

Panel                  It is shown Filtered On / Off checkbox

Link                   It is shown Text type and filter provides string compare with link text or if missing with link url

Img                    It is shown Text type and filter provides string compare with link url or if missing with image url

Button, Html, Icon, Abs, Gantt, List        It is shown Text type cell with left filter menu and the filter provides string compare for the cell values.

To not show the filter cell and filter menu, set <C CanFilter=’0’/>

 

The predefined or actual filter is set in individual cells by the cell value and attribute Filter.

 

<Filter cell>      type      value            Saved to cookies, to not load it, set <Cfg FilterLap=’1’ />                                                                   .

Predefined or actual filter value to filter the column by.

It is used only if Filter is set to value > 0.

 

<Filter cell>      int          Filter            [0]         Saved to cookies, to not load it, set <Cfg FilterLap=’1’ />                                                     .

Predefined or actual filter operator to filter the column by.

Can be one of predefined filters:

Off:                    0 – Off

Number filter:    1 – Equal, 2 – Not equal, 3 – Less than, 4 – Less than or equal, 5 – Greater than, 6 – Greater than or equal

String filter:        7 – Begins with, 8 – Does not begin with, 9 – Ends with, 10 – Does not end with, 11 – Contains, 12 – Does not contain

 

<Filter cell>      int          DefaultFilter     [1/11]                                                                                                                                         .

Predefined filter operator, it is automatically selected when filter is off and user changes filter cell value.

Default value is for Text, Lines, Img and Link types 11 and for other types 1. See Filter attribute.

If set to 0, no automatic selection is done.

 

<Filter cell>      string    FilterOff   [‘’]                                                                                                                                              upd 6.0 .

Value entered to cell that automatically removes filter from the cell (sets Filter to 0).

By default it is empty string for all editable types and new value for empty value for Enum and item Off for Radio.

For Enum and Radio it can be not existing item – it is added to the list, or existing item that switches off the filter.

 

<Filter cell>      bool       FilterEnumKeys        [0]                                                                                                                   new 7.0 .

For Enum type cell. If set to 0 it filters by Enum string. If set to 1 it filters by Enum value, by key or index.

 

<Filter cell>      bool       ShowMenu        [1/0]                                                                                                                                           .

If filter menu icon is visible.

You can hide it to use only DefaultFilter and Defaults button to select one value or more value to filter the column by.

By default is 0 for Bool, Enum and Radio types and 1 for all other types.

 

<Filter cell>      int []       MenuItems        [...]                                                                                                                                             .

Comma separated array of operators displayed in FilterMenu, can contain numbers from 0 to 12 in any order.

For possible values see Filter attribute.

Default value varies according to cell type, Bool = “0,1,2”, Int, Float, Date = “0,1,2,3,4,5,6”, other = “0,1,2,3,4,5,6,7,8,9,10,11,12”

 

<Filter cell>      bool       Range        [0]                                                                                                                                                            .

If the cell accepts more values or value ranges to be filtered by.

 

If set to 1 for cell of type Int, Float and Date, it accepts more values separated by ‘;’ and range of values separated by ‘~’.

It can be used only for filter operators 1 (Equal) and 2 (Not equal). Other operators will fail.

It will filter all values inside the range(s), including boundaries.

For example Equal 1;3~5;9~12;16 will find cells with values 1,3,4,5,9,10,11,12,16

 

If set to 1 for cell of string type like Text or Lines, it accepts more values separated by ‘;’.

It can be used only for filter operators: 1 (Equal), 2 (Not equal), 7 (Begins), 8 (Not begin), 9 (Ends), 10 (Not end), 11 (Contains), 12 (Not contain)

To filter by empty value, set the filter cell value to ‘;’.

 

If set to 1 for cell of type Enum or Radio, it accepts more value to be selected and to filter by.

To filter by empty value, set the filter cell value to ‘;’.

 

If set to 1, calendar component for selecting date supports selecting more dates or range of dates by mouse drag. The date should not contain time part.

If set to 1, Defaults dialog supports selecting more values for any cell type (not only Int, Float, Date, Text, Lines).

 

The characters ‘;’ and ‘~’ can be changed by <Lang><Format ValueSeparator=’;’ RangeSeparator=’~’/></Lange>

 

 

Filter actions and API

 

 

<Actions>                      FilterOn                 Attached to event OnClickPanelFiltered                                                                                .

Enables filtering in grid and re-filters it according to actual settings. It fails if filtering already enabled.

 

<Actions>                      FilterOff                 Attached to event OnClickPanelFiltered                                                                                .

Disables filtering in grid and un-filters it. It does not clear actual filter settings. It fails if filtering already disabled.

 

<Actions>                      ShowFilterMenu                   Attached to event OnClickSideFilter                                                           .

Shows filter operator menu

 

API event          bool       OnFilter    (TGrid grid, int type)                                                                                                                chg 7.0 .

Called when grid will be re-filtered. You can provide own filtering and return true.

type when it is called (0 on filter change, 1 on start, 2 (since 7.0) on grouping change)

(Since 7.0) It is called for every filtering in grid (<Filter>, <Search>, SetFilter).

 

API event          void       OnFilterFinish            (TGrid grid, int type)                                                                                       chg 7.0 .

Called after grid was re-filtered. Called only for filtering on client.

(Since 7.0) type when it is called (0 on filter change, 1 on start, 2 on grouping change)

(Since 7.0) It is called for every filtering in grid (<Filter>, <Search>, SetFilter).

 

API event          bool       OnRowFilter    (TGrid grid, TRow row, bool show)                                                                                        .

Called for custom condition while filtering.

Called when filtering the row, show is the actual filter result.

Must return if the row will be visible or not.

 

API method      void       ChangeFilter   (string cols, string values, string operators, bool nofilter, bool noclear, TRow filter)     upd 7.0 .           

Filters grid according to given columns.

cols, values and operators are comma separated values or JavaScript arrays. All must have the same length.

cols are column names, values are values for given filter cells and operators are filter operator numbers 0 - 12, see cell attribute Filter.

If nofilter is 1, does change the filter cells but does not filter grid.

If noclear is 1, does not clear the other filter cells that are not listed in cols.

(since 7.0) filter can be the filter row to change the filter settings here, if the grid contains more filter rows. If not set, uses the first filter row.

 

API method      void       DoFilter    (TRow row = null, string col = null)                                                                                                       .

Applies all filters (in Filter rows) to the grid.

If set row and col updates operator for this cell in Filter row.

 

API method      void       FilterTextRange        (string col, string val, string name, int show = 2)                                         upd 6.6 .

Creates new filter and applies it to grid. Shows all rows containing any item from range val in their range value in column col.

name(new 6.6), name of the filter to have more different filters, if row violates any filter, it is hidden.

show = 0 – set the filter only, but does not filter, 1 – filters by the filter, but does not show the changes, 2 – filter and show the changes.

 

API method      void       FilterDateRange       (string col, string val, string name, int show = 2)                                         new 6.6.

Creates new filter and applies it to grid. Shows all rows containing any item from date range val in their range value in column col.

The val can be for example “1/1/2010~12/31/2010 23:59:59” to show all dates from the year 2010

name – name of the filter to have more different filters, if row violates any filter, it is hidden.

show = 0 – set the filter only, but does not filter, 1 – filters by the filter, but does not show the changes, 2 – filter and show the changes.

 

API method      void       SetFilter   (string name, string filter, string col, int show = 2)                                                  new 6.6;upd 6.7.

Creates new filter and applies it to grid.

name – name of the filter to have more different filters, if row violates any filter, it is hidden.

filter – filter formula string, similar to cell Formula, it can use variables Grid as the grid and Row as the filtered row and also column names as values of the filtered row cells.

The filter code must return 0 to hide the row and 1 to show the row.

show = 0 – set the filter only, but does not filter, 1 – filters by the filter, but does not show the changes, 2 – filter and show the changes.

if set col, it tests row’s CanFilter attribute and the cell’s CanFilter attribute when the column has CanFilter=’2’

To clear the filter pass empty filter parameter.

(new 6.7) The custom filters can be saved to cookies when set SaveFilters = 1.