Create a Prompt with a List of Query Results

When you create a prompt that presents a list of results from another query, the prompt selection dialog box will display a drop-down list of values retrieved by running that query.

Tip: You can use a prompt with a list of query results to filter a dependent prompt's values.

Before You Begin

You can use only a Select query for generating a list of prompt values. Note that:

If the selected query contains only one column, the values return in that column will be displayed in the list of available prompt values. For example, if the query contains the Equipment ID column, the prompt will display a list of Equipment IDs.

If the selected query contains more than one column, the list of prompt values will contain a concatenated list of values from the second column and each subsequent column. For example, if the query results look like this table:

Equipment ID	Manufacturer	Description	Status
123	Alco	Tank	Active
456	Whitlock	Pump	Inactive
789	Delta	Pressure Vessel	Active

... the list of prompt values will look like this:

Alco Tank Active
Whitlock Pump Inactive
Delta Pressure Vessel Active

The values from the first column, Equipment ID, are not displayed as part of the prompt values.

Tip: Select Unique Values Only in the prompt query to keep the prompt list from containing duplicate values.

Steps

Access the Prompt Settings section of the Expression Builder window.
Define the prompt settings as needed.
In the Valid Values list, select Values From Query, and then select Next.

The Values From a Query section appears.
In the Enter query text or click Browse button to select an existing query box, enter SQL code directly. If you completed this task, then proceed to Step 6.

-or-

Select Browse.

The Select a query from the catalog window appears.
Select a query from the folder hierarchy, and then select Open.
Select the Values are Exclusive check box if you want users to be able to choose only from the list of presented values in the prompt window. If you do not select this check box, users will be able to enter an alternate value for the prompt.
Select the Allow Multiple Selections check box if you want users to be able to select multiple values by which to filter the query results.

Note: The Allow Multiple Selections check box is enabled only if you select the Values are Exclusive check box.
Select Next.

The Configure Default Prompt Selection section appears.
If you want a particular value to be selected by default, then select the row containing the necessary value, and then select Finish.

Note: You can select multiple default prompt values only if the Prompt Data Type is set to Character and you selected the Allow Multiple Selections check box in the Values From Query section.

The prompt appears in the Expression Builder window.
Select Done.

The Expression Builder window closes, and then, in the grid in the Conditions section, <expr> appears in the field on which you created the prompt.

Results

When a user runs the query, the prompt will contain a list of values from the second column in the query that the user selected in the prompt. The query results will display all records where the value in the field on which the user built the prompt is equal to the value in the first column that corresponds to the second column in the query that the user selected in the prompt.