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:




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.