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:


43 comments:

Daniel said...

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

Karim Hasan Abdellatif said...

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

Andrej Baranovskij said...

Hello,

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

Regards,
Andrejus

Karim Hasan Abdellatif said...

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

Andrej Baranovskij said...

Hi Karim, Thanks for confirming.

Andrejus

Daniel said...

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

Andrej Baranovskij said...

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

Daniel said...

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

Joonas said...

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.

Oskar said...

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.

John said...

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

Thanks,
John

Andrej Baranovskij said...

Definitely yes, check the difference with my app.

Regards,
Andrejus

Daniela Reséndiz said...

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?

Andrej Baranovskij said...

Hi Daniela,

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

Regards,
Andrejus

Delia said...

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

Andrej Baranovskij said...

Hi Delia,

Sounds like mandatory attribute validation error?

Regards,
Andrejus

Delia said...

Hi Andrejus,

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

Delia

Andrej Baranovskij said...

Hi,

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

Regards,
Andrejus

Delia said...

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

Andrej Baranovskij said...

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

Regards,
Andrejus

Delia said...

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

Andrej Baranovskij said...

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

Delia said...

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

Unknown said...

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

Andrey said...

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?

Andrey said...

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

Andrej Baranovskij said...

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

Andrey said...

Andrejus, thank you!

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

SY, Andrey.

Tony said...

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

Andrej Baranovskij said...

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

Andrejus

amer sohail said...

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?

Andrej Baranovskij said...

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

DeepakMCA said...

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

Andrej Baranovskij said...

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

Must double check your implementation...

Andrejus

Andrejs Dasko said...

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.

Gaurav said...

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.

Miracle said...

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

Marko said...

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

Andrej Baranovskij said...

Hi Marko,

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

Regards,
Andrejus

Andrej Baranovskij said...

Hi Marko,

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

Regards,
Andrejus

Unknown said...

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

Fernando R. DalSotto said...

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!

Unknown said...

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!