Oracle APEX 4.0: Cascading LOVs/Select Lists

One of the new features of Early Adopter 2 are Cascading LOVs/Select Lists. I’m pretty sure that almost every APEX developer had already the requirement to refresh a child select list when a parent select list was changed. For example you pick a department in the first select list and the second should just show employees of that department. There are two solutions to solve that:

  1. Use a “Select List with Submit” for the department field, which will automatically submit and refresh the page when a department is picked. In the Web 2.0 world that’s a little bit outdated and it can also takes quite long to refresh the full page. You also have to take care that your validations and processes do not fire in that specific case. So quite a number of extra conditions are necessary to deal with the auto submit. This solution doesn’t really make the page more maintainable and the user experience is also not very great.
  2. But you have heard about the holy grail AJAX anyway. It’s the way to go! The idea is to just refresh the child select list by calling the server to get a restricted list of employees and use browser DOM manipulation to replace the existing select list with the new values. Much better user experience! No full page refresh, the employee select list just magically changes. But it doesn’t come for free. If you have a look at all the Cascading LOV examples which are out there you will notice that you have to write quite a bit of JavaScript code and an On-Demand process which duplicates your LOV statement. So this solution offers great user experience but again makes your page hard to maintain. And I’m sure it will takes you more than 5 minutes to implement that solution…

So both solutions are not really satisfying because the require quite a bit of coding, which makes the application harder to maintain. But with Oracle Application Express 4.0 there is now a third solution for the problem. It gives the user a great UI experience and doesn’t require any additional coding!

Declarative “Cascading LOV” support for all “List of Values” based item types! That means select list, checkbox, radio group and shuttle based page items can automatically be refreshed if a parent field gets changed. (Note: Popup LOV and List Manager will be added as well in the final product) Important to mention here is that the parent field doesn’t have to be a select list, it can be a simple text field, date picker, … as well. Every DOM element which fires a change event will work as parent.

To add “Cascading LOV” support for a child page item, you just have to set the new “Cascading LOV Parent Item(s)” attribute in the “List of Values” section to the name of your parent page item. That’s it! This will tell APEX to automatically refresh the page item with new values each time the parent item gets changed.

Cascading LOV settings

So let’s create a simple example where we first select a department and then an employee in that department.

  1. Create page item P2_DEPTNO of type “Select List” with the following LOV statement
    select dname,
           deptno
      from dept
     order by 1
  2. Create page item P2_EMPNO of type “Select List” with the following settings
    • Cascading LOV Parent Item(s): P2_DEPTNO
    • List of values definition:
      select ename,
             empno
        from emp
       where deptno = :P2_DEPTNO
       order by 1

That’s all! Super simple as it should be.

If you have a look at the screenshot above you will see that Cascading LOVs have the attributes “Page Items to Submit” and “Optimize Refresh” as well. Most time you will probably not need to set them. For details about them consult the page item level help.

Check out my New Features Sample Application for the above example and a more advanced one with multiple levels and a shuttle.

32 thoughts on “Oracle APEX 4.0: Cascading LOVs/Select Lists

  1. This problem sounds familiar to me! ;D I will check this out soon…

    Thanks Patrick for all the effort you are putting into the new APEX release.

    Tobias

  2. I like this feature as I used to struggle a lot for doing same thing in HTML DB 2.0 or Apex 3.1.2 using Ajax.

    It makes more similar to Oracle Forms LOV.

    Thanks Patrick for highlighting helpful feature of Apex 4.0 as I miss even after looking EA2 release.

  3. Hi

    In our applications in Apex 3.x we used your ApexLib and that solved this problem for us for select lists. But there was a limitation that it did not work for tabular forms. Is this limitation also with Apex 4?

  4. Hi Patrick,

    will the Cascading Lists feature be available for Tabular Forms?

  5. Great feature. Cascading LOVs are lot easier with this.
    First time I run the page, Employee LOV doesnt show results. It is being refreshed when change department.

  6. Not sure yet if cascading LOV support for tabular forms will make it into APEX 4.0. But we will try hard.

    Regards
    Patrick

  7. > Not sure yet if cascading LOV support for tabular forms will make it into APEX 4.0. But we will try hard.

    Please make in available in Apex 4, since Tabular forms are considered a little weakness in Apex. I mean they are not as powerful and easy as the non-tabular forms. Making cascading LOV available in tabular forms will provide some ease.

  8. Hi Patrick,

    a great job – that’s what will make among others the APEX development easier and more efficient :-)

    Best regards
    Martin

  9. Pingback: Oracle APEX 4.0 – New Features in Early Adopter 2 | Inside Oracle APEX by Patrick Wolf

  10. What do you exactly mean by customizable? Because you can already change it bu modifying the Popup LOV template of your theme.

    Regards
    Patrick

  11. Patrick,

    That’s great feature. Will this work on Table based cascading select LOV’s? I mean for each row like this.

    Dept (select list) Emp (select list)
    HR Miros, Steve
    OPS Stic, Pat

    etc

    Ram

  12. I have a dependent select list. When I choose a selection from the parent list, the page submits and then give me values for the child list, which is what I want. However, when I select from the parent list, I get an error that the child list must have a value. How do I avoid the error as it is a false error.

  13. Hi Patrick,

    Is the Apex 4.0 will support new DYNAMIC ACTION in tabular form?

    Regards,

  14. if i have item more than one parent items;and i need refresh evre time selcte parent items

    ex:

    seelct emp_no,emp_name
    from employee
    where emp_mng = :Mng_ID
    and emp_dept= :dept_id

  15. Hi,

    just specify MNG_ID,DEPT_ID in the “Cascading Parent Items”. That should do what you want.

    Regards
    Patrick

  16. thanx Patrick Wolf;

    u need enter tow value mng_id and dept_id
    me qustion if i want enter mng_id only and dept_id is null,
    must tow value is not null;
    if u set Optimize Refresh = no its work but some time get long time or clear anther list of value dosnt working good…..

    Regards

  17. Patrick,

    Can this functionality be used for cascading shuttles where both items are shuttles? I am getting an ‘invalid number’ error when attempting to do that.

    Drew

  18. Yes that will work as well. Have you checked your SQL statement? What do you use as Null Value?

  19. great website. i learned a lot from your blog posts. i’m going to share this with my friends.

  20. I am not actually certain if top practices have emerged around stuff like that, other than I am definite that your huge job is obviously discovered. I was wondering if you offer some registration toward your RSS feeds since I would be extremely interested.

  21. Hi Patrick,
    I am using the above on an addresses with country/states etc. One thing I noticed is I populate the name address etc and when it comes to the list of countries and the selection is Canada the second list (states) has cascading lov to the country ID.
    The big issue is that when I change a country item the list is populated as expected but the complete (create item) information is all cleared by the cascade. Is there a way to not clear all the data when just changing a country?

    Thanks

  22. Hi David,

    what do you mean with “the complete (create item) information is all cleared”? Do you mean the default value?

    Patrick

  23. I have a form with

    Name
    Address
    City
    Country (lov)
    Province( LOV cascading to Country)
    Postal Code

    On Create in Apex 4.0
    I add text to Name, address, city
    When I select the country dropdown the page submits and wipes out the text entered in the Name,Address and City field. The correct LOV is loaded fine.

    Cheers
    David

  24. Hi David,

    it looks like that you have set the select list for country to “Submit Page”. Just set it to “None” and set the “Cascading LOV Parent Items” for your province field instead.

    Regards
    Patrick

  25. Hi Patrick,

    Is it not possible to have more than two columns for the list of values? Also, in cases like if the list of values is querying against a lookup table in which each record will be default values for a transaction table i.e.

    training_courses table (lookup)
    course_id number
    course_name varchar2(100)
    no_of_hours number
    price number
    instructor varchar2(100)

    trainings table (transaction)
    training_id number
    course_id number
    no_of_hours number
    price number
    instructor varchar2(100)

    In the situation above, I just use the training_courses table to fetch default values for the columns no_of_hours, price, instructor in the table trainings. Can you suggest an approach I can use for this requirement?

    Thanks
    Allen

Comments are closed.