The Advanced Queries function allows you to build more intricate queries using operators such as AND, OR, and NOT, multiple selection of values to match, and grouping (parentheses) of query criteria to generate more refined results. You can define and save advanced queries, enabling you to quickly select from a list of frequently used advanced queries, rather than re-entering the numerous query parameters that compose this type of query. You can use either group or personal advanced queries. You can always save a personal advanced query, and if you have been given permission by your Administrator, you may also be able to save an advanced query for use by all users in a user group.

Advanced queries are managed by the Saved Queries bar which appears on the Search Page below the status bar.

Using the Saved Queries bar, you can define new advanced queries, edit or remove existing advanced queries, and run or preview an advanced query. Group level advanced queries are listed followed by the User Group name in square brackets (e.g. "Assigned to Me [Users]" denotes the saved report called "Assigned to Me" is accessible by the user group "Users"). Admin users can access advanced saved queries for all user groups without having to be a member of each group. If a user group is not available for selection, you may not be a member of the user group. If you are a member of the user group or are logged in as a user with the Admin privilege, the user group may not have visibility to at least one Project and Form. Only user groups that have visibility to at least one Project and Form (configured in the Admin section) will be available for selection when creating group advanced saved queries.

• Field Visibility - how the field visibility feature affects advanced queries.
• Define an Advanced Saved Query - how to add an advanced saved query
• Run an Advanced Query - how to run an advanced query.
• Building Query Phrases - explains how to build the query criteria.
• Preview an Advanced Query - how to adjust the criteria before a single run of a query.
• Edit an Advanced Query - describes how to modify an advanced query.
• Delete an Advanced Query - how to remove an advanced query.

Field Visibility is the feature that allows fields to be restricted by user group in various areas of Tracker such as the Add, Edit, and View Pages and in reports on the Search and Home Pages. That is to say that the system can be configured such that each user group may see a different set of fields in each area of Tracker. Therefore, when creating advanced saved group queries, all fields may not be displayed depending on field visibility settings for the group selected.

When a field is not visible to the user group selected in a advanced saved group query, the field will be set to the default value when the advanced saved group query is run. In most cases, this default value is "*", which will include all possible values for the field in the advanced saved group query results. In the case of the Deleted field, the default is "No" such that all records that have not been marked as deleted will be included in the advanced saved group query results.

If a personal or group advanced saved query was created before a field was made invisible to a user group, it is possible for this user group to see an invisible field in a clause in the "Query Phrase" section of the advanced saved query. Any clause containing a field that is now invisible to the user group will be "read only". This clause cannot be edited because it contains a field that has been restricted for the user group. However, the clause can be deleted from the advanced saved query.

Please contact your Tracker administrator if you wish to include a field in an advanced saved group query that is not visible to the selected user group.

Return to Topics List

To define an advanced saved query, follow these steps:

1. To start, select a query from the Saved Queries pulldown menu that will be used as the starting point (template) for the new saved query. To start with a basic saved query, select the saved query "Default [Users]".
2. Click on the Add button on the Saved Queries bar
   

3. In the form that appears, enter a query name (this is the name that you will use to retrieve the query) in the Query Name field.
4. In the Query Type field, select whether this is a "Personal" (to create a saved query that will only be accessible by you) or "Group" (to create a saved query that will be accessible to members of a particular user group) saved query.
5. If you selected "Group" for Query Type, select a user group in the User Group field to which this saved query should be visible. You will only be able to select the user groups in which you are a member (However, the Tracker Administrator can add advanced saved queries for all user groups).
6. Select "Advanced" as the Query Format and click Next to proceed
   

7. If you have access to multiple projects and/or forms, the Project and Form fields will be displayed. By default, "*" will be selected to match any project or form. To select multiple values, hold down the Ctrl button on your keyboard while clicking on each desired value. If specific forms are selected, a Get Fields button will appear. Click on it to update the list of available fields for selecting query criteria.
8. Select a value in the Report Layout pulldown. The report layout determines which fields will be displayed in the columns of the query results.
9. Phrases will be displayed in the Full Query section based on the saved query you selected in the Saved Queries pulldown before clicking on the Add button. To edit or delete an existing phrase, click on the phrase, then click on the Edit or Delete button above the Full Query section. Build query phrases by making selections in the Query Phrase box according to the information in the Query Phrases section. If you are creating/editing the first query phrase, "(blank)" must be selected for the first field of the first row of the Query Phrase section.
10. Select values for the rest of the fields in the first row
11. If applicable, enter information in the rows 2, 3, or 4. To select multiple values in row 3, hold down the Ctrl button on your keyboard while clicking on each value you wish to select.
12. Click on the Insert button to add the first query phrase or insert a query phrase into the Full Query section
13. Click on the Reset button to clear the Query Phrase section, then construct the next query phrase
14. Repeat the above steps until you have inserted all of the query phrases you wish to include
15. Choose how the results will be sorted in the Sort By section. Select a field to sort by, then choose Ascending or Descending as the sort order. Link and TextArea type fields are not available as sort options. To add another level of sorting, click on the Add More link. Up to 4 fields can be selected for sorting. Or, click the Remove to remove a sort option.

    Please note:

    • When the preference Exclude Time From Reports is enabled and a Date field is selected in the Sort By section, the query results will be sorted by the time in each record even though the results will not include the time information.
    • When PRN (Record ID) is selected for one of the Sort By fields, the records will be ordered according to the numeric component of the PRN (Record ID) field. The short name of the form will not be used as part of the sorting when PRN (Record ID) is selected. For example, let's say you have PRNs "REC1" and "REC2". The sorting will be done using "1" and "2", ignoring the "REC".
16. The name you entered for Query Name on the previous page will be entered as the title. If you wish to use something different, enter a title in the Report Title field.
17. Click on the Add button to save your advanced query

Return to Topics List

Run an Advanced Query

To run an advanced query, follow these steps:

1. Click on the pulldown menu in the Saved Queries bar and select the desired advanced query from the list of options
2. Click on the Run button to the right of the Saved Queries pulldown

Return to Topics List

Compose query phrases that will be combined to generate the query results in the Query Phrase section of the Advanced Query page.

Logic operators and fields are selected for a query phrase in the first row of the Query Phrase section. A description of the fields in the first row of the Query Phrase section:

1. The first field is a pulldown that contains the operators "AND", "OR", and "(blank)". For the first phrase of an advanced query, "(blank)" is the only possible option for this first field.
2. The second field is a pulldown that contains the operators "NOT" and "(blank)".
3. The third field is a pulldown menu that lists all fields within the data record. In addition, "(" and ")" are options in this pulldown that can be used to nest (or group together) query phrases within the full query.
4. The fourth field will automatically change based on the selection that was made for the third field. Table 1 shows the possible values for the fourth field based on the input of the third field:

3rd Field Type 4th Field Displays... Type of Information to Enter in Input Box... Assignee is one of select a user or [User Group] from menu Date "before", "on or after" date entered in the format that matches the date and time settings in Preferences section (e.g. "mm/dd/yyyy") Date "is empty" N/A Date "within the" Select "previous" or "next", enter an integer for the number of increments desired and select a time increment to search within a date range relative to the current date and time Deleted is one of select Yes or No from menu Float <, <=, >, >= and =. floating point number Integer <, <=, >, >= and =. non-negative whole number Link "contains" text string Link "is one of" enter a text string to search for items, select item(s) from menu Link "matches" select a saved query from menu PRN <, <=, >, >= and =. record number Product (History Pulldown) is one of select value(s) from menu Pulldown Menu is one of select value(s) from menu RelNum = select value from menu for each digit A - D or leave as "*" Reporter is one of select a user or [User Group] from menu Status is one of select a State or [State Group] from menu Text or TextArea Field "contains" text string Text or TextArea Field "is empty" N/A URL "contains" text string URL "is empty" N/A YesNo is one of select Yes or No from menu <Clone> "is" true (if record is a clone) or false <Full Text Search> "(blank)" enter a text string in Search for field, select "<All Fields>" to search all fields or select one or more specific fields in the in field, select a Search Method in the using field. Review Full Text Search for more details. ( "(blank)" N/A ) "(blank)" N/A

The selections made in the pulldowns determine what information should be entered into the Input box below the pulldowns. Table 1 describes the type of information that should be entered.

Once the components of the query phrase have been selected, you can click on the Insert button to add the query phrase to the Full Query section. To add another query phrase, click on the Reset button and begin selecting options in the first row.

To edit a query phrase that has been inserted into the Full Query section, click on the query phrase, then click on the Edit button. Modify the phrase in the Query Phrase section, then click on the Update button. The query phrase will be updated in the Full Query section.

To delete a query phrase that has been inserted into the Full Query section, click on the query phrase, then click on the Delete button.

Return to Topics List

Sometimes you may wish to verify the parameters of an advanced query before running it or perhaps modify it slightly before running it.

To preview, optionally modify, and run an advanced query, follow these steps:

1. Select an advanced query in the Saved Queries pulldown, then click on the Preview button to the right of the Saved Queries pulldown. The parameters for the advanced query appear in the query dialog below.
2. Modify the advanced query parameters if desired
3. Click on the Run Query button in the query dialog to submit the query

Return to Topics List

Edit an Advanced Query

To edit an advanced query, follow these steps:

1. Select the advanced query you would like to edit in the Saved Queries pulldown, then click on the Edit button to the right of the Saved Queries pulldown. A page containing the current advanced query parameters will be displayed.
2. In the Full Query section, click on a query phrase you wish to modify, make the changes in the Query Phrase section, then click on the Update button. After you are done making changes to the advanced query, click on the OK button to save your updates.

Return to Topics List

Delete an Advanced Query

To delete an advanced query, follow these steps:

1. Select the advanced query you would like to remove in the Saved Queries pulldown, then click on the Delete button to the right of the Saved Queries pulldown

Return to Topics List