How Do I Use Cascading LOVs (Parent-Child Relationship)

 Introduction

Cascading LOVs in Oracle APEX allow you to create a parent-child relationship between two select lists or other LOV-based components. When the user selects a value in the parent list, the child list is automatically filtered based on that selection. This approach makes forms more dynamic and user-friendly by narrowing down options, preventing invalid combinations, and improving data accuracy. In this blog, we’ll walk through how to set up cascading LOVs step by step in an APEX form.

Using cascading LOVs (List of Values) in Oracle APEX is an effective way to filter one select list based on the user’s choice in another. This parent-child relationship between LOVs enhances usability and helps enforce data consistency. Let’s walk through how to set up cascading LOVs using a practical example where a user first selects a region, then selects an earthquake event from that region.

Step 1: Create the Parent LOV (Region)
First, we need a list of available regions for the user to choose from.

1. Go to Shared Components > List of Values.

2. Click Create > From Scratch.

3. Name the LOV: LOV_Regions.

4. Choose Dynamic as the source type.

5. Enter the following SQL query:

SELECT DISTINCT REGION AS display_value, REGION AS return_value  
FROM USCG_DATA  
ORDER BY REGION;

6. Save the LOV.

Next, assign this LOV to a select list item on your page:

• Go to the desired page in Page Designer.

• Create or select a page item named P1_REGION.

• Set the Type to Select List.

• In the List of Values section, select LOV_Regions.

• Save your changes.

Step 2: Create the Child LOV (Earthquake Events Based on Region)
Now, you will create the second LOV, which filters events based on the region selected in P1_REGION.

1. Go to Shared Components > List of Values.

2. Click Create > From Scratch.

3. Name this LOV: LOV_Earthquake_Events_By_Region.

4. Choose Dynamic as the source type.

5. Enter the following SQL:

SELECT PROPERTIES_TITLE AS display_value, ID AS return_value  
FROM USCG_DATA  
WHERE REGION = :P1_REGION  
ORDER BY PROPERTIES_TITLE;

6. Save the LOV.

Next, assign this LOV to a second page item:

• In Page Designer, create or select the page item P1_EVENT_ID.

• Set the Type to Select List, Popup LOV, or another LOV-compatible item.

• In the List of Values section, choose LOV_Earthquake_Events_By_Region.

• Set the Cascading LOV Parent Item to P1_REGION.

• Enable Submit when Value Changed on the P1_REGION item to refresh dependent items.

• Set P1_EVENT_ID to Refresh when P1_REGION changes (can be done using a Dynamic Action or automatic cascade option).

• Save and run your page.

Now, when a user selects a region, the second dropdown will automatically filter to show only the earthquake events for that region.

Final Notes
Cascading LOVs are an essential part of building interactive Oracle APEX applications. They allow you to:

• Control the flow of user input

• Reduce errors by narrowing user choices

• Connect related data fields logically and cleanly

Use Static LOVs for fixed options, Dynamic LOVs for query-driven lists, and Cascading LOVs when one list’s values depend on another. With these techniques, you can build forms that are not only functional but also user-friendly and intelligent.

In the application, select “Shared components”

Select the “Other Components” > “List of Values”

Select  and click on “Create” to create a List of Value “LOV”

Select from Scratch

Name the LOV

Select how you want the list to be filled.

Identify what you want to display…this is from the DEPT table

Finally there is a LOV that can be used in the Form’s controls.

Conclusion

Using cascading LOVs in Oracle APEX helps streamline user input by dynamically filtering available options based on earlier selections. This technique not only improves usability but also ensures better control over data entry. By implementing a parent-child LOV relationship, you create a more intuitive and responsive interface that adjusts in real time to user choices.