Creating a 'select list which support dynamic actions' using the 'on change' dynamic action of another select list in Oracle APEX 5.1

Question

I am developing an application using Oracle APEX 5.1

I have a requirement, where, based on the value selected in the first select list, a second select list should be populated. Based on second list value, a third list should be populated and so on till we reach the lowest level in the hierarchy.So the no.of select lists will be equal to the no.of levels in the hierarchy.

Also, these select list should be dynamic select lists which supports SQL query.

Since the no.of levels in the hierarchy are large, I think we need to create the select lists for different levels dynamically. I couldn't find any helping documents regarding this.

Answer 1

If the number of select lists depends on the values chosen about it you could use the dynamic pl/sql region type in APEX to draw the select boxes by using the apex_item.select_list function.

See API documentation: https://docs.oracle.com/cd/E71588_01/AEAPI/APEX_ITEM.htm#AEAPI192

I am currently doing this at work to submit reports of varying parameters where each report could have multiple select list fields.

Answer 2

Unless I'm wrong, all you have to do is to set Select List item's Cascading LOV Parent Item(s) property.

Apex help says:

Enter page or application items that trigger the refresh of this list of values. For multiple items, separate each item name with a comma. You can type in the name or pick from the list of available items. If you pick from the list, and there is already text entered, then a comma is placed at the end of the existing text, followed by the item name returned from the list.

This list of values is refreshed whenever the value of any of the specified items are changed on this page.

You should use the page item(s) specified in this attribute within the where clause of this list of values SQL Query to restrict the values returned.

For example, if you have page items for Car Make and Model, you could specify Car Make as the Cascading LOV Parent Item within the List of Values definition for Model, and use Car Make within the where clause for that list of values. Then whenever Car Make is changed, the LOV for Model is refreshed, and the list only displays values for the currently selected Car Make.