Advanced Query Design

UNDER REVIEW

Aegis CRM’s Query engine allows the user to create complex queries, with multiple layers of criteria. To help you better understand exactly what is happening as you manipulate the criteria fields, the following demonstration is based on a query named "Query Number Demo" in the HelpDemoDB database and contains the following criteria:

Obviously this query would never return any records. It has been constructed to illustrate what happens as different operations are performed. For the purpose of explanation, the criteria named "Gift Count Is Equal to 1" will be referred to as "Line 1", and so on.

Key Points

During this explanation, we will refer to "And" and "Or" groups. Aegis CRM groups criteria based on these operators.

"And" groups are identified with a red ampersand.
"Or" groups are identified with a blue "Or".
Hovering the mouse icon over any group operator with reveal a text box showing the underlying selection statement. The following "And" group...

...will display the following text box when the mouse is hovered over the red ampersand

Notice that the two items are joined by the "AND".
Dragging a grouped item onto the operation indicator (red "&" or blue "Or") will move that item to the bottom of the group. In the following case line 4 was dragged and released on the ampersand. Note that it now appears as the last item of the group.

Dragging an item which is a member of a group onto another item in the same group will create a new group, under the opposite type operator. (And becomes Or, and vice-versa)
The item that was dragged becomes the last item in the newly created group
In the following case, Line 1 was dragged and released on Line 6

Note the newly formed "Or" group, and the dragged item (Line 1) appearing at the bottom of that group.
Hovering the mouse over the top ampersand reveals the following text box:

Dragging an item from one type group onto an item in a different type group (from an And group to an OR group) creates a new group of the opposite type
Continuing the sample above, in the following case line 5 was dragged and released on line 6

Note that new "Or" group has been created. Hovering the mouse over the first ampersand will reveal the following text box

Dragging an item from one type group to the operation indicator of another group will make that item the last member of the destination group. Continuing to use the sample, Line 2 is dragged and released on the blue "OR"

Note that even though line 1 and line 2 are separated by the And operator group, they are still part of the same group. Viewing the underlying statement by hovering the mouse over the blue "Or" icon reveals the following:

Aegis CRM's query behavior is the same when adding new criteria to existing criteria. In the following example we will add the additional criteria to our example.
Expand the Contact's folder, and choose the "City" field

Drag the "City" onto the criteria pane, and release your mouse over the top ampersand. You will receive the following dialog box:

Note that Aegis CRM has preselected the "And" group. Enter Denver as the city and click Save. Note that "City Is Equal to Denver" has been added at the end of the "And" group.

Right click on the City Is Equal to "Denver" and choose "Remove Criteria"
Now, once again drag the City criteria to the right pane, and release your mouse over a blank area of the criteria window. You will see the following dialog box:

Choose the "Or" operator, enter Denver in the variable field, and click save. Hovering over the top ampersand shows the following dialog box. Note that the City criteria has been added to the end of the "Or" group.

Adding or manipulating existing criteria will continue to extend the statements if the user wishes. However, adding multiple levels tends to become very complicated and difficult for the user to ensure accuracy. Consider using one of the "With" functions to reference multiple queries.