Thursday 4 October 2007

Apex Cascading LOVs revisited

Like the JDev community, the Apex community has plenty of blogs and example sites on achieving certain functionality which cuts down the learning curve or reinventing the wheel. I recently had the immediate requirement for a cascading LOV in Apex 2.0, and went with Carl Backstrom's example on his sample site.

For my own notes, and possibly of benefit to others, I'd like to extend Carl's example by describing the actual steps needed to implement the solution.

A cascading LOV is where a web page containing 2 poplists called Select Items in Apex, or sometimes loosely referred to as List-Of-Values (LOVs), where the 2nd poplist's values are dependent on the 1st poplist's current selection. When the user selects a value in the primary LOV, the secondary LOV should show a subset of values relevant to the primary value rather than all values.

To implement Carl's example follow these steps:

1) At the application level create an Application Item named: TEMPORARY_ITEM. Application Items are created through the Shared Components page for your application. On creating the Application Item accept the other defaults.

2) Also within the Shared Components, create an Application Process. Carl's example assumes the name of the process is "otn_Select_XML", but you can call it anything you want such as RESOURCE_LIST. On creating the Application Process, ensure the Process Point is "On-Demand", and enter the Process Text as supplied on Carl's website -- essentially an anonymous PL/SQL block with a cursor that constructs a list of items.

3) Within the Application Process change the cursor's SQL query to what ever you want your secondary poplist to show, for example as follows:

select description, code from resources where type_code = :TEMPORARY_ITEM

Note how the query needs to have a predicate (where clause) that is restricted by TEMPORARY_ITEM.

Ensure that within the cursor for loop, you change the column names for the rec pl/sql datatype to match that of your select list aliases in the cursor.

4) Again within the Shared Components, create a List of Values entry that is either a static list of values, or a query driven dynamic list, which you will use for your primary LOV. For example:

List of values name: RES_TYPE_LOV
List of values query: select name d, code r from resource_types order by 1

5) In the web page that will contain the cascading LOVs, within the page's attributes (accessible in Apex 2.0 by clicking on the Edit Attributes button near the top of the screen), in the HTML Header option, embed the JavaScript code from Carl's webpage.

Note that Carl's JavaScript example makes a number of calls to method $x which isn't supported in Apex 2.0. Thanks to the comment in this blog entry by Patrick Wolf, you may replace the call to $x with html_GetElement. If you're using a later Apex version you should be fine.

An alternative easy hack to get this working is to include the function $x in the JavaScript code you just included before the other routines:

<script language="JavaScript1.1" type="text/javascript">

/* begin $x functions */
function $x(pNd){
  try{
    var node;
    switch(typeof (pNd)){
      case 'string':node = document.getElementById(pNd);break;
      case 'object':node = pNd;break;
    default:node = false;break;
    }

  if(node.nodeType == 1){return node;}else{return false;}
  }catch(e){return false;}
}

....other functions....
</script>


The $x function here comes from htmldb_html_elements.js, presumably a JavaScript library available in later versions of Apex?

6) Note within the JavaScript code you just copied in, within the function get_AJAX_SELECT_XML, there is a call to a function htmldb_Get on approximately the 3rd line of the function. Within that function call, there is the following parameter:

'APPLICATION_PROCESS=otn_Select_XML'

otn_Select_XML refers to the Shared Component Application Process you created some steps back. Change otn_Select_XML to whatever you called the Application Process.

7) Still within the web page that you're interested in showing the cascading LOV, create your primary LOV field, a Select Item, named RESOURCE_CODE for example. Base it on the LOV you created in the previous step (ie. RES_TYPE_LOV). Accept the other defaults.

8) Now create your secondary LOV field as a Select Item, named RESOURCE_TYPE for example. Alternatively the actual item you want to be the Select Item may already be on the page as a Text Item for example, so just change it from a Text Item to a Select Item. Base the Static Item on a static list with one dummy item.

9) Once the poplist is created, invoke the editor for the primary poplist, and within the HTML Form Element Attributes option, specify a JavaScript onChange event as specified at Carl's site:

onchange="get_AJAX_SELECT_XML(this,'P37_SELECT_DROP_XML')"

You'll note the onchange code mentions P37_SELECT_DROP_XML. This in fact should be whatever you specified for your secondary Select Item's name.

....

That's all the steps you should need to do. If I've missed out any obvious steps let me know and I'll update the example for the benefit of others.

Thanks to Carl Backstrom for the original example. Note that Patrick Wolf has a more generic solution suitable to later Apex versions than 2.0 that could be better solution for your site.

8 comments:

Patrick Wolf said...

Hi Chris,

the ApexLib Framework with the generic cascading lov solution also works with Oracle APEX 2.0. I also have reports from users that it even works with 1.6

Greetings
Patrick

Anonymous said...

I can't make it work.
The second list never filled.

Chris Muir said...

My colleagues and I have only used this technique several times successfully.

Firstly, don't post anonymously. Secondly, how about a tad more detail and maybe I could help you. I'd admit my instructions could possibly do with some more details, but as you've given me no idea what you've done, I can't help you. Have you turned your computer on?

Sounds like you may be better off heading to the APEX OTN forums for help.

CM.

Chris Muir said...

As a follow up to these instructions, if you decide in step 1 to change the Application Item name to anything besides TEMPORARY_ITEM, in step 6 ensure on approx the 4th line of the Javascript function, to change the reference to TEMPORARY_ITEM to whatever you decide to call the Application Item in step 1.

OldStuff said...

Hi,

I´m not sure if my previous message got through but......In this post in Step 2 you reference to a piece of code in Carl´s website. I cannot find that code:

"On creating the Application Process, ensure the Process Point is "On-Demand", and enter the Process Text as supplied on Carl's website"

How can I get to that code more explicitly?

Thank you

Javier

Chris Muir said...

Hi Javier

Carl's website has been redone since I wrote this post. Though I can't say with 100% certainty, but the bit you're now looking for is entitled "Application On Demand Process (otn_Select_XML)"

Regards,

CM.

rajesh said...

Hi

i have follwed your instrucions in getting select list based on first list. But selected one in the second list has not assigned to the variable. Variable is showing null in the Debug mode.

Thanx in Advance.

Chris Muir said...

Sorry, it's been over 3 years since I've worked with Apex, I wouldn't know where to start to assist you anymore.

CM.