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.
at

46 comments:

  1. UnknownApril 14, 2009 at 3:05 PM

    "
    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

    ReplyDelete
  2. Andrej BaranovskijApril 14, 2009 at 8:28 PM

    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

    ReplyDelete
  3. AnonymousApril 23, 2009 at 10:56 PM

    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

    ReplyDelete
  4. Andrej BaranovskijApril 25, 2009 at 12:13 PM

    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

    ReplyDelete
  5. AlexMay 4, 2009 at 4:55 PM

    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.

    ReplyDelete
  6. Andrej BaranovskijMay 4, 2009 at 7:37 PM

    Hi,

    You can consult ADF Developer Guide - pretty good book.

    Regards,
    Andrejus

    ReplyDelete
  7. AlexMay 5, 2009 at 3:40 AM

    Thanks a lot.

    Alex

    ReplyDelete
  8. OskarNovember 5, 2009 at 12:51 PM

    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 :)

    ReplyDelete
  9. Andrej BaranovskijNovember 5, 2009 at 1:33 PM

    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

    ReplyDelete
  10. OskarNovember 5, 2009 at 9:08 PM

    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.

    ReplyDelete
  11. Andrej BaranovskijNovember 5, 2009 at 9:37 PM

    Hi,

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

    Regards,
    Andrejus

    ReplyDelete
  12. RoyFebruary 18, 2010 at 9:04 PM

    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.

    ReplyDelete
  13. Andrej BaranovskijFebruary 18, 2010 at 9:07 PM

    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

    ReplyDelete
  14. AnonymousFebruary 24, 2010 at 4:43 AM

    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.

    ReplyDelete
  15. Andrej BaranovskijFebruary 24, 2010 at 8:14 AM

    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

    ReplyDelete
  16. AnonymousFebruary 25, 2010 at 4:12 AM

    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?

    ReplyDelete
  17. HasimApril 6, 2010 at 4:58 PM

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

    ReplyDelete
  18. Andrej BaranovskijApril 6, 2010 at 6:28 PM

    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

    ReplyDelete
  19. UnknownOctober 1, 2010 at 8:18 AM

    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

    ReplyDelete
  20. Andrej BaranovskijOctober 1, 2010 at 8:44 AM

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

    Regards,
    Andrejus

    ReplyDelete
  21. AnonymousDecember 7, 2010 at 9:14 AM

    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.

    ReplyDelete
  22. Huysmans FilipMarch 24, 2011 at 6:06 PM

    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

    ReplyDelete
  23. AnonymousMay 7, 2011 at 1:37 AM

    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

    ReplyDelete
  24. Deeepa PathreMay 27, 2011 at 9:35 AM

    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

    ReplyDelete
  25. meghaJune 7, 2011 at 1:20 PM

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

    ReplyDelete
  26. AnonymousJune 21, 2011 at 11:22 AM

    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

    ReplyDelete
  27. Andrej BaranovskijJune 21, 2011 at 11:19 PM

    Hi,

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

    Andrejus

    ReplyDelete
  28. Andrej BaranovskijJune 21, 2011 at 11:19 PM

    Hi,

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

    Andrejus

    ReplyDelete
  29. AnonymousJune 22, 2011 at 10:07 AM

    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

    ReplyDelete
  30. Java BuddyAugust 3, 2011 at 2:42 PM

    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.

    ReplyDelete
  31. AnonymousAugust 30, 2011 at 3:28 PM

    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

    ReplyDelete
  32. andreeaAugust 31, 2011 at 3:07 PM

    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

    ReplyDelete
  33. UnknownJune 18, 2012 at 11:41 AM

    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

    ReplyDelete
  34. Andrej BaranovskijJune 18, 2012 at 8:41 PM

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

    Andrejus

    ReplyDelete
  35. UnknownJune 19, 2012 at 8:28 AM

    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.

    ReplyDelete
  36. Andrej BaranovskijJune 19, 2012 at 6:44 PM

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

    Andrejus

    ReplyDelete
  37. AnonymousSeptember 13, 2012 at 4:59 AM

    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?

    ReplyDelete
  38. SagarOctober 22, 2012 at 7:53 AM

    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?

    ReplyDelete
  39. ThomasOctober 30, 2012 at 5:02 PM

    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.

    ReplyDelete
  40. Andrej BaranovskijOctober 30, 2012 at 5:58 PM

    Hi,

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

    Andrejus

    ReplyDelete
  41. Andrej BaranovskijOctober 31, 2012 at 2:30 PM

    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

    ReplyDelete
  42. ThomasOctober 31, 2012 at 4:48 PM

    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:






    ReplyDelete
  43. Andrej BaranovskijNovember 1, 2012 at 6:20 AM

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

    Andrejus

    ReplyDelete
  44. infantafdoDecember 14, 2012 at 7:06 AM

    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?

    ReplyDelete
  45. AnonymousFebruary 8, 2013 at 7:21 AM

    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

    ReplyDelete
  46. UnknownSeptember 23, 2015 at 10:45 AM

    hi Andrejus,

    thanx for the post. but i have a different requirement . i need a lov in tablecontainer that should be dependent on a lov that is in form container.
    when i created it and tried to run it on AM it is working fine. but when i opened in it page it did not work.could you tell me any solution for this.

    ReplyDelete