Weird actions when using '%' functionality in prompt

[system]

I have a requirement where users may want to select one value, multiple values or % to select ALL values in a prompt.

Numerous threads has given answers on how to achieve this.

FAQ: Designer

Designer FAQ How should I impose a WHERE clause in my object(s) in the universe? Is there any way to set up a prompt that will allow a user to select one value, many values, or type ‘ALL’ for all values in a list? Now that my LOV lets me use ALL for all values, how do I get the ALL in the list? I’ve deleted a universe and I’m in a mess! What do I do now? How do I migrate universes between repositories? How do I create cascading or nested prompts? What is an isolated join and how do I f…

I have implemented one of the suggestions as shown below:

(@Select(Sales\Customer Type) IN @Prompt(‘Customer Type? (Enter % for all Customer Types)’,‘A’,‘Sales\Customer Type’,multi,free)) OR (’%’ IN @Prompt(‘Customer Type? (Enter % for all Customer Types)’,‘A’,‘Sales\Customer Type’,multi,free) )

However, note the strange workflow:

1. If I enter % in the Customer Type prompt, everything works fine.

2. If I click on the Values button, I get a second prompt box with the same Prompt text. :? Clicking the Value button on this second prompt does NOT do anything. Clicking the Cancel or OK button presents me with a list of values! Selecting values then places those values in the first prompt box. Query runs fine.

My issue concerns point 2.
Why do I get a second prompt box? If I select values I want to see a list
of values.
Why does the Values button in the second prompt not work?
Why does the Cancel and OK button present me with a LOV in the second prompt box?

Has anyone come across this problem before and if so, how have they resolved it?

---

Miguel Varela (BOB member since 2002-08-15)

[system]

Can you look at ur ‘Sales\Customer Type’ lov definition, you may be having a customized lov on it with a prompt.

Reema

---

reemagupta (BOB member since 2002-09-18)

[system]

reemagupta

I checked the LOV definition. All three items are checked:

• Allow users to edit this list of values
• Automatic refresh before use
• Export with universe

I tried a number of combinations and it would appear that the problem is removed if I uncheck the Automatic Refresh before Use.

This would make sense as if you have an automatic refresh before use, you will simply be generating the prompt once again.

However, there may well be occasions where a refresh is required.

Any ideas how to get around this?

---

Miguel Varela (BOB member since 2002-08-15)

[system]

reemgupta

Thanks for your suggestions. I have now found a workaround which I’d like to share with everyone else.

I created a filter object based on the original customer type object which contains the prompt selection criteria.

I then pull this filter into the report.

The original object still keeps the ‘Automatic Refresh before use’ item selected.

On refreshing the report, the user can select % or if the user selects values, a refreshed LOV will be displayed.

Thanks for your help

MV

---

Miguel Varela (BOB member since 2002-08-15)