Search Queries
The Processor uses the Query area of the Process Reports page to create new search queries and manage existing queries. The queries are if/then conditional statements. If the defined conditions are met, then the invoice is displayed to the administrator.
Understanding Custom Queries
When creating or editing custom queries, the administrator should consider the following.
A custom query is composed of one or more conditions.
There is no limit to the number of conditions that a custom query can include.
Multiple conditions are separated by either And or Or
Custom queries are only available to the administrator who created them. They cannot be shared.
Conditions are composed of the following parts:
- A Data Object that represents an invoice or part of an invoice such as the Invoice Details section of an invoice
- A Field that is available on an invoice or a specified invoice part such as the Expense Type field
- An Operator such as Equal, Not Equal or Contains
The operators that are available are dependent on the field being searched. For example, if the selected field is Expense Type, the operators available for selection are In, Not In, Equal and Not Equal
- A Value
The values that are available are dependent on the field being searched. For example, if the selected field is Expense Type, the values available for selection are the list of expense types configured on the system.
When a conditional expression is composed of multiple conditions, each condition is separated by either And or Or. Parentheses are optional and are used to define order of operation for the And/Or operators. If the parentheses are omitted, And/Or operations are carried out left to right. There is no precedence of And over Or; the evaluation of the expression is simply left to right.
If a conditional expression contains brackets, the count of left brackets must match the count of right brackets. There can be up to three brackets for both left and right sides.
Simple Condition Example
A condition is a simple Boolean comparison, such as:
Invoice Total Is Greater than 1000
The condition looks at the invoice total and sees if it is greater than 1000. If the condition is true, then the invoice is located and displayed to the Processor. If the condition is false, the invoice is not displayed.
Complex Condition Example
The conditional expression can be a single condition as in the above example, or it can be a complex expression involving multiple conditions connected by And/Or operators and parentheses, such as the following example that contains four conditions:
Invoice Date Is Greater Than 30/09/18 and (Employee Group Equals GermanGroup or Employee Group Equals FrenchGroup) and Invoice Total Is Greater Than the Amount Remaining for Europe Budget.
Adding a Query - The Condition Editor
When adding a query, the processor uses the condition editor. Each condition appears on two lines. The fields, from left to right, are:
Field | Description |
|---|---|
Left Brackets | Select zero to three parentheses, depending on the complexity of the condition. |
Data Object | Select a data object. |
Field / Value | Select an item from the helper pane that appears. The information that appears within this pane is based upon the selection within the Data Object list. |
Operator | Select an item from the helper pane that appears. The information that appears within this pane is based upon previous choices. |
Data Object | This field will always display as Value and cannot be changed. |
Field / Value | Select an item from the helper pane that appears. The information that appears within this pane is based upon the selection within the Data Object list. |
Right Bracket | Select zero to three brackets, as required. |
And / Or | Select either option to join the current condition to the next condition. |
Determining How to Create a Query
When creating or editing a query, the administrator defines the conditional expression(s) – the if portion of the query. The expression can contain one or more conditions separated by And or Or.
For example: Assume the administrator wants to locate all invoices totalling 500.00 USD or more:
(Invoice Total Greater Than or Equal 500.00 USD)
Creating a New Query
A new query can be created for use on the Process Invoices page.
To create a new query:
Select Query > New Query. The Query Builder page appears. This page includes the following:
Field
Description
Query Name
Enter a name for the query. The name you provide will appear in the Run Query list on the Process Invoices page.
Conditions
Select the appropriate information from within the Condition Editor.
Add
Click to add additional conditions to the Condition Editor. Additional rows are always added at the bottom of the current rows. There is no limit to the number of rows that can be added.
Remove
Select the check box next to the condition to be deleted, and then click Remove. The condition is deleted. This action is permanent. If you delete a condition in error, you will have to recreate it in its entirety.
Continue adding conditional rows as required.
Click Save, or Save & Run. The Process Invoices page appears, with the query available in the Run Query list, or the query runs in the Process Invoices page, returning the results of the search.
Editing Existing Queries
Queries can be edited from the Query > New Query menu.
To edit existing queries:
Select Query > Edit Save Query.
Select the desired query. The Edit Query page appears.
On the Edit Query page, edit the condition information, add another condition row or delete a condition row.
Click Save or Save & Run. The Process Invoices page appears, with the query available in the Run Query list, or the query runs in the Process Invoices page, returning the results of the search.
Deleting a query
Queries are individually created; therefore, you will only be able to see and affect the queries that you created.
To delete a query:
Select Query > Edit Save Query.
Select the desired query. The Edit Query page appears.
Click Delete Query. The query is removed from the Query list on the Process Invoices page.
