Defining Prompts
Adding a prompt lets you further refine a query when you run it. For example, suppose that you want to change a query so that you could prompt the user to enter a value for the duration of a vacation. Before you add the prompt, the query always retrieves rows for employees who had taken vacation based on a defined constant value on which to make a comparison. Adding a prompt to the query enables the user to enter any duration, and then the query can return employees based on the value provided when running the query.
When you run a query with a prompt, a prompt page requests the required value. All date, time, and datetime prompt fields are required fields when running Query. The query uses the value that you enter as the comparison value for the criterion that included the prompt.
If the field you are prompting has an associated prompt table (even if it is the Translate table), the Edit Table drop-down list shows its name.
Use the Prompts page (QRY_PROMPT) to add or edit a prompt.
Navigation
Image: Prompts page
This example illustrates the fields and controls on the Prompts page.
Use the Edit Prompt Properties page (QRY_PROMPT_SEC) to edit the prompt properties.
Navigation
Select Reporting Tools, Query, Query Manager.
Add a new query or search for an existing one.
Access the Prompts page.
Click the Add Prompt button or the Edit button on the Prompts page.
Image: Edit Prompt Properties page
This example illustrates the fields and controls on the Edit Prompt Properties page. Definitions for the fields and controls appear following the example.
|
Field or Control |
Definition |
|---|---|
| Type |
Indicates the type of the field. |
| Format |
Specifies the field format. Over a dozen formats are available, including Name, Phone, Social Security Number, and Zip Code. |
| Length |
Indicates the field length. |
| Decimals |
Defines the number of decimals that are allowed. |
| Edit Type |
Defines the type of field edit for the specified field. No Table Edit is the default value. In general, you should use the same edit type that is used in the field record definition so that this edit type is consistent throughout Oracle's PeopleTools. |
| Heading Type |
Select a heading type for the prompt from the following values:
|
| Heading Text |
Displays the label for the text box where you enter the comparison value. To change the text, select Text from the Heading Type drop-down list, and then enter the new label in the Heading Text text box. |
| Unique Prompt Name |
A default value that Query Manager generates for globalization. Only base language users can set this value to uniquely identify a query prompt parameter. |
| Prompt Table |
If the edit type is Prompt Table, you can select a prompt table to use. If the edit type is Translate Table, the value in the drop-down list determines the values used. PeopleSoft Query assumes that the specified field has translate table values associated with it, and that the field is identified as a translate table field in its record definition. |
| Optional |
Use this option to define whether the prompt is optional or required.
Note: A required prompt value must be validated based on the Edit Type list. Prompt with the Edit Type set to Yes/No Table cannot be set as optional prompt because there are only two valid values, Y and N. See the “Defining Optional Prompts” section later in this topic for more information about defining optional prompts. |
| Default Value |
Use this field to set a default value for the prompt. The value in this field is used to populate the prompt when no other prompt value is selected or entered. For character fields, you can add a default prompt value that has up to 254 characters long. Note: Entering a default value for a prompt set the prompt to required. If the Optional option is selected, when you enter a default prompt value for the prompt, the Optional option is cleared and a message appears saying: “Optional prompt cannot have default value. Prompt is now set to be required.” Default prompt value is not a translatable field. Tree prompts cannot be used as the default or optional prompt values. |
To define prompts:
From the Prompts page, click the Add Prompt button to add a new prompt, or click the appropriate Edit button to edit an existing prompt.
The Edit Prompt Properties page appears.
Click the Look Up button next to the Field Name field to select a prompt field.
After you select a prompt field, the name of the field appears. PeopleSoft Query looks to the record definition for information about this field and completes the rest of the page based on its properties.
Note: When using a prompt table on a field from a record definition with multiple keys, you must prompt for all higher-level keys before lower-level keys. PeopleSoft Query needs values for the higher-level keys to generate the correct prompt list. Because of this complication, you should not use multikey prompt tables.
In the Edit Prompt Properties page, if there is a default prompt value, when you run the query, the prompt window displays the default prompt value in the prompt field.
Image: Edit Prompt Properties page
This example shows the Edit Prompt Properties page with the Default Value field set to Cube Builder.
Image: Prompt window
This example shows the prompt window with the default prompt value Cube Builder displayed for the character prompt.
In the prompt window, you can either run the query using the default prompt value or enter a different value to overwrite the default prompt value. If you do not use the default prompt value, the prompt value that you enter or select must be validated based on the value selected in the Edit Type list. For example, if the Edit Type list is set to Prompt Table, the prompt value must be a valid value from the prompt table.
Note: After you define the default prompt value, you are not able to run the query with a blank prompt value. In fact, the value in the Default Value field is always used as the prompt value if you do not enter a value for the prompt. For example, after you define the Default Value field and save the query, and then you clear the default value when you run the query, the query still runs using the value in the Default Value field as the prompt value, even after you clear the value.
If you do not enter a default value for the prompt and the required prompt is set to No Table Edit, the system runs the query using the blank value for a character prompt and using 0 (zero) for a numeric prompt.
If there is a default prompt value and you enter NULL as a prompt value when you run the query, the default prompt value is used as the prompt value.
Note: PSQuery doesn’t support the criteria that compare the long character fields to the prompt values. You can only set criteria with long character field to be Is Null or Is Not Null.
PSQuery does not set a numeric prompt field to NULL, so numeric prompt always initialize with 0 (zero), which may be the value that you enter or the default value of a field.
If 0 is a value of an optional numeric prompt and it also is NULL, this NULL value is used as an indicator that you have not entered a prompt value; therefore, the criteria defined to be compared with an optional numeric prompt is dropped during runtime.
If 0 is a valid prompt value, you should not set the prompt to optional. The query is run with the prompt value set to 0 and the prompt value is validated accordingly.
Using System Variables as the Default Prompt Values
In addition to specifying the default prompt value as a constant, you can use system variables as the default prompt values for some prompt types.
This table lists the system variables that can be used as the default prompt values.
|
System Variable |
Valid for Prompt Type |
|---|---|
|
%Date |
Date |
|
%DateTime |
DateTime |
|
%Time |
Time |
For example, if the Default Value field in the Edit Prompt Properties page is set to %Date, when you run the query using the Run page in Query Manager, the prompt window displays the current date as the default prompt value. You then can either use the default value (current date) or select a new date.
Image: Edit Prompt Properties page
This example shows the Edit Prompt Properties page with the Default Value field set to %Date.
Image: Prompt window
This example shows the prompt window displaying the current date as the default prompt value.
When you use the Schedule Query page to schedule a query that has a date prompt with the Default Value field in the Edit Prompt Properties page set to %Date, the prompt window also appears displaying the current date as the default value.
To ensure the correct date is displayed on the query report every time the query is run:
If you click the OK button in the prompt window without entering a new prompt date, the default prompt date value (%Date) is saved to the run control record instead of the current date (resolved value of %Date).
If you click the OK button in the prompt window after you enter a new prompt date value, for example, 01/01/2014, the new prompt date value 01/01/2014, not the %Date value, is saved to the run control record.
This section describes optional Prompt expression types and how to set them.
Understanding Defining Optional Prompts
When a user runs a query with an optional prompt defined, a prompt dialog box appears when he or she runs the query. They can enter or select a prompt value to refine the returned result set or not enter a value/by-pass the dialog box and get the entire result set.
When defining criteria, the Between condition type is not supported for Prompt expression types defined as optional. Instead, use an Expression expression type using greater than (>) and less than (<) operators to define the criteria. The following example shows a sample expression using greater than (>) and less than (<) operators to achieve a similar result as a Between condition type:
(A.MESSAGE_SET_NBR = :1 OR A.MESSAGE_SET_NBR > :1) AND ( A.MESSAGE_SET_NBR < :2 OR A.MESSAGE_SET_NBR = :2)Setting Optional Prompts
Image: Edit Prompt Properties page
This example shows the Edit Prompt Properties page with the Optional option selected and prompt table is set to PRCSTYPE_VW, which is used to validate the prompt value.
When you use the Run page in Query Manager to run a query with the prompt set to Optional, a prompt window is displayed.
Image: View SQL page with prompt window
This example shows the prompt window when you run a query using the Run page in Query Manager.
Because the prompt is set to optional, you can either enter a valid prompt value or click the OK button on the prompt window without entering a prompt value.
If you enter a prompt value, that prompt value must validated because the prompt is defined to have the Edit Type option set to Prompt Table. If so, the query is run using the entered prompt value and the report results display the prompt setting.
Image: Run page
This example shows the query results with Type = XML Publisher is displayed as the prompt setting:
If you click the OK button without entering a prompt value, the criteria defined to compare with the optional prompt is dropped during runtime. The query is run without the prompt value and the report results do not display the prompt setting.
Image: Run page
This example shows the query results without the prompt setting.
When you use the Schedule Query page to run a query with the prompt set to optional, a prompt window is also displayed and you can either:
Click the OK button on the prompt window without entering a prompt value. Prompt is saved to the run control record without a value. During the running process, the criteria defined to compare with the corresponding optional prompt are dropped from the SQL before the query runs.
Enter a valid prompt value on the prompt window and click the OK button. The new prompt value is saved to the run control record. Because there is a valid prompt value, the criteria defined to compare with the corresponding optional prompt are used to run the query.