PS Query Prompts Series: Give Users More Ways to Search

Last time, we gave users a little more search flexibility with a wildcard prompt. This time, we're looking at optional prompts—because sometimes users want to filter by a field, and sometimes they really don't.

An optional prompt lets users enter a value to narrow the results or leave the prompt blank to return all values for that field.

Session 2: Create an Optional Prompt

What It Does

With a standard prompt, the criterion stays in the query every time it runs. An optional prompt gives the user another choice: if they leave it blank, PS Query returns all values for the prompted field.

In other words, the user can:

•         Enter or select a value to apply the criterion.

•         Leave the prompt blank to return all values for the prompted field.

For example, an optional department prompt might let the user select department 10010 from a lookup list. If they enter 10010, PS Query limits the results to only that department. If they leave the prompt blank, PS Query removes the department criterion so the results will include all departments. Nice and simple.

How to Build It

The setup will look familiar:

1.       Go to the Fields tab.

2.       Find the field you want to use for the optional prompt. We'll use DEPTID in this example.

3.       Select the Add Criteria icon beside the field.

4.       Select the right Condition Type. For a prompt that returns one exact value, use Equal To.

5.       Under Choose Expression 2 Type, select Prompt.

6.       Select the New Prompt link.

7.       Select Text from the Heading Type drop-down list.

8.       In Heading Text, enter a clear instruction, such as Dept (blank for all). Don't make users figure out on their own that blank is an option.

9.       Select the Edit Type:

10.   Use Prompt Table when users should select or enter only valid values from a lookup list. This is the best choice for most optional prompts.

11.   Use No Table Edit only when unrestricted entry is intentional.

12.   For either Edit Type, if you're using a Prompt table record to provide look up values, make sure the correct record is in the Prompt Table field.

13.   Select the Optional checkbox.

14.   Leave Default Value blank.

15.   Select OK to save the prompt, then select OK again to save the criterion.

Why the Settings Matter

Only two settings really matter here, and they don't do the same thing.

·         Optional controls what happens when the prompt is blank. When it's selected, leaving the prompt blank tells PS Query to return all values for the field.

·         Edit Type controls what the user can enter.

o   Prompt Table Edit gives them a lookup and validates that the entry is correct.

o   Using No Table Edit can give users a lookup if a record is put in the Prompt table field, but it skips data validation entirely, meaning they can still type in whatever they want. Security can be overridden using No Table Edit. Even if a prompt is secured, that security only filters the dropdown choices. To actually validate the data and lock users down to your secured list, you need to use Prompt Table Edit.

For most optional prompts, Prompt Table is the better choice. Users can still leave the prompt blank, and you don't have to give up validation just to make the prompt optional.

Use No Table Edit only when unrestricted entry is actually needed, a reliable prompt table doesn't exist, the field is genuinely free-form, or if users need historical values that aren't in the lookup. Just remember that users can also type typos. PS Query won't warn them; it'll run the query and just return no results.

Finally, don't enter a Default Value. If PeopleSoft fills in a value, the prompt isn't blank anymore, and it becomes required.

How to Test It

Review the Results Fields

Keep the prompted field visible while testing. If DEPTID is the optional prompt, display DEPTID and its description so you can actually see whether the department filter was applied. Otherwise, you're mostly just hoping it worked.

Save and Test the Query

Save the query and select the Run tab. Test it with a value and without one.

1.       Enter or select a valid value. Confirm that the results are limited to that value.

2.       Run the query again and leave the prompt blank. Confirm that results for other values are now included.

3.       If the prompt uses Prompt Table, enter an invalid value and confirm that PS Query requires a valid selection.

4.       If No Table Edit was intentionally used, enter a mistyped value and confirm that the query runs but returns no matching results.

5.       Compare the number of rows returned with and without a prompt value. Make sure the broader result set is still reasonable for the query's purpose.

Looking Ahead

Next up: comma-delimited prompts, which let users enter several values in one prompt and return results that match any value in the list.