EJS TreeGrid documentation
Filtering rows
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] upd 9.0.
Filter behavior in tree
0 (Hide all not found) The filter is applied to parents before children to speed up filtering. The filter is applied to children only if the parent fulfills the condition.
1 (Hide all not found, iterate all) 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 than 0.
2 (Show all found) It shows all rows that fulfill the filter condition in tree. It shows all parent rows if some their children fulfill the condition.
The filter is applied to children before filtering parent.
3 (new 9.0) (Show all found, with all children) It shows all rows that fulfill the filter condition in tree, with all their children.
The filter is applied to parent before children. The filter is applied to children only if the parent does not fulfill the condition.
<Cfg> bool FilterHidden [1] new 8.1.
0 - it will clear filter for column hidden from columns menu by users
1 - it will preserve the filter also after the column is hidden
<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.
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 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 EqualFormatted [0] new 9.3.
If set to 1, it equals (Filter = 1,2,3,4,5,6) numbers as formatted strings (according to the Format setting). To avoid problems with rounded values due format.
<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> string FilterMenu [...] new 9.0.
Full JSON definition of the filter menu with operators.
It can define only menu attributes without Items (e.g. MaxHeight), in this case the Items will be created for standard filter operators.
Or it can define the menu items in Items array. The items named 0 to 12 will be set as standard filter operators, other items will be custom.
It supports extended capabilities like submenus and collapsible sections.
The menu format is standard TreeGrid menu.
For example this filter menu for column "Col1" shows operators in two columns:
<Filter ... Col1FilterMenu="{ Items:[ { Name:0 }, { Columns:2,Items:[1,2,3,4,5,6,7,8,9,10,11,12] } ] }" ... />
<Filter cell> int Range [0] upd 9.1 .
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 2 (new 9.1) it compares all values in range cells for operators 1 (equals at least one) and 2 (equals none) .
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 2 (new 9.1) it compares all values in range cells for operators 1 (equals at least one) and 2 (equals none). Only for string enums, not index enums.
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>
<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.