I am currently using BO 6.5.x and I need to a prompt where the user can select values from list of values. The user can also select ‘ALL’ from list of values.
The database table do not have ‘ALL’ as record, so I need to manipulate such that user can see ‘ALL’ in the list of values.
I was trying the following code as per the BO documentation but its not working.
@Select(Trader\Company Name) IN @Prompt(‘Select one/more/ALL Traders from list’,‘A’,{‘ALL’},‘Trader\Company Name’,multi,free)
When I add {‘ALL’} in the prompt, it is failing.
Any help/suggestion in this regard would be highly appreciated.
There are a couple of entries in the Designer FAQ that detail the steps you need to take. The work to add ALL to the LOV has to be done in the Universe Designer, thus the FAQ entries are in the same forum.
In designer select the object in question and double click to open the properties
In Properties dialog select the Properties tab
Under list of values click edit
In the Query Panel click View SQL
Update the SQL as outlined below
SQL Before Update:
SELECT DISTINCT
TABLE_NAME.COL_NAME
FROM
TABLE_NAME
Edit this to :
SELECT '*ALL*' FROM DUAL
UNION
SELECT DISTINCT
TABLE_NAME.COL_NAME
FROM
TABLE_NAME
Note that I use ‘ALL’ to ensure that the ALL appears at the top of the list.
Important : Tick “Do not generate SQL before running”
Click OK, OK, OK.
Change definition of your Filter Object as outlined below.
@Select(Trader\Company Name) IN @Prompt('Select one/more/ALL Traders from list','A','Trader\Company Name',multi,free)
OR '*ALL*' IN @Prompt('Select one/more/ALL Traders from list','A','Trader\Company Name',multi,free)