Friday, November 6, 2009

Defining the LOV on a Reference Attribute in Oracle ADF 11g

Yesterday I got a question from my blog post reader about LOV components, about how to use LOV components on a reference attributes. Actually, its quite common requirement to have reference attribute shown in LOV, and not its code. I knew it from before, this functional requirement can be implemented in ADF 11g, Steve Muench described it in Oracle Magazine article year ago. However, I decided to implement sample application for this requirement and to give more light on this topic.

Download sample application - LOVByName.zip. It is based on instructions from Steve Muench article mentioned above.

I'm talking here about LOV, where description text is used directly in LOV component, and code is not shown:


User can select value from LOV popup:


And return it back:


So, what you need to do in order to make it work this way. Actually, nothing new comparing to LOV with code value. As usual, include reference attribute, key attribute will be added automatically:


Those attributes are added as reference attributes, means not updateable:


Main thing here is to declare LOV on reference attribute - JobTitle:


You should set JobTitle as LOV attribute and additionally return real key attribute - JobId:


at

43 comments:

  1. DanielNovember 7, 2009 at 10:17 AM

    Hi!

    I've application builded in Forms 6i and I use many procedures which have variables stored on package specification in database. How it will be working if I move it on ADF?

    Thanks in advise!
    Daniel

    ReplyDelete
  2. Karim Hasan AbdellatifNovember 8, 2009 at 8:17 AM

    Hello Andrejus,
    nice post but I don't think this solution will not be so nice in creation case, because EmployeeView is based on two entities now.

    Thank you
    Karim Hasan

    ReplyDelete
  3. Andrej BaranovskijNovember 8, 2009 at 11:35 AM

    Hello,

    Second EO is used as referenc, only to get description. Same as when showing code value and description.

    Regards,
    Andrejus

    ReplyDelete
  4. Karim Hasan AbdellatifNovember 8, 2009 at 1:22 PM

    Hello Andrejus,

    you are right, I thought that JobTilte will be disable in creation case, because it's from reference EO, but It work fine in creation case and I am very happy for that

    Thank you
    Karim

    ReplyDelete
  5. Andrej BaranovskijNovember 8, 2009 at 3:29 PM

    Hi Karim, Thanks for confirming.

    Andrejus

    ReplyDelete
  6. DanielNovember 10, 2009 at 8:39 AM

    Hi,
    I didn't precise my question - I know that JDeveloper keeps connection to Application Server. But my question is: Is there any posibility to force programically Application Server to keep the same connection to database, not for all users, but only for these who switch this function on and until they switch it off. I know that maybe it is not the best way of programming in ADF but, as I mention earlier I've many packages in pl/sql (which use variables stored in specification) and it takes some time to write it again in new environment.

    Thanks in advise!
    Daniel

    ReplyDelete
  7. Andrej BaranovskijNovember 10, 2009 at 7:23 PM

    Hi,

    You may consider to use Reserved Release Level for Application State Management. Check 39.2.2.3.3 section in Oracle Fusion developer guide.

    Regards,
    Andrejus

    ReplyDelete
  8. DanielNovember 12, 2009 at 11:26 AM

    Hi!!!
    Thank you very much for your answer! This is what I was looking for! If you don't mind I have one more queston: how can I handle unexpected exit from my application? For example closing web browser, moving to previous page.
    I'm going to make a page which use my procedures stores on database. When user starts page I'll switch reserved level on. And after everythig will be done (on exiting from this page) I'll switch application module to managed level.

    Thanks in advise!
    Daniel

    ReplyDelete
  9. JoonasNovember 12, 2009 at 11:57 AM

    Hi Andrejus, very nice post, exactly what I needed!

    Do you know if it's possible to make the inputListOfValues component read-only in this case, ie. the value could only be changed through the search option and not directly typing to the field? Marking the component "read-only" in JDev removes the search option, and "disabled" also disables the search.

    ReplyDelete
  10. OskarNovember 14, 2009 at 12:39 PM

    Hi Andrejus

    I have the some problem as Joonas and i have another question. I dont want to JobTitle was mendatory field. The only way to do this is too uncheck "Mendatory" on entity Jobs.
    But what when i want use Jobs entity on another page lets say " dictionary page" where i can add row to entity jobs and field JobTitle is mendatory?

    Thanks in advice.

    ReplyDelete
  11. JohnDecember 12, 2009 at 9:30 PM

    Thanks for the blog. List of values is shown as read-only, Could not update it. Anything that i am missing ?

    Thanks,
    John

    ReplyDelete
  12. Andrej BaranovskijDecember 13, 2009 at 6:20 AM

    Definitely yes, check the difference with my app.

    Regards,
    Andrejus

    ReplyDelete
  13. Daniela ReséndizMay 10, 2010 at 9:43 PM

    Hi Andrejus!!! I've an requirement about a lov, but it need a parameters that i've in the fragment and in a diferents viewObjects. This parameters are elements that the user choose, the lov must display the information filtering throught a viewCriteria defined in the lov also the parameters choosed in the fragment.
    The lov's can receive parameters?

    ReplyDelete
  14. Andrej BaranovskijMay 10, 2010 at 10:00 PM

    Hi Daniela,

    Yes, please check here - http://andrejusb.blogspot.com/2008/12/cascading-lovs-in-oracle-adf-11g-update.html

    Regards,
    Andrejus

    ReplyDelete
  15. DeliaJune 2, 2010 at 12:00 PM

    Hi Andrejus

    Nice post but I have a problem in a form with a LOV made as you described: the code is mandatory and I get a JBO exception when I don't select a value for the LOV field. Any suggestions for this case?

    Thanks,
    Delia

    ReplyDelete
  16. Andrej BaranovskijJune 2, 2010 at 8:28 PM

    Hi Delia,

    Sounds like mandatory attribute validation error?

    Regards,
    Andrejus

    ReplyDelete
  17. DeliaJune 3, 2010 at 9:02 AM

    Hi Andrejus,

    Yes, it's a validation error with a message like this : "Attribute JobId in HrModule.EmployeesView1 is required."

    Delia

    ReplyDelete
  18. Andrej BaranovskijJune 3, 2010 at 9:59 AM

    Hi,

    So, its correct - framework asks to enter mandatory value. There is no problem here.

    Regards,
    Andrejus

    ReplyDelete
  19. DeliaJune 3, 2010 at 10:55 AM

    Hi,

    In this case we don't get a nice validation message saying that a value is required. We get a JBO error in stack trace, which means that the database table did not get a value for the mandatory attribute JobId.

    Regards,
    Delia

    ReplyDelete
  20. Andrej BaranovskijJune 3, 2010 at 11:03 AM

    Ok, it should work generally. If you want, you can send me sample app, I can take a look...

    Regards,
    Andrejus

    ReplyDelete
  21. DeliaJune 3, 2010 at 11:10 AM

    You can run the app you uploaded for this post. Save the form with no value for JobTitle field. You will get the error that I was talking about.

    Regards,
    Delia

    ReplyDelete
  22. Andrej BaranovskijJune 3, 2010 at 11:31 AM

    Hi Delia,

    Thats correct behavior - JobTitle attribute is not set as Mandatory, but JobId is mandatory. So, Model layer protects JobId and show error message to the user.

    If you want to protect JobTitle and show nice red highlight border, you need to set JobTitle attribute as Mandatory=true on Jobs EO. Then it will invoke validation earlier, on JobTitle.

    Regards,
    Andrejus

    ReplyDelete
  23. DeliaJune 3, 2010 at 12:05 PM

    Hi Andrejus,

    Yes, you are wright. The attribute JobTitle was not mandatory in entity. I should have noticed this from the missing "*" in the form.
    Thanks for your help.

    Delia

    ReplyDelete
  24. UnknownAugust 16, 2010 at 7:33 PM

    I used your example, but wanted to additionally return maxsalary in the employee query.
    I added the field as reference in the VO and as return value in the LOV.

    However the LOV funtionality is lost a run-time.
    Please help.
    Have i done something wrong or is this a bug? Any workaround?
    thanks

    ReplyDelete
  25. AndreyFebruary 12, 2011 at 1:22 AM

    Andrejus, good day!

    May be you can help me with case:

    Have 2 views. First - updatable, base on Entity, which have FieldId attribue, and second - readonly, based on query, which have - FieldId (key), FieldName, FieldDescription.
    In first view i make transient field "FieldDescr" (update always) and LOV (for view-2 as LOV source) for "FieldId" with display field "FieldName" and returns (view2.FieldId -> view1.FieldId,
    view2.FieldDescription -> view1.FieldDescr).

    When i open first view - i see: FieldId displays correct (show name of key value), and FieldDescr is empty. When i change value of FieldId - FieldDescr display corrent value (i.e. FieldDescription of key value).

    So question is: I doing something wrong to display other attributes from LOV or it's a bug?

    ReplyDelete
  26. AndreyFebruary 12, 2011 at 5:57 PM

    Good day again.

    Solve this problem by using calculations on transient fields (getting row from LOV Accessor by key value), but still have feelings - this solutiong is not right. Like trying to stretch condom on terrestrial globe :)
    I would be very grateful, if anyone suggest "right" solution (by using returning values of LOV).

    SY, Andrey

    ReplyDelete
  27. Andrej BaranovskijFebruary 12, 2011 at 6:33 PM

    Ha ha, most funny comment I ever received :)

    If you could send me your sample app, I could look into it. Hard to advice something without seeing code.

    Andrejus

    ReplyDelete
  28. AndreyFebruary 12, 2011 at 6:57 PM

    Andrejus, thank you!

    I'll prepare sample app and two tables with data for this case and send you!!

    SY, Andrey.

    ReplyDelete
  29. TonyMarch 7, 2011 at 5:46 PM

    Hello Andrejus,
    I cannot update the field. In the jspx I see inputListOfValues component, but in the web browser, I see just non-editable text without LOV button and displaying the correct value.
    I see inputListOfValues component just when I check updatable for for reference entity(like checkbox in the 5th image, you have it unchecked). But when it's checked, it does not behave correctly.
    Do you have any idea what could I change, please?
    Thank you in advise
    Tony

    ReplyDelete
  30. Andrej BaranovskijMarch 7, 2011 at 5:49 PM

    May be I could help, by looking into your sample app.

    Andrejus

    ReplyDelete
  31. amer sohailMay 20, 2011 at 8:33 AM

    Hi Andrejus,

    I am porting my forms based application in ADF and i am having a problem in the following scenario:
    I have three tables, One for location, second for countries and third for cities which also contain country id as foreign key. Location table has foreign key of city table only. Now in a new location form, I want to have three fields. Address line from location, country name from country table and city name from city table. Requirement is that either user can select city from LOV or can create new city. So how should i do this. One idea was to create a view linke between locationVO and cityVO. second idea was to create a VO based on LocationEO and then add CityEO and CountryEO as reference entities but CountryEO as updatable, and third idea was to create VO based on Location and then add transient fields for city and country. What do u suggest?

    ReplyDelete
  32. Andrej BaranovskijMay 20, 2011 at 10:42 PM

    Hi,

    I'm not exactly sure about your requirement. But second idea looks more close - VO join. First one is Master-Detail, and third - transient fields - no point having them.

    Andrejus

    ReplyDelete
  33. DeepakMCAJune 14, 2011 at 2:58 PM

    Hi Andrejus,
    I have tried implementing the same, but in the creation mode the reference entity attribute is shown as read only. In my task flow, the createinsert method of the base EmpVO is the default activity and after that i am showing the Create Employee page. The attribute is coming as readonly. Do you suggest any other approach where the same form has been used in Create and Update mode both.
    Thanks

    ReplyDelete
  34. Andrej BaranovskijJune 14, 2011 at 4:27 PM

    Same approach works for me even in create mode - LOV is editable.

    Must double check your implementation...

    Andrejus

    ReplyDelete
  35. Andrejs DaskoNovember 15, 2011 at 1:48 PM

    Hello Andrejus!

    I am trying to create a inputComboboxLOV as a column in a table. I have two EOs with master detail relationship. Master EO holds the FK from a details table. I wish to replace this FK with a LOV from detail table. What is the correct way to do this? When a user selects a value for a master table from this LOV, I want to handle the change programmatically.

    ReplyDelete
  36. GauravJanuary 30, 2012 at 10:09 AM

    Hello Andrejus,
    I am creating the LOV with popup.I have created a read only VO and associated the VO as view accessor to the base VO.I associated the corresponding attribute with the LOV.Now the pop up is coming fine, but when I am selecting any value in the popup,everytime the first value from the search is getting selected,irrespective of selecting any other value.Any suggestions regarding this.

    ReplyDelete
  37. MiracleJune 16, 2014 at 6:39 AM

    Hi,

    I used the transient lov for an attribute as u mentioned,
    whereas i'm implementing it as panel with table design,
    lov is working fine in table level, but while selecting values from search area(query panel), it displays all the records instead of matched records. how to resolve it..?

    ReplyDelete
  38. MarkoNovember 28, 2014 at 12:40 AM

    Hello Andrejus,

    Did you try find mode in this sample on 12c or 11.1.2? I am getting

    JBO-25009. Error message parameters are {0=oracle.jbo.domain.Number, 1=HrModule_EmployeesView1.__DefaultViewCriteria__._DefaultViewCriteria_Row_, 2=java.lang.String}

    It happens every time when lov have more than one column in join. Is there any workaround for this case? Do I need to create SR for this is this a bug? Appreciate any help.

    Regards

    ReplyDelete
  39. Andrej BaranovskijNovember 28, 2014 at 7:51 AM

    Hi Marko,

    I will need to test this, thanks for the question.

    Regards,
    Andrejus

    ReplyDelete
  40. Andrej BaranovskijDecember 7, 2014 at 8:24 PM

    Hi Marko,

    I have tested and it works well in ADF 12c (12.1.3). No error reproduced.

    Regards,
    Andrejus

    ReplyDelete
  41. UnknownSeptember 3, 2015 at 9:04 PM

    Hi Andrejus,


    I am struggling on one issue from past one week. Can you please help me out in creating LOV where I have only one table with Columns Name, Address, Job. And I need to create LOV for name and Address on one form with Select One Choice and populate list with Uniques list of Name and Address.


    Thanks & Regards,
    Vikrant

    ReplyDelete
  42. Fernando R. DalSottoOctober 16, 2015 at 4:13 PM

    Allowing me to download the source code was of huge help. I was struggling with a very simple thing using the LOV and checking your example I was able to quickly resolve the problem. As I am new to ADF some stuff still consume a lot of my time. Thanks for your post!

    ReplyDelete
  43. UnknownOctober 28, 2017 at 3:16 PM

    Hello there. Thanks for your great helpful blog.
    Considering your sample, let's suppose JobId is not mandatory in EmployeesView. Hence, when one attempts to set a currently null JobTitle to a not-null value, an error similar to what Delia commented will happen. It seems the reason is the lov tries to insert a new row on it's view accessor, or something like that.
    In other words, if JobId in JobsView is mandatory, the error is like: Attribute JobId in JobsView1 is required. And if JobId is not mandatory, the error is like: It cannot insert to Attribute JobsView1.

    Is there any solution?
    BTW, it seems the sample application download link is dead!

    ReplyDelete