PS Query Prompts Series: Give Users More Ways to Search
So far, we've given users the option to use wildcards and leave a prompt blank. Now we're tackling the problem of "I have a list of something, but I really don’t want to run the query over and over or have tons of optional prompts".
A comma-delimited prompt lets users enter one or several values in one prompt, separated by commas.
Session 3: Create a Comma-Delimited Prompt
What It Does
A comma-delimited prompt lets users enter one or many values in the same prompt. When they need several, they separate the values with commas, such as:
00123456,00123457,00123458
When the query is ran, it returns rows that match any value in the list. One prompt, several values, fewer reruns. Everybody wins.
For this example, we'll use EMPLID. Users enter each ID exactly as it appears in PeopleSoft, with commas between the values. Spaces after the commas are fine—the expression will remove them.
How to Build It
There are two pieces to this one: the prompt that collects the list and a slightly odd-looking criterion that searches it. Stick with me—the odd-looking part does have a purpose.
Create the Prompt
1. Go to the Prompts tab and select Add Prompt.
2. Leave the Field Name and Prompt Table fields blank.
3. Change the default Length to 254. As of PeopleTools 8.63, that's still the maximum for a character prompt.
4. Make sure Edit Type is set to No Table Edit.
5. In Heading Text, enter XXX,XXX,XXX so users know to separate values with a comma. Heading Text is limited to 30 characters, so short and useful wins here.
6. Select OK.
Create the Criteria
1. Go to the Criteria tab and select Add Criteria.
2. Select Expression as the Expression 1 Type.
3. Select the New Expression link.
4. Leave Expression Type and Length as they are.
5. Enter 'X' in the Expression Text box.
6. Select OK.
7. Leave the Condition Type as Equal To.
8. Select Expression as the Expression 2 Type.
9. Enter the following in the Define Expression 2 box:
- 'X' AND 0 <> INSTR(',' || REPLACE(:1, ' ', '') || ',' ,',' || RTRIM(A.EMPLID) || ',')
- Replace :1 and A.EMPLID with your prompt alias and ALIAS.FIELDNAME.
11. Select OK.
Why the Settings Matter
The number of values that fit into the 254 character limit depends on the length of the values and the use or spaces and commas between them, so don't promise a specific count.
No Table Edit is important because the user's full entry—00123456,00123457,00123458—is treated as one prompt value. A prompt table would try to validate the entire list as though it were one EMPLID, and that isn't going to end well. And because it uses No Table Edit, PS Query won't validate each EMPLID. If a user mistypes one, the query will still run; the incorrect value just won't return any matches.
The 'X' may look like it's doing absolutely nothing—and, by itself, it is. The Equal To condition turns the beginning of the criterion into 'X' = 'X', which is always true. That gives PS Query the comparison it expects, and the real filtering happens after AND.
REPLACE removes spaces from the list the user entered, so these entries work the same way:
• Without a space: 00123456,00123457
• With a space: 00123456, 00123457
INSTR then checks whether each EMPLID appears in the list. If it finds the EMPLID, it returns a number other than zero and the row stays in the results. RTRIM removes any trailing spaces from the EMPLID stored in PeopleSoft before the comparison is made.
The commas around both sides of the comparison make sure the query finds the full EMPLID. Without them, a shorter value could accidentally match part of a longer one.
How to Test It
Review the Results Fields
Display EMPLID and the person's name while you're testing. You want to see exactly which IDs came back, not stare at the results and cross your fingers.
Save and Test the Query
Save the query, select the Run tab, and give it a proper workout:
1. Enter one valid EMPLID and confirm that only that person is returned.
2. Enter several valid EMPLIDs separated by commas and confirm that everyone in the list is returned.
3. Add an invalid EMPLID to a list of valid ones. The valid people should still return, and the bad value shouldn't match anything.
4. Enter a shorter value that appears inside a valid EMPLID and confirm that the longer EMPLID isn't returned by mistake.
5. Run the same list again with spaces after the commas and confirm that you get the same results.
6. Test a longer list and make sure the prompt accepts the number of EMPLIDs your users are likely to enter.
Looking Ahead
Next up: the Flex prompt, which combines optional, wildcard, and comma-delimited functionality in one prompt.