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:
- 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.
- 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.
So let’s create a simple example where we first select a department and then an employee in that department.
- Create page item P2_DEPTNO of type “Select List” with the following LOV statement
select dname, deptno from dept order by 1 - 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.
February 28th, 2010 at 00:35
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
February 28th, 2010 at 13:38
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.
March 1st, 2010 at 09:54
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?
March 1st, 2010 at 09:55
Superb. This will save a lot of people a lot of time.
March 1st, 2010 at 10:16
Hi Patrick,
will the Cascading Lists feature be available for Tabular Forms?
March 1st, 2010 at 18:15
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.
March 1st, 2010 at 18:29
Not sure yet if cascading LOV support for tabular forms will make it into APEX 4.0. But we will try hard.
Regards
Patrick
March 1st, 2010 at 23:40
Thanks! I used it a lot in 3.x. Every time it costs a lot of coding. This makes it just the way we want it.
Learco
March 2nd, 2010 at 09:45
> 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.
March 2nd, 2010 at 13:21
Hi Patrick,
a great job – that’s what will make among others the APEX development easier and more efficient
Best regards
Martin
March 3rd, 2010 at 04:18
Please make it a std option.. that is so.. clean..
March 11th, 2010 at 07:45
any ETA on the release of APEX 4? can’t wait. =)
March 16th, 2010 at 06:08
Is the LOV appearance final? Please make it customizable… Thanks.
March 19th, 2010 at 07:49
What do you exactly mean by customizable? Because you can already change it bu modifying the Popup LOV template of your theme.
Regards
Patrick
March 23rd, 2010 at 20:23
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
April 9th, 2010 at 14:48
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.
April 12th, 2010 at 18:43
Are you using APEX 3.x or 4.0?
April 13th, 2010 at 17:43
Hi Patrick,
Is the Apex 4.0 will support new DYNAMIC ACTION in tabular form?
Regards,