PS Query Prompts Series: Give Users More Ways to Search

We've made it to the final post in the series! The Flex prompt really is the One Prompt to Rule Them All. It combines optional, wildcard, and comma-delimited functionality in a single prompt.

Users don't have to decide which kind of prompt they need before running the query. They just enter what they have—or leave it blank—and the expression figures out what to do.

Session 4: Create a Flex Prompt

What It Does

A Flex prompt gives users four practical choices:

1.       Enter one value to return an exact match.

2.       Enter several values separated by commas to return matches for any value in the list.

3.       Enter a wildcard to search for a pattern.

4.       Leave the prompt blank to return all values for that prompt.

That's a lot of flexibility packed into one little box. The magic—well, the SQL—is a CASE expression that checks the user's entry from top to bottom and stops at the first condition that works.

How to Build It

We'll use DEPTID as the example field, but you can use the same setup with any other character fields.

Create the Prompt

1.       Go to the Prompts tab and select Add Prompt.

2.       Leave Field Name and Prompt Table blank.

3.       Change Length to 254, the maximum PS Query allows.

4.       Set Edit Type to No Table Edit.

5.       Select Text as the Heading Type.

6.       In Heading Text, enter a short instruction such as XXX,XXX,XXX-% or Blank=All. It fits within the 30-character limit and tells users what their choices are.

7.       Select the Optional checkbox so users can leave the prompt blank.

8.       Select OK to save the prompt.

Create the Expression

1.       Go to the Expressions tab and select Add Expression.

2.       Select Number as the Expression Type.

3.       Set the Length to 1 and Decimal Positions to 0.

4.       Build the expression below. Use Add Prompt every place [PROMPT] appears, and use Add Field to insert DEPTID every place [FIELD] appears. You can also type in your prompt alias and ALIAS.FIELDNAME yourself.

a.       The brackets are placeholders to show you what belongs where, so don’t type [PROMPT] or [FIELD] into the expression. Use Add Prompt or  Add Field or type the values in directly. 

b.       CASE WHEN [PROMPT] IS NULL OR TRIM([PROMPT]) = '' THEN 1
WHEN ',' || REPLACE(TRIM([PROMPT]), ' ', '') || ',' LIKE '%,' || [FIELD] || ',%' THEN 1
WHEN [FIELD] LIKE TRIM([PROMPT]) THEN 1
ELSE 0
END

5.       Save the expression, but don't select Use as Field. This expression is doing behind-the-scenes work; users don't need a mysterious column of ones and zeroes in their results.

Create the Criteria

1.       Go to the Criteria tab and select Add Criteria.

2.       Select Expression as the Expression 1 Type.

3.       Select the Flex expression you just created.

4.       Leave the Condition Type as Equal To.

5.       Select Constant as the Expression 2 Type.

6.       Enter 1 as the Expression 2 Value.

7.       Select OK, then save the query.

Why the Settings Matter

The CASE expression works from top to bottom. As soon as one line returns 1, the row passes the criterion and stays in the results. If the expression reaches ELSE 0, the row doesn't match the user's entry and is left out.

Here's what each part is doing:

•      Blank entry: The first line returns 1, so the query returns all values for that prompt.

•      One value or a comma-delimited list: The second line cleans the entry, adds commas around the list, and checks for the current row's value. The commas prevent partial matches, so entering 06 won't accidentally match 060.

•      Wildcard entry: The third line uses LIKE to check a wildcard pattern such as 1% or %WD%.

•      No match: ELSE 0 removes the row from the results.

•      REPLACE removes spaces from the user's entry, so both of these work the same way:

o   Without a space: 060,061,062

o   With a space: 060, 061, 062

No Table Edit matters because wildcard patterns and comma-delimited lists aren't individual values that a prompt table can validate. It also means PS Query won't catch a typo. The query will still run; the mistyped value just won't return a match.

How to Test It

Review the Results Fields

Display DEPTID while you're testing so you can see exactly which values were returned. Once you're satisfied that everything works, you can decide whether the field belongs in the final results.

Save and Test the Query

Save the query, select the Run tab, and test each option separately:

1.       Enter one valid value and confirm that only that value is returned.

2.       Enter several valid values separated by commas and confirm that every value in the list is returned.

3.       Run the same list with spaces after the commas and confirm that you get the same results.

4.       Enter a wildcard and confirm that every returned value matches the pattern.

5.       Leave the prompt blank and confirm that the query returns all values for that prompt.

6.       Enter a value that shouldn't match anything and confirm that no matching rows are returned.

7.       Test a shorter value that appears inside a longer one and confirm that the query doesn't return the longer value by mistake.

Wrapping Up

That’s a wrap! This brings us to the end of our prompt series. We’ve covered wildcard prompts for broader searches, optional prompts that can be left blank, comma-delimited prompts for entering multiple values, and the Flex prompt that combines all three.

Each option gives users more control over the results they receive when running a query. The key is choosing the prompt that delivers the right amount of flexibility without making the query harder to use.