Filling a LOV in Oracle Apex based on data in another text box

Question

I am fairly new to Oracle Apex, and have a problem. Our application currently has a method of entering data, with several text boxes and Optional List of Values. I would like to have an LOV based on information in another text box like so:

select APPOINTMENT_ID PATIENT_ID from APPOINTMENT where PATIENT_ID = :P9_PAT_NUM

where P9_PAT_NUM is a patient number in a text box. However, this would apparently only work if the text box has already been submitted, else it assumes the text box is null.

Is there any way to get this working with an LOV, or perhaps some other method?

Answer 1

In Apex version 4 I believe this will be added as a built-in feature.

Prior to version 4, there are a number of solutions available - Google "Apex cascading LOV" and you'll find a few solutions that involve a bit of work, e.g.:

Javascript: http://www.inside-oracle-apex.com/generic-solution-for-cascading-select-listslovs/

ApexLib: http://one-size-doesnt-fit-all.blogspot.com/2007/10/apex-cascading-lovs-revisited.html

Using ExtJS: http://application-express-blog.e-dba.com/?tag=apex-cascading-lov

Answer 2

Once Apex 4.0 comes out (should be quite soon) this will be very simple using its cascading LOV functionality. Prior to that you can do it in one of two ways: easy but clunky, or difficult but slick...

1) Easy but clunky:

Add the following Javascript to the first item's HTML form element attributes:

onchange="doSubmit()"

This will submit the page when the first item is changed, so when it reloads the item's current value will have been used to populate the LOV. I say is it "clunky" because the entire page reloads (slow), and the cursor moves back to the starting position.

2) Difficult but slick:

If your LOV is a popup then you just need to get the value of the first item into session state, which you can do via Javascript and AJAX.

If your LOV is a select list then you need to dynamically re-populate the select list using Javascript and AJAX.

I won't got into the details of these, but I see that while I've been writing this Jeffrey Kemp has posted some useful links that will explain in more detail.

Answer 3

Hello I tried to use the clunky onchange="doSubmit()" option as I am only putting together a demo appliaction for our existing database, but this does not seem to work.

This is my LOV statement

select asset_id||' - '||description d, asset_id r from asset where node1 = :P9_X order by 1

but when I comment out the variable P9_X and use it in the select statement it is properly evaluated

select :P9_X||' - '||asset_id||' - '||description d, asset_id r from asset /* where node1 = :P9_X */ order by 1