Filtering rows

TreeGrid documentation

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.
If rows can be filtered.
Setting it to 0 disables all filtering functions, including Search and SetFilter filters.
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.
If and how will be hidden rows with all children filtered.
0 - never.
1 - always if set CanFilter=2 or 3.
2 - like 1 except leaf rows without any child.
3 - like 2 except rows that permit and fulfill actual filter condition.
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.
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.

If set to 1, the StandardFilter 1 and 3 hide the not found parents, but still show their found children.
To hide the parent without its children it sets the parent's Expanded value 2 or 3.
If set to 2 it behaves like 0, but always hides children of hidden parents (clears the Expanded value 2 and 3). Use to switch between FilterHideParents on and off dynamically.
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
If the empty cells will be ignored by some filter operators. Bit array.
The setting is not applied when comparing / searching empty string.

1. bit &1	Never shows empty numbers for operators <, <=, >, >=.
2. bit &2	Never shows empty strings for operators <, <=, >, >=.
3. bit &4	Never shows empty strings for operators not contain / not begin / not end
4. bit &8	Never shows empty numbers for operator not equal
5. bit &16	Never shows empty strings for operator not equal

Wildcard character to be replaced by one character when filtering by string operators (7-12).
For example it can be set to "?" and filter string can be "a??c" to find "abbc" or "axycd" but not "abcd" or "ac". Wildcard character to be replaced by none, one or more characters when filtering by string operators (7-12).
For example it can be set to "*" and filter string can be "a*c" to find "abbc" or "acd" or "abcd". If it colors the rows that fulfill the filter condition. The other visible rows that cannot be filtered are not colored.
0 - it does not color any row for the filter.
1 - it colors the first row that fulfills the filter in tree. In StandardFilter=3 it does not color the children of already found row.
2 - it colors all rows that fulfill the filter. In StandardFilter=3 it runs filter also for rows that will be shown as children. It can slow down this filter type.
Rows can be filtered by this column.
If set to 2, it is also tested cell's CanFilter attribute.
If the row can be filtered according to this column. Used only when the column has set CanFilter='2'.
See also filter cell FilterDef attribute.
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
If the row is displayed in grid.
The row Visible attribute can be changed also by Filter / Search and by deleting row, when deleted rows are hidden.
By API the Visible attribute can be changed by methods ShowRow / HideRow.
Collapsing / expanding children and rendering pages, child pages or child parts does not affect row Visible attribute.

The Visible attribute does not directly affect if the row is rendered in grid.
When row has Visible='0' on start, it is not rendered at all and it is rendered when it is shown for the first time.
When the already rendered row is hidden by Filter / Search or by HiddenRow, it has set display='none' and next showing just clears the CSS attribute.
The visible row can be also not rendered because of collapsed parent (in child paging) or not rendered page, child page or child part.
The Hidden attribute is set by manual hiding and showing rows.
If set Hidden='1' the row is not shown/hidden by any filter. 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.
Called to get cell value for filtering and searching.
val is predefined value, returns val or new value.
Since 16.2 string is set if comparing as string, otherwise comparing as numbers.
Count of visible variable rows after filter is applied.
It is null if the filter was not run.
If set to 1, saves to cookies the custom filters set by SetFilter method.
It does not affect the standard or search filters. If set, clears the filter cell value after chosen operator off (0).

Comparing strings

You can also specify these settings for whole grid in default column <Def><D Name='C'/></Def>.
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.
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.
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.
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 ','
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.
Predefined or actual filter value to filter the column by.
It is used only if Filter is set to value > 0.
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

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.
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.
Menu text for FilterOff item. If not set, the FilterOff value is used.
Comma separated list of row default names the filter will be applied to. If not set, the filter is applied to all rows.
If set, the rows their default is not listed in the FilterDef automatically fulfill the filter condition.
It is used also for SetFilter, it is read from the first filter row in grid.
See also cell CanFilter attribute.
Comma separated list with predefined row default names available for FilterDef.
If set, the filter operator menu will contain also this list to let users to choose the rows to be the filter applied to.
If set to only one item (any), the filter operator menu will contain the FilterDef values only as read only item, just show where the filter will be applied to.
Item caption can be set for individual defaults by FilterMenuName.
Item caption used in filter menu for given default row. If not set, the default Name is used.
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.
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.
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.
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"
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:
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='~'/></Lang>
Called before change of filter operator (Filter attribute value) and / or FilterDef attribute value in given cell.
Return true to cancel the change.

Filter actions and API

Enables filtering in grid and re-filters it according to actual settings. It fails if filtering already enabled.
Disables filtering in grid and un-filters it. It does not clear actual filter settings. It fails if filtering already disabled.
Shows filter operator menu in actual or focused cell.
Fails if the cell has not set Icon or Button = "Filter".
Shows filter operator menu in actual or focused cell.
It shows the menu also if the operator icon is hidden by ShowMenu = 0.
Fails if the cell is not in Filter row.
Sets the actual or focused cell value to the filter cell in the same column and filters by it using DefaultFilter.
Sets the actual or focused cell value to the filter cell in the same column and shows operator menu.
Sets the actual or focused cell value to the filter cell in the same column and shows operator menu.
It shows the menu also if the operator icon is hidden by ShowMenu = 0.
Clears the filter operator for the actual or focused column.
Clears all the filter operators in the first filter row.
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).
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).
Called for custom condition while filtering.
Called when filtering the row, show is the actual filter result. Return new or the same value show.
The show and the returned result can be:
0 - the row does not fulfill the filter condition and will be hidden.
1 - the row fulfills the filter condition and will be shown.
2 - the row is not filtered and will be shown.
Prior 14.0 the code did not distinguish between 1 and 2.
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.
Applies all filters (in Filter rows) to the grid.
If set row and col updates operator for this cell in Filter row.
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.
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.
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.
For spec = 0 returns all actual filters in all Filter rows in array [ [col1,value1,operator1], [col2,value2,operator2], ... ]
For spec = 1 returns all custom filters set by SetFilter method in array [ [name1,filter1,col1, [name2,filter2,col2], ... ]
Returns bit array with information which filter is active. Returns:
1. bit (&1) for filter by Filter row.
2. bit (&2) for filter by SearchAction "Filter".
3. bit (&4) for filter by custom filter by SetFilter.
4. bit (&8) for filter by OnRowFilter API event. It returns it just when the OnRowFilter is defined!