Data Grid Search

Important

  • Operators (AND OR NOT TO) must always be capitalized.

  • Spaces in names or search terms must be escaped with \

  • Searches are not case-sensitive.

  • Brackets () group terms together.

Boolean

Search for a specific combination of values or exclude specific values from your search. Boolean operators include: AND OR NOT

<searchTerm> AND <searchTerm>

Search for both, e.g:

Paris AND Berlin → Returns rows where both values, 'Paris' and 'Berlin', are found.

Note: + or && can also be used instead of AND

<searchTerm> OR <searchTerm>

Search for either, e.g:

Paris OR Berlin → Returns rows where a value is either 'Paris' or 'Berlin'.

Note: || can also be used instead of OR

NOT <searchTerm>

Exclude from search, e.g:

NOT London → Returns rows where 'London' is not a value.

Paris NOT London → Returns rows that contain 'Paris', but excludes rows that contain 'London'.

Note: - or ! can also be used instead of NOT

Columns

Search for specific column (field) values.

<columnName>:<searchTerm>

Specific columns, e.g:

City:London → Returns rows where the City field contains 'London'.

Column\ with\spaces:<searchTerm>

Where a column name contains a space, do not replace or delete the space, but escape the space with '\', e.g:

Capital\ City:Berlin → Returns rows where the 'Capital City' field contains 'Berlin'.

Note

: Deleting a space or altering the field name in your search (e.g. making it 'CapitalCity' instead of 'Capital\ City') means that the field that you are attempting to search does not actually exist. The result would be an error or incorrect search results.

<columnName>:(NOT <searchTerm>)

Exclude per column, e.g:

City:(NOT London) → Excludes rows where the City field contains 'London'.

<columnName>:<yes/no>

Checkbox columns, e.g:

CapitalCity:Yes → Returns rows where the value of the CapitalCity field is true, i.e. the city is a capital city.

Numbers

Search for specific numbers or number ranges.

[10 TO 20]

Between 10 and 20 (inclusive), e.g:

ID:[10 TO 20] → Returns rows that contain IDs 10 through 20.

{10 TO 20}

Between 10 and 20 (exclusive), e.g:

ID:{10 TO 20} → Excludes rows that contain IDs 10 and 20, but returns rows that contain IDs 11 through 19.

Dates

Search for specific dates or date ranges.

{2017/03/01 TO 2017/03/15}

Between two dates (inclusive), e.g:

DateOfBirth:{2017/03/01 TO 2017/03/15} → Returns rows that contain dates in DateOfBirth column 2017/03/02 through 2017/03/14.

Note

: Depending on your configuration, dates can be formatted using different seperators, including dashes (-), e.g. DateOfBirth:{2017-03-01 TO 2017-03-15}

Wildcards

Using wildcards in a search.

?

Single character replacements, e.g:

Par?s → Returns rows that contain values that start with 'Par', followed by any letter, then followed by 's', e.g. 'Paris' and 'Parys'.

*

Multiple character replacements, e.g:

Lo*on → Returns rows that contain values that start with 'Lo', followed by any number of any letters, then followed by 'on', e.g. 'London' and 'Loyalton'.

Special characters

Use '\' to escape spaces and special characters contained in a search term. Special characters are: + - && || ! ( ) { } [ ] ^ " ~ * ? : \

\

Escape a special character or space, e.g:

Paris\ \+\ Berlin → Returns rows that contain the value 'Paris + Berlin'.


Advanced Search Examples

Some advanced search examples for the datagrid:

Paris NOT London Returns rows that contain 'Paris', but excludes rows that contain 'London'

NOT Berlin AND NOT London Excludes rows that contain 'Berlin' and excludes rows that contain 'London'

NOT (Berlin OR London) Excludes rows that contain at least 'Berlin' or 'London', or both.

NOT (Berlin AND London) Excludes rows that contain both terms 'Berlin' and 'London'

Continent\ Name:Europe AND (NOT Capital:London) Returns rows where the 'Continent Name' field is 'Europe', and the 'Capital' field is not 'London'

NOT (Berlin AND City:London) Excludes rows that contain both the term 'Berlin' anywhere and 'London' in the 'City' field

Last updated