Overview

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 Query bar which appears on the Query Page below the status bar.

Using the Saved Query 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. Admin users can access advanced saved queries for all user groups without having to be a member of each group.

Field Level Visibility in Advanced Saved Group Queries

Field Level Visibility is the feature that allows fields to be restricted by user group in various areas of ProblemTracker such as the Add, Edit, and View pages and in Queries and Reports. 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 ProblemTracker. 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 ProblemTracker administrator if you wish to include a field in an advanced saved group query that is not visible to the selected user group.

Building Query Phrases

The procedure for building an advanced query differs from a standard query in that multiple options for each field can be selected and / or excluded for advanced queries. This is done by composing various query phrases that will be combined to generate the query results. These phrases are constructed 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:

Table 1

3rd Field Type	4th Field Displays...	Yellow Triangle On...	Type of Information to Enter Near Yellow Triangle...
PRN	<, >, =, >=, <=	Row 2	PRN number
Text Field	"contains" or "is empty"	Row 2	text string (or nothing if "is empty" is selected)
Pulldown Menu	is one of	Row 3	select value(s) from menu
RelNum	=	Row 4	select value from menu or leave as "*"
Date	"before", "on or after", or "is empty"	Row 2	date (or nothing if "is empty" is selected)
Float	<, >, =, >=, <=	Row 2	floating point number
Integer	<, >, =, >=, <=	Row 2	non-negative whole number
URL	"contains" or "is empty"	Row 2	text string (or nothing if "is empty" is selected)
(	"(blank)"	N/A	N/A
)	"(blank)"	N/A	N/A
<Clone>	"is"	Row 3	true (if record is a clone) or false

A yellow triangle will appear to the left of the second, third or fourth rows in this section based on the selection made for the third field in the first row. The yellow triangle indicates that more information is needed in that particular row to complete the query phrase. Table 1 describes the type of information that should be entered in rows 2, 3, or 4 based on the selections made in the first row.

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.

Define an Advanced Query

To define an advanced query, follow these steps:

1. To start, select a query from the Saved Query 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 Adv. button on the Saved Query bar.
3. Select whether this is a Personal or Group saved query. If it is a Group advanced query, also select the User Group to which this advanced saved query should be visible. You will only be able to select the user groups in which you are a member (However, the ProblemTracker Administrator can add advanced saved queries for all user groups). Click OK to proceed.
4. 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.
5. Build the first query phrase, by selecting "(blank)" for the first field of the first row of the Query Phrase section.
6. Select values for the rest of the fields in the first row.
7. 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.
8. Click on the Insert button to add the first query phrase to the Full Query section.
9. Click on the Reset button to clear the Query Phrase section, then construct the next query phrase.
10. Repeat steps 7 and 8 until you have inserted all of the query phrases you wish to include.
11. If desired, choose fields and order in the Sort By section to sort the query results.
12. If desired, select a Report Layout from the pulldown menu.
13. Click on the Add button to save your advanced query.

Run an Advanced Query

To run an advanced query, follow these steps:

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

Preview an Advanced Query

Many times 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. Press the Preview button in the Saved Query bar. The parameters for the advanced query appear in the query dialog below.
2. Modify the advanced query parameters if desired.
3. Press the "Run Query" button in the query dialog to submit the query.

Edit an Advanced Query

To edit an advanced query, follow these steps:

1. Click on the pulldown menu in the Saved Query bar and select the advanced query you would like to edit from the list of options.
2. Press the Edit button in the Saved Query bar. A page containing the current advanced query parameters is displayed.
3. 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.

Delete an Advanced Query

To delete an advanced query, follow these steps:

1. Click on the pulldown menu in the Saved Query bar and select the advanced query you would like to remove from the list.
2. Press the Delete button in the Saved Query bar.