How to showing the ways to filter a listbox by using the column search fields to filter.
Table of Contents
Filters¶
What we call ListBox are the small input fields at the top of each column. They allow you to type text in order to filter the list.
The ListBox are very useful as soon as you know what you can write in them.
Strict Searching, Extended Searching and Numerical Searching¶
Depending on the type of column, Searching won't behave the same. It is due to the fact that you won't search a Date or Time the same way you will do for a Reference or a Description.
We should distinguish three different types of Searching: the range, the strict and the extended Searching.
The Range Searching¶
It's often assimilated with columns that contains only numerical value such as quantities, but it also applies to alphabetical values. Dates can be searched as numbers. In this case, we can use the Syntax that applies to Range Searching. You should also see the specific rules for Dates
The Strict Searching¶
Strict Searching applies for example to References. The default behavior is that the search box will take into account the exact term that you typed in. If you type "Jo", it won't find "John". It will search everything that is exactly "Jo".
Simple request and Multiple Request syntaxes apply to this kind of Searching.
The Extended Searching¶
Extended Searching applies for example to description field. By default, this kind of Searching will look everything that contains what you typed in.
ListBox Syntax¶
Please notice that ListBox can be case-sensitive.
Simple request
If you know exactly what you are looking for, you can just type the name in the corresponding ListBox. If you don't, or if the name is too long, you can use the "%" symbol Input
| Input |
Strict Searching |
Extended Searching
|
| John Doe |
Everything that is 'John Doe' |
Everything that contains 'John Doe' |
| John% |
Everything that begins with 'John' |
Everything that begins with 'John' |
| %Doe |
Everything that ends with 'Doe' |
Everything that ends with 'Doe' |
Please notice that everytime you will use a % symbol in your request, the Extended and Strict Searching will behave the same.
Multiple Request¶
You can ask for Multiple request using the 'AND' 'OR' 'NOT' operators. In order for them to be used as operators, they should be written in capital. Otherwise they will be considered as the searched text:
| Input |
Strict Searching |
Extended Searching |
| John OR Roger |
Everything that is 'John' or 'Roger' |
Everything that contains 'John' or 'Roger' |
| John% OR Ro% |
Everything that begins with 'John' or 'Ro' |
Everything that begins with 'John' or 'Ro' |
| %Doe OR Ro% |
Everything that ends with 'Doe' or begins with 'Ro' |
Everything that ends with 'Doe' or begins with 'Ro' |
| John% AND %ger |
Everything that simultaneously begins with 'John' and with 'ger' |
Everything that simultaneously begins with 'John' and ends with 'ger' |
| John AND Roger |
Not possible, can't be both 'John' and 'Roger' in the same time |
Everything that simultaneously contains 'John' and 'Roger' |
| %Doe AND Ro% |
Everything that simultaneously ends with 'Doe' and begins with 'Ro' |
Everything that simultaneously ends with 'Doe' and begins with 'Ro' |
| NOT John |
Everything that is not 'John' |
Everything that does not contains 'John' |
| Roger NOT John |
Everything that is Roger and not John |
Everything that contains 'Roger' and not 'John' |
| John Doe OR %Roger |
The system will consider the space between 'John' and 'Doe' as an AND operator
It will search Everything that is 'John' and 'Doe' or Everything that is 'John' and ends with 'Roger' |
Everything that contains 'John Doe' and not 'Roger' |
| "John Doe" OR %Roger |
Using quotes (") in complex expression, the system won't consider the space as an AND operator, but it will take into account the entire expression 'John Doe' :
It will Search everything that is 'John Doe' or ends with 'Roger'
|
Everything that contains 'John Doe' and not 'Roger' |
With those operators, you can enter more complex queries using parentheses. If you use many different operators in the same request, without any parentheses, the operator AND will have the highest priority. Here is few examples:
| Input |
Explicit equivalent |
| foo AND bar OR baz |
("foo" AND "bar") OR "baz" |
| foo OR bar AND baz OR fred |
"foo" OR ("bar" AND "baz") OR "fred" |
| (foo OR bar) AND baz OR fred |
(("foo" OR "bar") AND "baz") OR "fred" |
| John Doe OR %Roger |
"John" AND ("Doe" OR %Roger) |
Range Searching¶
If you have a column with numbers, you can filter them using the operators > < = and !=:
| Input |
Query |
| <10 |
Everything that is strictly inferior to 10 |
| >10 |
Everything that is strictly superior to 10 |
| =10 |
Everything that is equal to 10 |
| !=10 |
Everything that is not equal to 10 |
| >=10 |
Everything that is superior to 10 including 10 |
| <=10 |
Everything that is inferior to 10 including 10 |
It also can be used with alphabetical values, considering the alphabetical order:
| Input |
Query |
| <foo |
Everything that comes before foo in alphabetical order |
| >foo |
Everything that comes after foo in alphabetical order |
| =foo |
Everything that is foo |
| !=foo |
To set upEverything that is not foo |
| >=10 |
Everything that comes after foo, including foo |
| <=10 |
Everything that comes before foo, including foo |
Dates¶
You can filter dates with the same operators as the ones used for numbers. Depending of the request, the interval of date that will be displayed is different:
Without hours:
| 2001/01/01 |
From 2001/01/01 - 00:00:00 included |
To 2001/01/02 00:00:00 excluded |
| =2001/01/01 |
From 2001/01/01 - 00:00:00 included |
To 2001/01/02 00:00:00 excluded |
| !=2001/01/01 |
Before 2001/01/01 - 00:00:00 excluded |
After 2001/01/02 - 00:00:00 included |
| <2001/01/01 |
Before 2001/01/01 - 00:00:00 excluded |
|
| <=2001/01/01 |
|
Before 2001/01/02 - 00:00:00 excluded |
| >2001/01/01 |
|
After 2001/01/02 - 00:00:00 included |
| >=2001/01/01 |
After 2001/01/01 - 00:00:00 included |
|
With Hours:
| 2001/01/01 12 |
From 2001/01/01 - 12:00:00 included |
To 2001/01/02 12:00:00 excluded |
| =2001/01/01 12 |
From 2001/01/01 - 12:00:00 included |
To 2001/01/02 12:00:00 excluded |
| !=2001/01/01 12 |
Before 2001/01/01 - 12:00:00 excluded |
After 2001/01/02 - 12:00:00 included |
| <2001/01/01 12 |
Before 2001/01/01 - 12:00:00 excluded |
|
| <=2001/01/01 12 |
|
Before 2001/01/02 - 12:00:00 excluded |
| >2001/01/01 12 |
|
After 2001/01/02 - 12:00:00 included |
| >=2001/01/01 12 |
After 2001/01/01 - 12:00:00 included |
|
Related Articles¶
