PS Query Prompts Series: Give Users More Ways to Search
Series Overview
A prompt lets the person running a query choose a value without changing the query itself. That works nicely when users know exactly what they want. Of course, that is not always how it goes. Sometimes they want to use only part of a value, need several values, or do not want to limit the results by that field at all.
This series looks at four ways to give users a little more flexibility:
1. Wildcard prompts: Search for values that match a pattern.
2. Optional prompts: Enter a value or leave the prompt blank to skip that filter.
3. Comma-delimited prompts: Enter several values in one prompt, separated by commas.
4. The Flex prompt: Combine optional, wildcard, and comma-delimited functionality in a single prompt.
Session 1: Create a Wildcard Prompt
What It Does
An exact-match prompt is pretty straightforward: the user enters a complete value or selects one from a lookup list. For example, they could look up and select department 10010, and the query would return results for that department only. Perfect—if one department is all they need.
A wildcard prompt comes in handy when the user needs more than one value that follows the same pattern. If several department IDs begin with 10, they can enter 10% and return all of them in a single search or just put in % to return all values.
When the criterion uses the Like condition type, PS Query recognizes two wildcard characters:
· % stands for any number of characters, including none.
· _ stands for exactly one character.
|
User enters
|
PS Query looks for values that…
|
|
10010
|
Match 10010 exactly.
|
|
10%
|
Begin with 10.
|
|
%10
|
End with 10.
|
|
%10%
|
Have 10 anywhere.
|
|
10___
|
Begin with 10 and have exactly three more characters.
|
When you test, use values that actually exist in your environment. It makes confirming the results a whole lot easier!
How to Build It
Here is the setup:
1. Go to the Fields tab.
2. Find the character field you want to use for the prompt. We will use DEPTID in this example.
3. Select the Add Criteria icon beside the field.
4. Set Condition Type to Like.
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 short instruction, such as Dept (% wildcard; blank=all). Don’t make users guess what they are supposed to enter.
9. Set Edit Type to No Table Edit.
10. If users need a lookup list, select a suitable record in Prompt Table field. If no lookup is needed, select No Value.
11. Select the Optional checkbox.
12. Leave Default Value blank.
13. Select OK to save the prompt, then select OK again to save the criterion.
Why the Settings Matter
These settings work together, but each one has its own job:
· Like tells PS Query to treat % and _ as wildcard characters.
· No Table Edit allows the user to type a pattern, such as 10%, even though that pattern is not an individual value in the prompt table.
· Optional lets the user leave the prompt blank. When left blank, PS Query runs without this particular criterion.
One important clarification: leaving the prompt blank does not remove the query's other criteria, joins, or security restrictions. In the suggested heading, blank=all means all values allowed by the rest of the query—not every value in the database.
How to Test It
Review the Results Fields
Before you start testing, make sure the prompted field appears in the results. Give the fields meaningful headings, put them in a logical order, and add a useful sort order. Keeping DEPTID visible makes it much easier to confirm that the wildcard returned what you expected.
Save and Test the Query
Save the query, select the Run tab, and test each possibility separately:
1. Enter a complete value and confirm that only that value is returned.
2. Enter a prefix followed by % and confirm that every returned value begins with that prefix.
3. Place % before and after part of a value and confirm that each result has that sequence.
4. Use _ to replace one character in a known value.
5. Leave the prompt blank and confirm that the query runs without this criterion.
6. Enter a value that should not match anything and confirm that no rows are returned.
Start with a narrow wildcard pattern. A broad pattern—or just using the % to return all values—can return a whole lot more data than you expected!
Looking Ahead
Next up: optional prompts and what happens when the user enters a value—or leaves the prompt blank.