How can I create a select list without specifying its List of Values?

Question

I have a form which has two select lists. The first one is prepopulated with an SQL query and has a dynamic action to set the value of the second list based on what is selected in the first.

I tested this out with a select list-textbox combination and it works just fine. However when I try to change the textbox into a select list then I am forced to also provide a "list of values" which overwrites my dynamic action.

How can I create a select list without specifying its List of Values?

Some clarification about my situation.

I have 2 tables: Wines and Winegroups.

+---------+------------+
|  Wines  | Winegroups |
+---------+------------+
| WineID  | GroupID    |
| Name    | Name       |
| GroupID |            |
+---------+------------+

In APEX I have a form like this:

When the page is loaded, the first select list will get filled with values using this query:

select distinct Name, GroupID from Winegroups

Furthermore there is a dynamic action defined like this which fires whenever a group is selected. This is supposed to change the values inside the 'Wines' select list.

Event: Change
Selection type: Items on P5_GROUPID
Action: Set value
Page items to submit: P5_GROUPID
Affected elements: Items on P5_NAME

select distinct Wines.Name, Wines.WineID id
from Wines
left join Winegroups
 on Wines.GroupID = Winegroups.GroupID
where Winegroups.Name = :P5_GROUPID
order by 1

The second select list uses the same query. When I execute my program the first select list is filled with groups nicely but the second one never gets any values.

Answer 1

Starting in apex 4.1, it is no longer necessary to use a dynamic action to get one select list to populate based on the value selected in a prior one. To get the functionality you want, you should be able to just do:

1. Disable or remove the current dynamic action.
2. Go into the Wines select list page item (P5_NAME).
3. In the List of Values section, for the Cascading LOV Parent Items field, select the winegroup select list item (P5_GROUPID).
4. The Page Items to Submit field will appear in that same section. Select the P5_GROUPID there as well.
5. In the list of values definition, put your query just as you wrote it above:

select distinct Wines.Name, Wines.WineID id  
from Wines  
left join Winegroups  
on Wines.GroupID = Winegroups.GroupID  
where Winegroups.Name = :P5_GROUPID  
order by 1;

Edit to answer to question in comments:

When you do:

select distinct Name, GroupID 
from Winegroups; 

for a select list it displays name to the user, but stores the groupid. If you wanted to use name, you could do:

select name d, name r 
from winegroups; 

as your winegoup select list query. Then the value in P5_GROUPID would actually be the name and you could join it to winegroups.name in the wines select list query. Of course you should also rename P5_GROUPID if you were going to do this.

But I would probably just leave it the way it is. This way you actually don't need the join in your second query. You could just make it:

select wines.name, wines.wineid 
from wines where groupid = :P5_GROUP_ID.

This would be the cleanest implementation.