Search and advanced filter

TreeGrid documentation

Search provides advanced searching and filtering in grid cells and rows.
It can provide Search like Google or Search by JavaScript expression, see <Cfg SearchMethod/>
The found results can be filtered, selected, colored or focused, see <Cfg SearchAction/>
The search can be case sensitive or insensitive, see <Cfg SearchCaseSenstive/>.
The search can be run according to individual cells or whole rows, see <Cfg SearchCells/>.

Search criteria can be changed by a user in special Search row.
Search criteria can be preset in XML by <Cfg SearchExpression SearchAction/>
Search can be run by API method SearchRows If searching in grid is permitted.
If searching is enabled by user.
A user can enable / disable searching by click to Search row left panel.
Expression to search or advanced filter expression.
The expression syntax depends on SearchMethod used. By default is the method chosen automatically according to expression syntax, see SearchMethod.
1 - Search like Google - expression has similar syntax to searching at www.google.com.

word word	words separated by space - finds all cells or rows containing all the words
"Phrase with spaces in double quotes"	quoted words are searched together as whole phrase
-word	word or phrase with hyphen prefix - finds all cells or rows without this word or sentence
word OR word	words or phrases joined by OR (uppercase only) - finds all cells or rows containing at least one of them
#	finds all empty cells

Example: London Paris OR Nice "above sea level"

2 - Search by expression – expression has similar syntax to TreeGrid formulas and JavaScript code, but it is not in TCalc content, so it cannot use TreeGrid calculation functions like sum().
The expression has the JavaScript syntax, operators (==, !=, <, <=, >, >=, &&, ||, !, parenthesis, function calls), if fact it can contain nearly every JavaScript code.
(Since 7.0) Due security reasons it is not possible to do function calls except methods of Math object and functions parseInt, parseFloat, escape, unescape, isFinite, isNaN.
Cell values from the row are referenced by column captions (not names!);
String constants can be in double or single quotes or can be without quotes at all.
To input dates in special format set it in SearchDateFormat.
For comparing equality can be used both '==' and '=' operators. For comparing inequality can be used both operators "!=" and "<>"
For operators "&&", "||" and "!" can be used word (case insensitive) equivalents, defined in Text.xml, in <Lang><Text And='' Or='' Not=''/></Lang>
Remember, the operator && / and have higher priority then || / or
Here can be used special string operators to check if one string contains another string, starts with the string, ends with the string.
Example: (Name starts James or Name = John) and Age >20 and Age <40

3 - Exact string search - (new 6.3) expression has no special syntax, the string is searched for as is without any parsing.
The search is done also in number types as string search.
Name of search action predefined or currently applied in grid, can be one of Filter, Select, Mark, Find, FindPrev or empty. (Since 7.0) It can contain also more actions separated by comma or semicolon to do all these action in this order, e.g. "Filter,Mark" to show only found rows and mark the found cells.

Filter	Hides all rows that don't match the SearchExpression in method and other search options set.
	The filter works in the same way as filter by <Filter>.
	You can control the filter also by filter attribute like <Cfg StandardFilter /> and <I CanFilter />.
	If row cannot be searched through or filtered by its settings, it is not hidden.
Select	Selects all rows that match the SearchExpression in method and other search options set.
	Selects only whole rows, cannot select only cells.
	If row cannot be searched through it is not selected.
	Which rows will be iterated is affected also by ClearSelected.
Mark	Colors all rows or cells (see SearchCells) that match the SearchExpression in method and other search options set.
	The rows or cells are colored by the CSS Found colors.
	After change some search setting like SearchExpression and do Mark action again, new color for coloring is used (Found1->Found2->...)
	The color is restored to Found1 after any other search action is done (Filter,Select,Find,FindPrev,Clear)
	If row or cell cannot be searched through it is not colored.
Find	Focuses the first row or cell that matches the SearchExpression in method and other search options set.
	By default searches from the next row / cell from actually focused (like Find next), for other possibilities see SearchFocused.
	If row or cell cannot be searched through, it is not focused.
FindPrev	(new 12.0) Focuses the last row or cell that matches the SearchExpression in method and other search options set.
	By default searches from the previous row / cell from actually focused (like Find previous), for other possibilities see SearchFocused.
	If row or cell cannot be searched through, it is not focused.

Method of the search
0 - Automatic method selection - automatically chooses 1 or 2 method according to used expression 1 - Search like Google
2 - Search by expression (since 7.0 it removes all function calls to be safe)
3 - Exact string search (new 6.3). It is not automatically chosen by Method=0.
4 - Search by expression unsafe (new 7.0). Like 2, but does not remove function calls and doesn't add quotes for strings.
Used only for SearchMethod = 1 (Search like Google) and 3 (Exact search).

0	searches in rows, thus all the cells in one row together must fulfill the SearchExpression.

For example if you search "London 1000" at least one cell in row must contain "London" and other (or the same) the "1000".
For Filter or Select action the row is filtered (visible) or selected if it fulfills the SearchExpression.
For Mark action the whole row is colored.
For Find or FindPrev action the whole found row is focused (does not change focused column), for next match it continues in next row.

1	searches in cells, thus every cell to find must fulfill the SearchExpression.

For example if you search "London 1000" the found cell must contain both "London" and "1000".
For Filter or Select action the row is filtered (visible) or selected if at least one cell is found.
For Mark action only the found cell is colored.
For Find or FindPrev action focuses the first cell found, for next match continues in the same row in next cell.
If strings are compared case sensitive.
List of characters to ignore when searching.
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.
It changes characters in comparing strings for searching.
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"
How numbers are compared (cell types Int and Float). Used only for SearchMethod = 1 (Search like Google) and 3 (Exact search).
Default value is 1 for SearchMethod = 1 and 0 for SearchMethod = 3.
0 - as strings, e.g. -1.7 is found by -1.7 or 1.7 or -1 or 1or 7 and so on, similar to SearchMethod=3
1 - exactly, e.g. -1.7 is found only by -1.7
2 - rounded, e.g. -1.7 is found by -1.7 or -2
3 - floor, e.g. -1.7 is found by -1.7 or -1 or 1 (but 1.7 is not found by -1 or 1.6)
Comma or semicolon separated list of default row names. Only row with the Def attribute that is listed will be searched through.
The row with not listed Def will be: a) for Filter action never hidden, b) for other actions never found / colored / selected
If SearchDefs is empty, all rows will be searched through.
Columns names (not captions!), comma or semicolon separated.
Only cells in given columns will be searched through. Used only for SearchMethod = 1 (Search like Google) and 3 (Exact search).
If SearchCols is empty, all cells will be searched through.
Result depends also on SearchHidden. If column has CanSearch='0', it cannot be searched through at all.
For 1 and any action it searches also in hidden columns. Used only for SearchMethod = 1 (Search like Google) and 3 (Exact search). For 1 and action Select or Mark it expands parents of the found row to be visible in tree.
Only for Find and FindPrev actions 0 - Searches from beginning
1 - Searches from focused cell, when reaches end, ends
2 - Searches from focused cell, when reaches end, continues from beginning
3 - Searches from focused cell, when reaches end, it asks user to continue from beginning
If set and action is Find or FindPrev, alerts message "Not found" if no result is found.
If set to 1, it alerts the message by standard alert.
If set to 2, it alerts the message by HTML dialog with ok button
If set to >=10, it alerts the message by HTML dialog for given count of milliseconds.
If set and action is Filter, Select or Mark, alerts message with count of row / cells found.
If set to 1, it alerts the message by standard alert.
If set to 2, it alerts the message by HTML dialog with ok button.
If set to >=10, it alerts the message by HTML dialog for given count of milliseconds.
You can use also SearchCount variable to display the found count in some cell formula.
If set to 1, found cells (Mark action) can be highlighted also by CSS class attributes like color or font, defined in GxColorFoundX CSS class in Grid.css.
If set to 0, found cells are highlighted only by background color.
The value 1 can slow down selecting cells.
How many different Mark colors are permitted in grid.
Set it to 1 to clear mark for every new search.
Format to convert strings to date when used in advanced filtering (SearchMethod=2).
Comma separated strings as possible names of the column
Used in SearchExpression to reference this column instead of its caption, one column can have more names to be referenced by.
Used only for SearchMethod = 2 (Search by expression).
If values are searched within this column. Used only for SearchMethod = 1 (Search like Google) and 3 (Exact search).
The columns can be also restricted by SearchCols and SearchHidden.
Used only for SearchMethod = 2 (Search by Expression).
For 0 compares the cell raw value, for 1 compares the cell formatted string (for Enum compares with values from Enum array).
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.
Count of found rows for action Select.
Count of marked cells or rows for action Mark.
For action Filter it is empty string, to get the result use FilterCount variable.
For no action or actions Find / FindPrev it is null.

User interface to choose search

User interface for search is shown by solid space <Search> row. <Solid><Search ... /></Solid>
This row is not divided to any section and is never scrolled. It can contain any cells like Space row, but the cells named as listed below have special functions.
To more customize searching and advanced filters in grid you can define all the user interface by Space row cells instead of Search. See this tutorial.
It can be placed in <Solid> tag. The position in grid is set by its Space attribute.
The Search row can have these predefined cells, the cells can have standard cell and space row cell attributes, like Width or Tip.
Expression The expression to search for or advanced filter formula, standard input cell type Text. Its Action attribute can have special values.
Filter, Select, Mark, Find, FindPrev (new 12.0), Clear, Help Buttons to do standard search action. Clear restores original state, Help shows help message.

Actions	Select box with standard search actions to select one that will be run by Search button or after Expression changes and its action is set to Actions.
	Its Action attribute can have special value "Refresh" to repeat search in grid after another value is selected.
Search	Button to do search action selected by Actions combo, if not defined the cell Actions, just by Actions attribute.
	Since 7.0 it can be used also to define more actions for Actions at once, like Actions="Filter,Mark" to filter the rows and mark the found cells.
List	Select box with predefined values for SearchExpression. To let user just select predefined filter or search instead of building his own.
	You have to set the cell Expressions and Defaults attributes.
Cols	Select box to select which columns will be searched through. If not present, all columns are searched.
	You have to set Cols and Defaults attributes and optionally Label attribute.
Defs	Select box to select which rows (according to their Def attribute) will be searched through. If not present, all rows are searched.
	You have to set Cols and Defaults attributes and optionally Label attribute.
Case	Check box to choose case sensitive (1) or case insensitive (0) search. Sets SearchCaseSensitive. Has optional attribute Label.
Type	Check box to choose to search in rows (0) or in individual cells. Sets SearchCells. Has optional attribute Label.

Search action done after the Expression cell value changed, can be Filter, Select, Mark, Find, FindPrev, Actions, Refresh or Last.
Use Actions value if cell Actions is included in Cells. Use Last to run the last action previously done. Use Refresh to redo the actual action.
Use empty string to not do any action.
Default value is Filter if no button and Actions cell is defined or Actions or the first button's action.
Search action done after the Actions select box is changed, can be Refresh or empty string.
Use Refresh - repeats search with the selected action.
The default value is Refresh if no Search cell is defined, otherwise empty string.
First character separated array of item names to display in List cell. The same as button type Defaults list.
First character separated array of predefined expressions that will be assigned to SearchExpression after specific item in ListDefaults will be selected.
Search action done after the list item is chosen, can be Filter, Select, Mark, Find, FindPrev, Actions, Refresh or Last.
Use Actions value if cell Actions is included in Cells. Use Last to run the last action previously done. Use Refresh to redo the actual action.
Use empty string to not do any action.
First character separated array of item names to display in Cols cell.
First character separated array of column names that will be assigned to SearchCols after specific item in ColsDefaults will be selected.
It should contain actual SearchCols value to provide correct formula.
First character separated array of item names to display in Defs cell.
First character separated array of default row names that will be assigned to SearchDefs after specific item in DefsDefaults will be selected.
Position of the row in grid, specifies horizontal section of the grid.
If more solid rows has the same Space value, are placed in the order they are in data
-1 - above grid, 0 - above header, 1 - under head rows, 2 - above foot rows, 3 - under foot rows, 4 - under vertical scrollbar, 5 - under grid
-1, 0, 4, 5 are spanned for whole grid, including vertical scrollbar and pager, 1, 2, 3 are spanned only for columns.
If the row displays left side panel, to show icon for enabling / disabling searching.
If set to 1, the panel is displayed only if the grid left side panel is displayed.
If set to 2, the panel is displayed always.
HTML label displayed in front of the cell. For example ColsLabel='Select columns:' adds the label in front of the cell Cols.
Left indent of any cell, in pixels.

Search actions and API

Enables searching in grid and re-searches it according to actual settings. It fails if searching already enabled.
Disables searching in grid and clear search. It does not clear actual search settings. It fails if searching already disabled.
Called when searching in grid starts after user action. Return true to suppress default searching.
Action can be Filter, Select, Mark, Find, FindPrev, Clear, Help, Refresh.
Called after search in grid finished, all changes are already visible.
Called when searching in row.
col is column cell when is searching if set searching in cells - SearchCells = 1, otherwise it is null
found is the search result.
It must return new found value. 1 - found, 0 - not found, -1 not applicable (it will not be selected but also not be hidden by filter)
If set col (searching in cells), the OnRowSearch can also return column name as the cell found.

func is caller function for searching row that resulted found. it has prototype func (TRow row, string col, type userdata)
func returns -1 if cannot search, 0 if not found or 1 if found when searching in row or column name if found when searching in cells
The func and userdata parameters can be used for searching in other rows than the actual. Remember from the func is called the OnRowSearch event
userdata is null in default call, use it to mark custom calling to avoid recursion cycle.
Example using func and userdata:

Grids.OnRowSearch = function (G,row,col,found,F,type){

// This function searches in children to mark/unmark parent row

Calls DoSearch asynchronously, for server paging it reloads grid.
Runs specified searching action in grid.
action can be standard search action "Filter", "Select", "Mark", "Find", "FindPrev" or can be
"Refresh" - runs actual action (SearchAction) again. "Clear" - clears actual action and restores original state. "Help" - shows help