Queries: Advanced search

In the tab Filter you can specify in greater detail the conditions for the objects you are looking for by selecting Advanced. For users to be able to make use of advanced search, this should be allowed in the respective user group settings.

The conditions are represented as a tree of nodes. A line can contain a boolean operator or a filter condition. The first item in the line is a drop-down menu, listing the options ‘and', ‘or' and ‘not'. These are called 'boolean operators'. You'll need these when your query has more than one condition. The boolean operator indicates how to interpret the various conditions.

Selecting one of these options makes the other items of that line disappear. You have created a boolean constraint. By clicking the first button 'insert item', a condition can be added to the boolean operator. When the new line appears, it will again contain the standard items.

The second item is used for field selection. When you click on it a list will be shown with the fields that can be used to define your conditions. Selecting one will make the first item disappear.

The third item contains a drop-down menu with the operators like ‘is', ‘is not', ‘>', ‘=>', ‘<', ‘<=', ‘like', ‘is not null' and ‘is null'. When a field has been selected, only the relevant options are shown. What options are relevant depends on the data type of the field. When an operator is chosen, either a drop-down menu or entry field is shown. In the first case, one of the options must be chosen. If this field does not show a drop-down menu a value must be filled in. Note that if more words are filled in, these, including the spaces, will be considered as one value.

In the example above you have created a constraint that will be true when both condition A (first name starts with "A") and condition B (birthday greater than 1 January 1970) are true.

You can manipulate the structure of your tree with the following buttons:

The ‘Insert before' and ‘Insert after' buttons produce lines that are siblings of the current line.
The ‘Insert child' button generates a new line, being a child of the currently selected one.
Clicking the ‘Delete' button deletes the current line.
Clicking: 'move current item to clipboard' deletes the node and moves it to the clipboard
Clicking: 'copy item to clipboard' keeps the node and copies it to the clipboard
Subsequently the node in the clipboard can be pasted into a new node with the 'paste' button

With these options you are able to define and run complex queries on your database. However, to do this properly and create queries that represent your intention correctly, be aware that you need a basic understanding of your data model.

More complex queries

Above we have seen a simple example with just one boolean operator "AND". In many cases however, you'll need to mix various boolean operator. Suppose we want to find everyone in our database born after 1 January 1970 whose name starts with the letter "A" or the letter "B". We could write this as:

A) Born after 1-1-1970 (write this as: 19700101)

B) Name starts with "A"

C) Name starts with "B"

The full constraint would be: A and B or C. However this is not enough, we need to define how the various conditions are grouped together. A different grouping will pose a totally different question and will find different results. In this case there are two possibilities:

A and (B or C)
(A and B) or C

The first question is the one we want, the second one will return all persons born after 1 January 1970 whose name start with the letter "A" plus anyone whose name start with "B". To express this in the constraint tree you'll need to resolve this ambiguity.

We'll use the first question "A and (B or C)" as our example. Any search that involves more than one "constraint" needs boolean operators to group the various constraints. We'll have to start with the outermost constraint in this case the "and". Next we add the left side.

and
... constraint A

Now we need to add the right side. The right side also has more than one constraint and therefore a boolean operator. Again, start with the boolean operator and attach the various constraint. The end result will look like this.

and
... constraint A
... or
... constraint B
... constraint C

Tip: When using dates in a query, it is possible to get the current date by entering now(), or for example the date of exactly a year ago by entering now() - 365.