Hi All,

I would like to know if it's possible to have a LOV which depends on a textField. Let us assume that there is a textField with a given value. I want the LOV search results to be dependent on the textField value.

Is this functionality achievable?



asked 28 May '13, 06:13

suranga's gravatar image

accept rate: 0%

Hi Suranga,

Formspider has an LOVField component which is designed exactly with this use case in mind. Here are the steps to create an example.

The steps below, create an LOVField and associates an LOV to it. The LOV filters Employees based on their first name. The text the user enters to the LOVField is used to filter the rows in the LOV when the the LOVField loses focus.

  1. Create a new application ( I used the Oracle supplied HR schema for the example)
  2. Create a datasource based on the table Employees table
  3. Add the following where clause to the data source: first_name like '%'||:firstName||'%'
  4. Create the firstName bind variable of type Varchar2
  5. Create an LOV component named EmpsLOV. In the columns tab unselect every column except the first_name column.
  6. Update the mainPanel with the following XML code:

<panel> <tablelayout> <row> <cell> <lovfield lov="EmpsLOV" bindvariable="firstName"> </lovfield> </cell> <cell> <textfield emptytext="Dummy Item"> </textfield> </cell> </row> </tablelayout> </panel>

The above Panel XML code creates an LOVField and associates it to the LOV component EmpsLOV. The LOVField is also associated with the bind variable called firstName.

The dummy item simply helps to tab out of the LOVField component.

Run the application. Enter a value to the LOVtextField and tab out of the component. Note that the LOV EmpsLOV shows up with a filtered result set.

Hope this helps. Kind Regads, Yalim


answered 28 May '13, 07:56

Yalim's gravatar image

Yalim ♦♦
accept rate: 20%

Hello Yalim,

Thank you very much for the reply. I tried your answer and it works. But this is not exactly what i want. Let say, we want to change the LOV depending on the Dummy Item. How can we achieve this. I need to bind a variable outside the LVOField to the LOV. Is this possible?



(28 May '13, 09:40) suranga

Hi Suranga,

If you think you can make it all work with a single query, single LOV and multiple bind variables, try this:

Continuing from the example above,

  1. Add the following criteria to the where clause of the datasource definition:

and (last_name like '%'||api_component.getValueTX('mainPanel.dummyItem')||'%' or api_component.getValueTX('mainPanel.dummyItem') is null)

Note that, we are getting the value of the dummyItem in the where clause.

  1. Go to the Datasource Screen (double click the datasource in the navigation tree) and uncheck the Query on Initialize checkbox. (This is to circumvent a bug in Formspider in which an undesired exception is raised when an API that references an uninitialized Panel is called from a datasource definition where clause.)

  2. Name the dummy Item dummyItem

<panel> <tablelayout> <row> <cell> <lovfield bindvariable="firstName" lov="EmpsLOV"/> </cell> <cell> <textfield emptytext="Dummy Item" name="dummyItem"/> </cell> </row> </tablelayout> </panel>

If you run the app, the LOV should pick up the value in the dummy item and filter results accordingly.

(28 May '13, 10:04) Yalim Gerger ♦♦

If you think you need multiple queries and LOV's to implement your use case then try the following:

The textField component has an attribute called icon and an event called iconClick. These exist to help the developer imitate the LOV behavior. I added a new textField to the example above by following the steps below

  1. Create a new Datasource Definition based on the table Departments.
  2. Update the Datasource Definition where clause with the following: department_name like '%'||:departmentName||'%'
  3. Create a new bind var called departmentName
  4. Create the LOV DeptsLOV. Check the Auto Refresh and Filter Before Display checkboxes.
  5. Create a new PL/SQL procedure with the following code:

procedure showDeptsLOV is begin api_datasource.setbindvar('DEPARTMENTS1.departmentName', api_component.getValueTx('mainPanel.dummyItem')); api_lov.show('DeptsLOV'); end;

(28 May '13, 10:48) Yalim Gerger ♦♦

6- Create the action showDeptsLOV which points to showdeptsLOV procedure. 7- Update the mainPanel XML code to:

<panel> <tablelayout> <row> <cell> <lovfield bindvariable="firstName" lov="EmpsLOV"/> </cell> <cell> <textfield emptytext="Dummy Item" name="dummyItem"/> </cell> </row> <row> <cell> <textfield name="fakeLOVField" icon="default"> <events> <iconclick action="showDeptsLOV"> </iconclick> </events> </textfield> </cell> </row> </tablelayout> </panel>

(28 May '13, 10:48) Yalim Gerger ♦♦

Note that a new textField with a new icon and iconClick event is added to the XML code.

Run the app. The new textField shows up with an icon next to it. When clicked it picks up the value entered to the dummyItem.

Obviously, in a real life situation, the value of the fakeLOVField should also be used in the DeptsLOV using something like:

api_datasource.setbindvar('DEPARTMENTS1.bindVar2', api_component.getValueTx('mainPanel.fakeLOVField'));

Please note that, the textField named fakeLOVField is not a real LOVField. An LOV will not automatically show up when you leave the field. You will need to code that yourself.

Finally, one last alternative is to call the showDeptsLOV action from a regular button and place it next to a textField.

I hope this helps.

Kind Regards, Yalim

(28 May '13, 10:49) Yalim Gerger ♦♦

The point is in the second method, since you are using PL/SQL to call the LOV, you can customize your PL/SQL to call another LOV (or another 5 LOV's for that matter) based on any criteria.

(28 May '13, 10:51) Yalim Gerger ♦♦

Thanks Yalim, It works fine. I'm using the IconClick event for this purpose.



(29 May '13, 11:30) suranga

You are welcome Suranga. Happy to hear that we found a solution.

Kind Regards, Yalim

(30 May '13, 04:17) Yalim ♦♦
showing 5 of 8 show 3 more comments

Hello Yalim, I also have another question. Is it possible to include a action event for lovField such that the execution of the LOV could be handled via the PL/SQL code.




answered 19 Jun '13, 06:32

suranga's gravatar image

accept rate: 0%

Hi Suranga, No but if you could explain why you need this and what it is your are trying to accomplish maybe we can suggest an alternative solution.

(19 Jun '13, 06:59) Yalim ♦♦

Hello Yalim, My suggestion is , the users have to use LovField and TextField with Icon to facilitate the requirements given above. I feel it's better that all the functionality is provided with one LovField.




answered 21 Jun '13, 06:53

suranga's gravatar image

accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported



Asked: 28 May '13, 06:13

Seen: 4,298 times

Last updated: 21 Jun '13, 06:53

© Copyright Gerger 2017