Interactive Grid Cascading LOVs: Dependent Select Lists That Work

The Challenge

Cascading LOVs (where the values in one select list depend on the selection in another) are straightforward on a standard APEX form page but require extra configuration in Interactive Grids. The challenge is that IG cells are rendered dynamically as the user scrolls and edits, so the parent-child LOV relationship must work at the cell level within the grid’s editing framework.

Setting Up the Parent Column

Create the parent column (e.g., COUNTRY) as a Select List item type in the IG. Define its LOV as a static or dynamic list of countries. Set the column’s Static ID to something memorable like COUNTRY.

Setting Up the Child Column

Create the child column (e.g., CITY) as a Select List. In the LOV definition, write the query to filter by the parent value:

SELECT city_name AS d, city_id AS r
  FROM cities
 WHERE country_id = :COUNTRY
 ORDER BY city_name;

In the child column’s settings, set the “Cascading LOV Parent Column(s)” property to the parent column name (COUNTRY). This tells APEX to refresh the child LOV whenever the parent value changes.

Why It Sometimes Fails

The most common reasons cascading LOVs fail in IGs are: the parent column name in “Cascading LOV Parent Column(s)” does not match the actual column alias in the SQL query, the LOV query uses a bind variable name that does not match the parent column name, or the parent column is not set as a Select List type (it must be a select list, not a plain text column, for the cascade to trigger).

Handling the Bind Variable

The bind variable in the child LOV query must match the parent column’s name from the IG source query. If your source query aliases the column as COUNTRY_CODE, then the LOV query must use :COUNTRY_CODE and the Cascading LOV Parent must reference COUNTRY_CODE:

-- IG Source
SELECT order_id, country_code, city_id, ...
  FROM orders

-- City LOV (child)
SELECT city_name d, city_id r FROM cities
 WHERE country_code = :COUNTRY_CODE

Multi-Level Cascades

You can chain cascading LOVs: Country drives State, State drives City. Each level references its immediate parent. APEX handles the chain automatically, refreshing each child when its parent changes. Keep the chain shallow (three levels maximum) because each level adds a server round-trip when the user changes a value, which can feel sluggish with more levels.

Clearing Child Values

When the parent value changes, the child LOV refreshes, but the child cell may retain an old value that is no longer valid. Set the column’s “Include Null Value” to Yes and the “Null Display Value” to a placeholder like “– Select –“. If you need to automatically clear the child value when the parent changes, add a Dynamic Action on the IG’s Column Change event that checks if the changed column is the parent and clears the child.