Thanks to Patrick Wolf
One of the new features of Apex 4 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.
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.