Oracle APEX 4.0: Cascading LOVs/Select Lists

Posted on February 27, 2010 by Patrick Wolf

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.

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

Tobias Arnhold on February 28, 2010 at 00:35 said:

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
Milind on February 28, 2010 at 13:38 said:

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.
Zulqarnain on March 1, 2010 at 09:54 said:

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?
Robert on March 1, 2010 at 09:55 said:

Superb. This will save a lot of people a lot of time.
Angelos on March 1, 2010 at 10:16 said:

Hi Patrick,

will the Cascading Lists feature be available for Tabular Forms?
Kishore on March 1, 2010 at 18:15 said:

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.
Patrick Wolf on March 1, 2010 at 18:29 said:

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

Regards
Patrick
Learco brizzi on March 1, 2010 at 23:40 said:

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
Zulqarnain on March 2, 2010 at 09:45 said:

> 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.
Martin Koeb on March 2, 2010 at 13:21 said:

Hi Patrick,

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

Best regards
Martin
Dean Attewell on March 3, 2010 at 04:18 said:

Please make it a std option.. that is so.. clean..
Pingback: Oracle APEX 4.0 – New Features in Early Adopter 2 | Inside Oracle APEX by Patrick Wolf
allen on March 11, 2010 at 07:45 said:

any ETA on the release of APEX 4? can’t wait. =)
lester on March 16, 2010 at 06:08 said:

Is the LOV appearance final? Please make it customizable… Thanks.
Patrick Wolf on March 19, 2010 at 07:49 said:

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

Regards
Patrick
Ram on March 23, 2010 at 20:23 said:

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
brenda on April 9, 2010 at 14:48 said:

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.
Patrick Wolf on April 12, 2010 at 18:43 said:

Are you using APEX 3.x or 4.0?
Simon on April 13, 2010 at 17:43 said:

Hi Patrick,

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

Regards,
Mohammad Talat on September 6, 2010 at 08:01 said:

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
Patrick Wolf on September 6, 2010 at 21:31 said:

Hi,

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

Regards
Patrick
Mohammad Talat on September 7, 2010 at 06:07 said:

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
Drew on October 15, 2010 at 20:24 said:

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
Patrick Wolf on October 20, 2010 at 22:48 said:

Yes that will work as well. Have you checked your SQL statement? What do you use as Null Value?
Limousine New York on October 25, 2010 at 09:25 said:

great website. i learned a lot from your blog posts. i’m going to share this with my friends.
candida on October 27, 2010 at 13:09 said:

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.
Volker on October 28, 2010 at 09:52 said:

Hi Patrick,
declarative cascading LOVs in tabular forms would be a great feature.

regards
Volker
David Costelloe on October 29, 2010 at 14:48 said:

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
Patrick Wolf on October 31, 2010 at 20:48 said:

Hi David,

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

Patrick
David Costelloe on November 1, 2010 at 18:54 said:

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
Patrick Wolf on November 1, 2010 at 19:43 said:

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
Allen on December 13, 2010 at 08:31 said:

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.

Related Posts

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