How to Dynamically Update Three Dependent Select Lists?
When working with Oracle APEX applications, it's common to use dependent "Select Lists" (dropdowns) to filter data dynamically. This guide explains how to configure three Select Lists so they update automatically based on their dependencies: year, month, and day.
Overview of the Select Lists
The setup involves the following Select List items:
- P140_YEAR: Displays available years.
- P140_MONTH: Displays months based on the selected year.
- P140_DAY: Displays days based on the selected month.
By establishing dependencies between these Select Lists, they will dynamically update whenever a parent item changes.
Step-by-Step Implementation
1. Create the Select List Items
- P140_YEAR: The parent Select List containing an SQL statement to retrieve available years.
- P140_MONTH: A dependent Select List linked to
P140_YEAR. It updates whenever the year changes. - P140_DAY: A dependent Select List linked to
P140_MONTH. It updates whenever the month changes.
2. Configure the SQL Statements
Each Select List requires specific SQL logic to handle dependencies:
- Year (P140_YEAR):
Retrieves a list of years.sql WITH Month AS ( SELECT TRUNC(SYSDATE - (360 * 5), 'Year') AS min_start_date, MAX(SYSDATE) AS max_end_date FROM dual ) SELECT TO_CHAR(my_month, 'dd.mm.YYYY') AS d, my_month AS r FROM ( SELECT ADD_MONTHS(min_start_date, LEVEL * 12) AS my_month FROM Month CONNECT BY LEVEL * 12 < MONTHS_BETWEEN(max_end_date, min_start_date) + 2 ) ORDER BY my_month DESC;- Month (P140_MONTH):
Retrieves a list of months based on the selected year.sql WITH Month AS ( SELECT MIN(:P140_YEAR) AS min_start_date, MAX(TO_DATE(:P140_YEAR) + 330) AS max_end_date FROM dual ) SELECT TO_CHAR(my_month, 'Month YYYY') AS d, my_month AS r FROM ( SELECT ADD_MONTHS(min_start_date, LEVEL - 1) AS my_month FROM Month CONNECT BY LEVEL < MONTHS_BETWEEN(max_end_date, min_start_date) + 2 ) ORDER BY my_month ASC;- Day (P140_DAY):
Retrieves a list of days based on the selected month.sql SELECT TO_CHAR(TO_DATE(:P140_MONTH, 'DD.MM.YYYY') + LEVEL - 1, 'DD.MM.YYYY') AS d, TO_DATE(:P140_MONTH, 'DD.MM.YYYY') + LEVEL - 1 AS r FROM dual CONNECT BY LEVEL <= TO_DATE(:P140_MONTH, 'DD.MM.YYYY') + 30 - TO_DATE(:P140_MONTH, 'DD.MM.YYYY');
3. Configure the Dependencies
To ensure the Select Lists update dynamically:
- For P140_MONTH:
- Navigate to the "List of Values" tab.
- In the "Cascading LOV Parent Item(s)" field, specify
P140_YEAR. - For P140_DAY:
- In the "List of Values" tab, set
P140_MONTHas the parent item in the "Cascading LOV Parent Item(s)" field.
Key Points to Remember
- Cascading LOV Parent settings ensure automatic updates when a parent item changes.
- SQL statements must reference parent Select Lists to filter the data dynamically.
- Validate your SQL logic to confirm that the values display correctly.
Why Use Dependent Select Lists?
- Improved User Experience: Streamlines the user interface by showing relevant options only.
- Data Accuracy: Ensures users can only select valid combinations of year, month, and day.
- Dynamic Interactivity: Automatically updates options based on user input, reducing errors.
Conclusion
With this setup, your Select Lists will dynamically update based on user selections, providing a seamless and interactive experience in your Oracle APEX application. Implement these steps to enhance the usability and functionality of your project.
{fullWidth}