CREATING A CASCADING LIST OF VALUES

Introduction

A Cascading List of Values (LOV) in Oracle APEX is a powerful feature that allows you to filter one LOV based on the selection made in another. This is especially useful when you have a parent-child relationship between data sets—for example, selecting a country to filter available states, or choosing a department to filter employees. Using cascading LOVs improves user experience by narrowing down choices and ensuring data relevance. In this blog, we’ll walk through how to create cascading LOVs in Oracle APEX with practical examples, detailed steps, and best practices to guide your implementation.

How to Create a Cascading List of Values – Step-by-Step

Scenario Example:
We want to create a form with two select lists:

• One for Department

• One for Employee that only shows employees from the selected department.

Step 1 – Create the Parent LOV (Department Select List)

1. Open your page in Page Designer.

2. Create a new item:

   • Type: Select List

   • Name: P1_DEPT_ID

3. Under the List of Values section:

   • Type: SQL Query

   • SQL Query:

     SELECT department_name d, department_id r FROM departments ORDER BY 1
     

4. Leave the other settings as default or customize as needed.

Step 2 – Create the Child LOV (Employee Select List)

1. Add a new item:

   • Type: Select List

   • Name: P1_EMP_ID

2. Under List of Values:

   • Type: SQL Query

   • SQL Query:

     SELECT employee_name d, employee_id r 
     FROM employees 
     WHERE department_id = :P1_DEPT_ID 
     ORDER BY 1
     

3. Set the Cascading LOV Parent Item(s) to P1_DEPT_ID.

4. Optionally, set the Default Value or placeholder text to something like - Select Employee -.

5. In the Settings section, make sure Reload On Change is enabled for P1_DEPT_ID.

Step 3 – Run and Test the Application

• Run the page and select a department from the first list.

• The second select list should refresh and only show employees from the selected department.

Extensive Example – Countries and States
Country LOV (P1_COUNTRY_ID)

SELECT country_name d, country_id r 
FROM countries 
ORDER BY 1

State LOV (P1_STATE_ID)

SELECT state_name d, state_id r 
FROM states 
WHERE country_id = :P1_COUNTRY_ID 
ORDER BY 1

Configuration:

• State LOV is set to cascade from P1_COUNTRY_ID

• Make sure P1_COUNTRY_ID has “Submit when value changed” or “Item is re-evaluated on change”.

By creating a cascading list of values (LOV), one item on a page determines the list of values for another item. App Builder includes cascading LOV support for the following item types: Select List, Shuttle, Checkbox, Radio Group, Popup LOV, and List Manager.

You create a cascading LOV by creating two items. To populate the second item, you specify a Cascading LOV Parent Item when running the Create Item Wizard or by editing the item's attributes.

The following example demonstrates how to create two related select lists where the value of the first list populates the second.

To create a cascading list of values:

Create the first item, P1_DEPTNO:

Create a select list item.

In the Property Editor, edit the attributes:

Identification: Name - Enter P1_DEPTNO

Identification: Type - Select Select List.

Label: Label - Enter Department

In the Property Editor, edit the List of Values attributes:

List of Values: Type - Select SQL Query.

List of Values: SQL Query, enter:

SELECT dname as d,

       deptno as r

  FROM dept

 ORDER BY dname

List of Values: Display Null Value - Select On.

List of Values: Null Display Value - Enter

- Select Department -

Accept the remaining defaults.

Click Save.

Create the second item, P1_EMPNO:

Create a select list item.

In the Property Editor, edit the attributes:

Identification: Name - Enter P1_EMPNO

Identification: Type - Select Select List.

Label: Label - Enter Employee

In the Property Editor, edit the List of Values attributes:

List of Values: Type - Select SQL Query.

List of Values: SQL Query, enter:

 

SELECT ename as d,

       empno as r

  FROM emp

 WHERE deptno = :P1_DEPTNO

List of Values: Display Null Value - Select On.

List of Values: Null Display Value - Enter:

- Select Employee -

Accept the remaining defaults.

Click Save.

To save and run the page, click Save and Run Page.

Two select lists appear. Making a selection in the Department select list, determines which individuals display in the Employees select list.

Best Practices

• Use bind variables properly (:P1_PARENT_ITEM) in child LOVs to ensure correct filtering.

• Enable session state for parent items so child items can read the current value.

• Use meaningful display and return values for clarity and accuracy.

• Test with NULL and default values to ensure the UI behaves as expected.

• Group cascading LOVs logically on the page for better UX.

• Keep LOV queries optimized—use indexes and avoid unnecessary joins or filters.

• Avoid overusing cascading LOVs in one form, as they can become complex to maintain.

Oracle APEX Documentation Link
Oracle APEX – Cascading LOVs Documentation

Conclusion
Cascading Lists of Values in Oracle APEX allow developers to create dynamic, dependent form fields that enhance user experience and ensure relevant data selection. Whether you're building master-detail forms or refining input accuracy, cascading LOVs make applications more intuitive and efficient. With proper setup, optimization, and testing, this feature can significantly improve the usability and reliability of your APEX applications.