Monday, December 15, 2008

Cascading LOV's in Oracle ADF 11g Update 1

I was very happy, when have noticed that Oracle have fixed null value bug in LOV component, I have described couple of weeks ago. This bug is fixed in newest JDeveloper 11g release - JDeveloper 11g Update 1. At first, this bug looks quite funny, however it was affecting lots of functionality related to LOV components. One of such examples is Cascading LOV functionality, previously it simply wasn't working, since it was impossible to assign null value to child LOV, if parent LOV value was changed. Now it works fine.

I have developed and posting sample application for Cascading LOV's, you can download - CascadingLOV.zip. Cascading LOV's in 11 are simple, you just need to define View Criteria on dependent LOV component and specify Bind variable. Usage for Bind variable should be specified as Required:


While you will define Bind variable, don't forget to set Display Hint for this variable to Hide. This will allow to hide Bind variable in LOV component and not to render it on UI:


Finally, when you will define LOV for View object attribute, in View Accessor select defined View Criteria and specify value for Bind variable, this will ensure cascading relationship:


In ViewController, don't forget to set AutoSubmit = true for parent LOV's and PartialTriggers for dependent LOV's, you can set EL expression for Disabled property. You will need to create Value Change Listener methods for parent LOV's as well. Those methods will clear values in dependent LOV components:


On runtime, it works pretty nice, here is an example parent LOV and two cascading LOV components. Third LOV is dependent on second and first:


When value in first LOV is selected, second LOV becomes enabled:


Value in second LOV is selected, third LOV becomes enabled:


Finally, value in third LOV is selected:


If parent LOV value is cleared, all dependent LOV's are cleared also, it wasn't working before:


Spanish Summary:

Titulo: Listas de Valores en cascada en ADF 11.

Resumen: Este capitulo muestra como construir listas de valores dependientes una de otra como el clásico (PAIS - DEPARTAMENTO). Andrejus destaca la solución de un bug que tenia esta funcionalidad pero que ha sido solucionada en la versión reciente de la herramienta.

46 comments:

donald said...

I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.

Betty


http://www.my-foreclosures.info

Nilofer said...

"
Finally, when you will define LOV for View object attribute, in View Accessor select defined View Criteria and specify value for Bind variable, this will ensure cascading relationship:
"
Can this be done via Jdeveloper 10.1.3.3. ?
We are still using this version of Jdeveloper and want to implement cascading LOV.
If the above is not possible in 10.1.3.3. what can replace in this version ?

Thanks for creating this blog. Very helpful

Andrejus Baranovskis said...

Hi,

No, this feature is not available in 10.1.3.3. May be, my older post will help you - Three Dependent List Boxes in af:table Component.

Regards,
Andrej

Anonymous said...

Thank you so much for this blog. Can please you post a suggestion on how to implement 2 cascading LOVs with two following requirements:

1) if parent LOV has value selected, child LOV shows only dependent values, BUT when parent LOV value is not selected, then child LOV shows all available values.

2) both LOVs have static LONG lists of values, that are read from an XML file. Do I need to implement ListOfValuesModel? Could you please post a ListOfValuesModel implementation example?


Thank you,
Oksana

Andrejus Baranovskis said...

Hi Oksana,

For 1st, just select Optional usage in View Criteria wizard for Bind Variable from dependent LOV.

For 2nd, I don't have working example at the moment.

Regards,
Andrej

Alex said...

Hi,I'm new one to ADF,and I want to use LOV to setup a input list of values with the data of the FIRST_NAME in HR's Employees.I encountered problems in choosing base data source and list data source.I want just FIRST_NAME in the LOVs,and that will display all FIRST_NAME in the pull-down list box.Would you like to teach me how to do with it?*.*Thank you.

Andrejus Baranovskis said...

Hi,

You can consult ADF Developer Guide - pretty good book.

Regards,
Andrejus

Alex said...

Thanks a lot.

Alex

Oscar said...

Hi

How can i change Your application too:
Raplace: LocationId to Location Name in UI?
I dont want show any ID, just Names.

I want create LOV that return ID in hidden item, but button of lov must by next to "another field -not this id" - just like it was in Oracle Forms

Thanks in advice
Sorry form my english :)

Andrejus Baranovskis said...

Hi Oscar,

I think Steve Muench Oracle Magazine entry will help you - http://www.oracle.com/technology/oramag/oracle/09-jan/o19frame.html

Read "Defining the LOV on a Reference Attribute" chapter.

Regards,
Andrejus

Oscar said...

Thanks a lot, its working!

so i have another question :)
I have few Forms (Oracle Forms 6i) thta use Block based on Stored Procedures and i want rewrite this forms to ADF.
In Oracle Forms every store procedure has in/out parameter of "table type -array"

I canot found anything that resolve this problem, every example use "SIMPLE DATATYPE" (a lot of single -field parameter), dont use TABLE type.

This plsql procedurs are very complexive and almost always need few records to work correctly.

Do you have any idea how can i resolve this in low cost :D

Thanks in advie.

Andrejus Baranovskis said...

Hi,

You should use JPublisher, here is some example - http://www.oraclenerd.com/2009/07/sql-objects-jdeveloper-jpublisher-and.html

Regards,
Andrejus

Roy said...

Hi,

I'm wondering if you can use cascading LOVs within an LOV.

Example: I have a column on a page called "Product." that uses an LOV. The LOV (Let's call is ProductLOV) has Category (using CategoryLOV), Subcategory (using SubCatByPrimaryCatLOV), Product ID and Product Desc. Subcategory should cascade off of Category.

Since this happens in an LOV of Product, I can't perform any of the following:


In ViewController, don't forget to set AutoSubmit = true for parent LOV's and PartialTriggers for dependent LOV's, you can set EL expression for Disabled property. You will need to create Value Change Listener methods for parent LOV's as well.

I would hate to have to create a popup and listener just to perform this lookup functionality.

Andrejus Baranovskis said...

Hi,

In latest release its more simple, you can set autoSubmit=true on VO attribute and define dependency as well between attributes.

Try if it will work for you.

Andrejus

Anonymous said...

I was trying to use this and the table VACATIONREQUESTS is not defined? Do you have this sql to create the table per what is req'd to run this sample? I tried to trace back and it did not appear to be available.

Andrejus Baranovskis said...

Hi,

In Model project, there is Entity - Vacationrequests. Just check attributes from this Entity, and you can create this table - its really basic one.

Regards,
Andrejus

Anonymous said...

I had done that, but for some reason, your page showed no input, so maybe a different issue. However, I did get this to work on my page, by looking at the settings, the only issue is that it ONLY works for inputListOfValues. Why not for any list type? My req. is 2 choice lists the first of which causes the second to refresh w/ different options based on the first. When I switch display types, my list does not render. Is there some other way if you want the same behavior btw choiceLists or comboboxes?

Hasim said...

If i want the same behaviour for Af:Query Component for Vacation then how would i achive this.

Andrejus Baranovskis said...

Hi,

You should enable AutoSubmit directly on View Object attribute. Something similar is described here - http://andrejusb.blogspot.com/2009/07/lov-query-validation-in-query-criteria.html

Regards,
Andrejus

Srikanth said...

Hi Andrejus,

I have cascading LOVs in my application. I have 3 of them. I select values for the first 2 LOVs, and then I click on the 3rd LOV. The query for the third LOV returns no records from the database and so, a NullPointerException is thrown.

When there are no rows to display in the LOV, I want the LOV to still pop up with a message saying "No rows to display".

Is there a way I can achieve this functionality using the existing features?

Thanks
Srikanth Addanki

Andrejus Baranovskis said...

Yes, you should uncheck "Ignore Null Values" checkbox for criteria item.

Regards,
Andrejus

Anonymous said...

what can i do for without setting the property Autosubmit as true.what is the problem in my scenario is when i set the autosubmit property as true it is firing the mandatory fields validation.pls guide me in this.

Huysmans Filip said...

Hi Andrejus,

thanks a lot for this post.
Did you ever try this with a tree table? So putting 2 lov in a tree table structure.
I'm unable to get it working.
Hoping to see your comments on this.

Filip

Anonymous said...

I followed your example, everything worked correct. But when I create Value Change Listener thru backBean, when I type this.getDepartmentidId().setValue(null);
I was not able to get the getDepartmentidId(). How do I do to make those view accessor visible in backBean?

Thanks in advance!

David

Deeepa Pathre said...

I have created LOV , and using 2 bind parameter kile kind, webuserId , But everytime when I click for search my popup shows these bind parameters as required fields for search, I want it to remember bind parameter values as I have already set in while creating view Accessor ,Can anybody help me

megha said...

Hi ,
I have created lOV but all fields changed but the primary key field remains same ,what is he solution

Anonymous said...

Hi Andrejus,

nice blog. Very helpful for newbies like me.

Just a question (most likely stupid), I'm trying to find how to do the same - to have cascade LOVs, say countries and clients but not as a part of third entity. I want to use them as parameters in train activity, not in CRUD.

If I understand correctly in your example we have Locations, Departments and Employees which contains references to Locations and Departments. I would like to do the same but without "the third".

Regards,
Tom

Andrejus Baranovskis said...

Hi,

Yes, its definitely possible. You are facing any issues with this approach?

Andrejus

Andrejus Baranovskis said...

Hi,

Yes, its definitely possible. You are facing any issues with this approach?

Andrejus

Anonymous said...

Hi Andrejus,

thanks for response.

I'm sure the cause of my problems lies in lack of experience in ADF. I expect it must be possible, after all it's basic requirement.

I used your approach in CRUD, so it works fine, I just define LOVs on third entity and d'n'd from Data Control.

But without the third entity I cannot initiate proper wizard in which I would set View Criteria thus passing parameter into dependent view object.

I tried to google it, checked developer guide but couldn't find any information. Or maybe I'm missing something... perfectly possible.

Regards,
Tom

Unknown said...

Hi,
I am using one LOV and one popup which has some input fields used to add new data to LOV. So we have get old data from lov and we can add new data from this popup and once you added new data will be populated in LOV text box using PPR feature, normally it working fine.
But the problem is when we open LOV and click cancel button or close icon thereafter value is not fetched by LOV when I have added new data in popup.It is getting populated when we refresh the page only. Kindly give you idea on this issue.

Anonymous said...

Hi,

Is there any way to create cascade LOV with multiselect in View Criteria.
Ex. If a select multiple Countries from the parent LOV, the child LOV would display the list of States for the selected Countries.

Regards,
Koushik

andreea said...

I have one question regarding LOV.
If I want to have 3 LOV, first 2 are independent, and the third is dependent on the values selected for the first two. How is the best way to handle this case. Is it enough to have LinkViews representing the relashinship between the entities for the 3 LOVs and set corectly the autoSubmit and partialTrigger, or is there somthing else to force the third LOV to execute the ViewCtriteria after the LOV1 and LOV2 have been selected a value?

Thanks,
Andreea

Sameera Samarasinghe said...

Hi Andrejus,
I get error saying that
ORA-00942: table or view does not exist
That is because I don't have Vacationrequests table/view of HR db

Andrejus Baranovskis said...

Yes - you can create it by yourself. Check Vacation Requests EO attributes - create table with same columns.

Andrejus

rakesh menon said...

Hi Andrejus,

Is it possible to use a string type as parent attribute and render the corresponding child attribute as cascade like the way you do with a integer type?

My use case is i need you a string type say a locationName to render countryName which needs to be a cascaded LOV is it possible?

Because i tried doing it but the child attribute was not displaying any values and when i gave a default value for bindLoc as 'Texas' it was showing the corrsponding child values but without any value its not working at all..

Have you ever been in a situation?

Thanks.

Andrejus Baranovskis said...

This should work with no problem, unless your SQL is not correct. Enable detail logging and check what SQL is generated.

Andrejus

rickymax said...

Andreus, unfortunately this is not supported with postgres because the driver does not support the character '?' representing the input variable, for example:

SELECT PnSubtipoArticuloEO.sa_codigo, PnSubtipoArticuloEO.sa_tipo, PnSubtipoArticuloEO.sa_nombre, PnSubtipoArticuloEO.sa_estado, PnSubtipoArticuloEO.sa_stock_min_estimado
PnSubtipoArticuloEO pn_subtipo_articulo FROM WHERE (((PnSubtipoArticuloEO.sa_tipo =?)))

You know how to solve this with postgres and ADF?

sagar said...

Ihave an updatrable view object in which two lovs tablename,columnname are there. Column name is dependent on table name. By selecting table name column name should be populated and that is working fine. But while commiting the updatable view object i am not getting any value in the columnname attribute. it is inserting null. what can i do for this?

Thomas said...

my LOVs work just fine in edit mode. when i create a new record the dependent LOV doesn't populate upon selecting the parent. any clue why not? i'm running JDeveloper 11.2.3.0.

Andrejus Baranovskis said...

Hi,

I will check this for you and reply tomorrow...

Andrejus

Andrejus Baranovskis said...

Hi Thomas,

I have tested on JDeveloper 11.2.3.0 - it works for me, I recreated this app.

You are testing with form or table?

Andrejus

Thomas said...

i have a parent table; it's working fine. then i have a form below with the various controls. upon clicking create, it blanks the form. upon changing the region LOV it blanks the district without populating it.
here's XML:






Andrejus Baranovskis said...

Please send sample app to me for the test. Anyway, I plan to post updated cascading LOV app soon on the blog.

Andrejus

infantafdo said...

HI,
I have a table, in one column I have defined the LOV. the List type of that is inputtextwithLOV. Based on the LOV selection, I want to set the other columns values. How to do this?

Anonymous said...

Hi Andrejus,

I Have question . i have dependent Lov for parent list of value and child list of value . when first time change parent LOV and change depent child Lov also change save the record. again go to change child LOv change not Paraent Lov. if i'm going to save the record only save parent not child. could find any solution.

Thanks