Saturday, November 3, 2012

Cascading LOV's in JDeveloper 11.1.2.3.0

Blog reader was asking about cascading LOV's support in JDeveloper 11.1.2.3.0 (latest release). Question was based on my old post from 2008 - Cascading LOV's in Oracle ADF 11g Update 1. Things changed since then - its easier to implement cascading LOV's with the latest JDeveloper 11g releases. I decided to update old sample application and describe how you can implement such functionality using ADF BC declarative tools.

Here is updated sample application - CascadingLOV_v2.zip. This sample is based on custom table - Vacationrequests (sql is script is provided). Two cascading LOV's are implemented with one master LOV (Locations -> Departments -> Employees):


There is no need to perform any additional configuration for ADF UI (opposite from before), cascading LOV field will be refreshed automatically. Here we are creating new record and cascading LOV works for the new record:


We change master LOV value to be 1500:


Dependent LOV values for Departments -> Employees are cleared automatically:


Same works for ADF table component, change master LOV to be 1800:


Dependent columns are cleared - bind variable value is reset:


Now I will present 4 steps, describing how this functionality is implemented.

Step 1

Dependent LOV VO must have View Criteria, it filters based on bind variable value:


Step 2

View Accessor from main VO (Vacationrequests) contains bind variable and sets value from the parent:


Step 3

Set Auto Submit = True for master attribute on VO level. This allows to simulate AutoSubmit behavior on ADF UI and reset dependent LOV bing variable value:


Step 4

Set dependency on VO level for cascading LOV attribute from the parent - this will force dependent LOV to clear its value after changes in master LOV:


8 comments:

Anonymous said...

Hello!
I try you example on my app. View criteria not applied if LOV view from shared AM (Jdev 11.1.1.6). Please, can you check it?

Andrej Baranovskij said...

Why would you use AM for LOV instance? LOV instance is declared directly on VO and criteria is applied there.

Andrejus

Anonymous said...

Hi Andrejus,

Thanks Really use full post ... But i want to customize the InputTest value form id to name..While inserting into DB it should insert ID.. same as select one choice..But my req is to use input text with LOV. can u plz help me on this ?

Thnaks Nitesh

Anonymous said...

Hi Andrejus,

Great Blog!!

I am trying to implement a create page i used the createinsert action. On the new form I have LOVs but when i select a value in LOV I get error that "Could not find selected item matching value". I suppose Its taking the position of LOV item. How can I fix it?

Thanks in advance!!
Sumit

kiran said...

Hi Andrejus,

I tried this thing in my UI. I added the dependency of parent lov on child. But in UI i see that whenever i change parent lov the child lov isnt refreshing to null. It is still holding the old value and trying to set the lov index to that value. I am not sure what exactly the issue is.

Anonymous said...

Hi Andrejus,

I have found very usefull information in your blog and for that thank you!

I encountered a problem that had troubled me when creating a cascading dropdown list with 3 levels and the problem was not in the solution but in the Jdeveloper.

As you know several times we develop queries in the SQLDeveloper or equivalent and then copy and paste it to create a view object.

BE AWARE!

When you copy and paste the query on a view object/view criteria always press "Test and Explain" or it will give you and SQL error :

"ORA-00907: missing right parenthesis

Exception: oracle.javatools.db.sql.InvalidSQLException"

This error disappears when I remove all the "formatting spaces" and put the query in only one line.

It is an easy fix (and I dont know if you ever encountered it) but it gave me a big headache to me (noob in ADF)


Thanks for your dedication to the blog as it helps a lot of people!

Cheers!

Unknown said...

Hello! I have a problem. I've done project as it written here. In tester works, but on page have troubles. Parent LOV works. Items in Child LOV refreshes, but after choose appear message 'have no choice'.
What can it be? Help please. Thanks in advance

Unknown said...

How can I accomplish this when having a many to many relationship?