ADVANCED QUERY
The following directions are for Setting up and Saving an Advanced Query. The examples shown are for Instrument records.
Basic Advanced Queries:
- Provide a List of Instruments with Additional Columns displayed.
In the upper left corner of the Advanced Query screen, the User has the option to add available fields to be displayed. In the example above the User has already selected “Location”, “Building”, “Last Cal Date” & “Next Cal Date” to also be displayed. To “Assign” additional fields either highlight the field and select the “Assign” button or “Double Click” on a field.
You can also change the order in which they are displayed by selecting the field you would like to move and select the appropriate “Move” button.
Note: You can only Display on one ‘User Defined Field’.
Below is an example of the Executed Query above:
- Provide a different Sorting order for the displayed list.
You can also select how the information that fits the Advanced Query will be sorted. Select one or more fields for sorting. The field selected for sorting does Not have to be a field that is displayed.
Note: You can only Sort on one ‘User Defined Field’.
- Basic Filtering:
In the “Selection Criteria” section, the User can create filters to display only those records that meet the criteria. The “Selection Field Name” will contain a dropdown that is applicable to the item type that you are Querying from.
The Operation dropdown will contain the following:
- < Less than
- <= Less than or equal to
- <> Not equal to
- = Equal to
- > Greater than
- >= Greater than or equal to
- LIKE Used with wildcards
- NOT LIKE Used with wildcards
Not all operations will work with all Selection Field Names.
Below are examples of some basic Queries: (Examples use the Instrument Record type.)
Display all Instruments with a Manufacture equal to “Fluke”
Display all Instruments with a “Next Cal Date” less than or equal to Today’s Date
Display all Instruments that have the word “press” in the “Description” (useful to show all pressure gauges, pressure transducers, pressure regulators…)
Buy placing the “Wildcard” character “%” before and after the letters ‘press’ will show results of any Instrument record that has ‘press’ in the description.
The other Wildcard character is the underscore “_”, this is useful if you know the exact number of missing characters. An example of a use for this would be if your Instrument ID’s were all consecutive numbers starting with ID 00001 and ending with ID 05000. If the user wanted to view all the records between 03200 and 03299, the query would be set up like this:
This example would show a list of Instruments with IDs 03200 to 03299
- Date Field Key Words
When the Selection Field Name contains a Date the following Key Words will be available in the ‘Selection Value” field:
- End of Week
- End of Month
- End of Year
- Today
- Tomorrow
You can also enter an actual Date.
- “And” “Or” Grouping
By using the “And” grouping, the criteria will have to be met for both parameters. The example below will display all Instruments that have a Manufacture of “Fluke” AND have a “Next Cal Date” less than or equal to Today.
By using the “OR” grouping, the criteria will only have to meet either parameter to be displayed.
- Grouping
When you have multiple sets of Selection Criteria in your Query, the Group button may be used to put parentheses around those sets that you want to keep together. For example, imagine you have built a Query with the following three sets of Selection Criteria:
(Status = In Service OR Status = Out of Service) AND Cal Due Date = Today
Using the Group button to put parentheses around the first two sets of Selection Criteria forces the system to use them together. This will result in a Query list consisting of two types of Statuses. Some will have a Status of “In Service” and a Calibration Due Date equal to today’s Date; the others will have a Status of “Out of Service” and a Calibration Due Date equal to today’s Date.
If you do not Group the Statuses together, the system will pull up a list with two other types of Instruments. Some will have a Status of “Out of Service” and a Calibration Due Date equal to today’s Date, while the others will have a Status of “In Service” with no regard to Calibration Due Date.
- Saving
If you defined the parameters of an Advanced Query that you would like to use in the future, the Query can be saved and then executed from the main screen of the Item Type.
- Exporting and Printing
Once a Query has been executed and the list of items that fit the query is displayed, by right clicking on the grid you will have options to export the list to an Excel document, Word document or you can print the list. The User will also have an option to set parameters for the print out by selecting “Print Setup” option.
Print Setup:
