Data Grid Search
Boolean AND OR NOT Columns Specific columns Column name contains space Exclude per column Checkbox columns Numbers Inclusive range Exclusive range Dates Between 2 dates Wildcards ? * Special characters
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.
→ Returns rows where both values, 'Paris' and 'Berlin', are found.
Note:
+ or && can also be used instead of AND
+ 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'.
→ Returns rows where a value is either 'Paris' or 'Berlin'.
Note:
|| can also be used instead of OR
|| can also be used instead of OR
NOT <searchTerm>
Exclude from search, e.g:
NOT London
→ Returns rows where 'London' is not a value.
→ Returns rows where 'London' is not a value.
Paris NOT London
→ Returns rows that contain 'Paris', but excludes rows that contain 'London'.
→ Returns rows that contain 'Paris', but excludes rows that contain 'London'.
Note:
- or ! can also be used instead of NOT
- 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'.
→ 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'.
→ 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.
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'.
→ 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.
→ 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.
→ 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.
→ 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.
→ 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}
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'.
→ 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'.
→ 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'.
→ 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'
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'
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.
Excludes rows that contain at least 'Berlin' or 'London', or both.
NOT (Berlin AND London)
Excludes rows that contain both terms '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'
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
Excludes rows that contain both the term 'Berlin' anywhere and 'London' in the 'City' field