TreeGrid v6.0

Search and advanced filter

TreeGrid documentation index

 

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.

 

<Cfg>                bool       Searching           [1]                                                                                                                                               .

If searching in grid is permitted.

 

<Cfg>                bool       Searched             [1]                       Saved to cookies, to not load it, set SearchedLap=’1’                                      .

If searching is enabled by user.

A user can enable / disable searching by click to Search row left panel.

 

<Cfg>                string    SearchExpression              Saved to cookies, to not load it, set SearchLap=’1’               upd 7.0 .

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!);

these names can be predefined by column SearchNames attribute, especially if the column caption contains spaces.

             The column captions are referenced case insensitive.

String constants can be in double or single quotes or can be without quotes at all.

Not quoted strings must not interfere with column captions and other reserved words

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>

By default can be used instead of “&&” => “and”, “||” => “or”,  “!” => “not

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.

The operator names are defined in Text.xml, in <Lang><Text Starts=’’ Ends=’’ Contains=’’/></Lang>

By default is Contains="contains,has" Starts="starts,starts with,starts by,begins,begins with,begins by"  Ends="ends,ends by, ends with"

Example: (Name starts James or Name = John) and Age >20 and Age <40

 

3Exact 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.

 

<Cfg>                string SearchAction                               Saved to cookies, to not load it, set SearchLap=’1’               upd 7.0 .

Name of search action predefined or currently applied in grid, can be one of Filter, Select, Mark, Find 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.

 

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,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 / cell from actually focused (like Find next), for other possibilities see SearchFocused.

             If row or cell cannot be searched through it is not focused.

 

<Cfg>                int          SearchMethod            [0]         Saved to cookies, to not load it, set SearchLap=’1’               upd 7.0 .

Method of the search

0 – Automatic method selection – automatically chooses 1 or 2 method according to used expression

2 (expression search) is chosen if the expression contains one of these characters ( ) = ! < >

             or if the expression contains at least two column names or one column name and one keyword of these: contains, ends, starts

             otherwise is chosen 1 (Search like Google)

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.

 

<Cfg>                bool       SearchCells                   [0]         Saved to cookies, to not load it, set SearchLap=’1’      renamed 6.0  .

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 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 action focuses the first cell found, for next match continues in the same row in next cell.

 

<Cfg>                bool       SearchCaseSensitive      [0]    Saved to cookies, to not load it, set SearchLap=’1’        renamed 6.0             .

If strings are compared case sensitive.

 

<Cfg>                string    SearchWhiteChars                                                                                                                        new 7.0 .

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.

 

<Cfg>                string[*] SearchCharCodes                                                                                                                         new 7.0 .

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 ‘,’

– 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”

 

<Cfg>                int          SearchNumbers        [1/0]                                                                                                                new 7.0 .

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)

 

<Cfg>                string[]  SearchDefs                                  Saved to cookies, to not load it, set SearchLap=’1’                             .

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.

 

<Cfg>                string[]  SearchCols                                  Saved to cookies, to not load it, set SearchLap=’1’                             .

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.

 

<Cfg>                bool       SearchHidden             [0]                                                                                                          renamed 6.0  .

For 1 and any action it searches also in hidden columns. Used only for SearchMethod = 1 (Search like Google) and 3 (Exact search).

 

<Cfg>                bool       SearchExpand            [1]                                                                                                          renamed 6.0  .

For 1 and action Select or Mark it expands parents of the found row to be visible in tree.

 

<Cfg>                int          SearchFocused         [3]                                                                                                          renamed 6.0  .

Only for Find action

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

 

<Cfg>                int          SearchNotFound     [500]                                                                                         renamed 6.0; upd 7.0     .

If set and action is Find, 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.

 

<Cfg>                int          SearchAlertFound                                                                                                                         new 7.0 .

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.

 

<Cfg>                bool       SearchClass    [0]                                                                                                                                 new 6.0 .

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.

 

<Cfg>                int          SearchMaxMark        [3]                                                                                                                    new 7.0 .

How many different Mark colors are permitted in grid.

Set it to 1 to clear mark for every new search.

 

<Cfg>                string    SearchDateFormat                                                                                                                                      .

Format to convert strings to date when used in advanced filtering (SearchMethod=2).

 

<C>                   string[]  SearchNames                                                                                                                                                   .

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).

 

<C>                   int          CanSearch         [1]                                                                                                                                               .

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.

 

<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          SearchCount                                                                                                                                       new 7.0 .

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 action Find 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.

 

<Search>          string [] Cells             [“Expression”]                                                                                                                            upd 7.0 .

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, 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 Defs 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>          string    ExpressionAction [“Filter”/...]                                                                                                                    .

Search action done after the Expression cell value changed, can be Filter, Select, Mark, Find, 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>          string    ActionsAction            [“Refresh” / “"]                                                                                                              .

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.

 

<Search>          string[*] ListDefaults                                                                                                                                                        .

First character separated array of item names to display in List cell. The same as button type Defaults list.

 

<Search>          string[*] ListExpressions                                                                                                                                            .

First character separated array of predefined expressions that will be assigned to SearchExpression after specific item in ListDefaults will be selected.

 

<Search>          string    ListAction                                                                                                                                                             .

Search action done after the list item is chosen, can be Filter, Select, Mark, Find, 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.

 

<Search>          string[*] ColsDefaults                                                                                                                                                    .

First character separated array of item names to display in Cols cell.

 

<Search>          string[*] ColsCols                                                                                                                                                                .

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.

 

<Search>          string[*] DefsDefaults                                                                                                                                         .

First character separated array of item names to display in Defs cell.

 

<Search>          string[*] DefsDefs                                                                                                                                                                 .

First character separated array of default row names that will be assigned to SearchDefs after specific item in DefsDefaults will be selected.

 

<Search>          int          Space         [1]                                                                                                                                                           .

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.

 

<Search>          int          Panel           [1]                                                                                                                                                           .

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.

 

<Search cell>   string    Label                                                                                                                                                                          .

HTML label displayed in front of the cell. For example ColsLabel=’Select columns:’ adds the label in front of the cell Cols.

 

<Search cell>   int          Left                                                                                                                                                                               .

Left indent of any cell, in pixels.

 

 

Search actions and API

 

<Actions>                      SearchOn                         Attached to event OnClickPanelSearched                                                               .           

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

 

<Actions>                      SearchOff                         Attached to event OnClickPanelSearched                                                               .

Disables searching in grid and clear search. It does not clear actual search settings. It fails if searching already disabled.

 

API event          bool       OnSearch            (TGrid grid, string Action, bool Show)                                                                                   .

Called when searching in grid starts after user action. Return true to suppress default searching.

Action can be Filter, Select, Mark, Find, Clear, Help, Refresh.

 

API event          void       OnSearchFinish       (TGrid grid, string Action, bool Show)                                                                      .

Called after search in grid finished, all changes are already visible.

 

API event          int          OnRowSearch            (TGrid grid, TRow row, string col, bool found, function func, type userdata)      .

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

if(type) return found;                               // to avoid recursion cycle

if(!row.firstChild) return -1;                                                      // not applicable for children

for (var r=row.firstChild;r;r=r.nextSibling) if(F(r,col,1)>0) return 1;

// Searches in children, if at least one found, parent is marked as found

return 0; }

 

API method      void       SearchRows    (string action, bool noshow)                                                                                                   .

Calls DoSearch asynchronously, for server paging it reloads grid.

 

API method      void       DoSearch            (string action, bool noshow)                                                                                                   .

Runs specified searching action in grid.

action can be standard search action “Filter”, “Select”, “Mark”, “Find” or can be

Refresh” – runs actual action (SearchAction) again. ”Clear” – clears actual action and restores original state. “Help” – shows help