Generic solution for cascading select lists/lovs

After implementing several times a select list/popup lov, which is depending on another item on the same page (see the AJAX example from Carl Backstrom), I thought that it was time to come up with a more generic solution where I don't have to implement javascript code and on-demand processes for each lov anymore.


I thought if the render engine of APEX is able to generate the SQL statements on the fly, I should be too ;-)

My goals where that I

• don't have to include any Javascript code or any other specific code onto the page and that I
  
• don't have to write an on-demand process for each select list/lov anymore.
• And of course I don't want to use selects-list with submit, I want that my application is "sexy" and uses AJAX for partial page reload!!! :-)
• So everything should be "out of the box" as APEX would already provide this feature.
  

My solution after thinking about it for a while

• Create a generic Javascript library which handles the dependencies between the items. It should be able to clear and repopulate all depending items, if the value of an item changes.
• Write a process which fires on page 0, which reads the APEX data-dictionary to determine the relationship between the different items and generate the necessary calls to my generic Javascript library. But which has to be aware of existing Javascript code which may has already been registered for an onchange event on the item.
  
• Write an on-demand process who is able to execute the select statement of the lov definition and return the result to the Javascript library (see the existing AJAX examples) which will update the select item on the page.

And finally the result! :-)

So I have put together some Javascript and PL/SQL code and a How-To-Do, so that you are able to integrate it into your existing applications.

The solution has been tested with APEX 2.2.1 and Firefox and IE 6. But it should also work for all 2.x versions.

Integration into your application

Follow the steps in my How to integrate posting.

Just want to use the cascading lov? Then you only have to create

• ApexLib - include Javascript library
• ApexLib - Before footer with the following code

  ApexLib_Lov.generateBrowserData;
  ApexLib_Browser.flushJsBuffer;

• Application Item APEXLIB_REFERENCE_TYPE and APEXLIB_REFERENCE_ID
• Application Process ApexLib_getLovResult

That's it! All the rest is done automatically when you execute your application.

So what did we just do?

We created a two new regions on the page 0 (page 0 is executed for every page), the first will add the Javascript library into the html-page, and the second will call the PL/SQL package which will determine the lov dependencies for a page. It will generate the necessary javascript code on the fly. The Javascript code will call the generic on-demand process with all the necessary item values the lov query requires. The PL/SQL package which the on-demand process is calling, executes the defined lov query with the values entered on the html-page. The result is send back and used to repopulate the lov on the HTML page.

BTW, it's no problem when you have your own javascript code in an "onchange" event, the code generated by the library will be executed after your code.
If you want to disable the dependency checking for a lov, just write $APEXLIB_IGNORE_LOV$ into the "comments" of the page item.

Known restrictions

• Doesn't support lovs in a report region (multi-record)
• Doesn't support lovs where the select statement is returned by a function
• Doesn't support lovs of type radio group or checkbox
• Doesn't use the translation table for the result values if the property is set

But I will work on them as soon as I have some time...

Try it if this generic solution will also works for you. Feedback always welcome!

PS: I wish that some similar functionality will make it into APEX 3.0...

Example

On apex.oracle.com I put together a simple example which you can try. Afterwards the steps to create it.

1. Create a new region of type "HTML" and set region template to "Form Region"
2. Add a new item with the following settings
• Type: Select List
• Select list control type: Select List (you don't need Select List with Submit, ... anymore!)
• Name: P4_REGION_ID
• Display Null: Yes
• Null Text: - select region -
  
• List of Values Query

  
  SELECT REGION_NAME AS D, REGION_ID AS R
    FROM REGIONS
   ORDER BY REGION_NAME
  

• Label: Region
• Item Source: Always null


3. Add another item to this region
• Type: Select List
• Select list control type: Select List
• Name: P4_COUNTRY_ID
• Display Null Option: Yes
• Null Text: - select country -
  
• List of Values Query

  
  SELECT COUNTRY_NAME AS D, COUNTRY_ID AS R
    FROM COUNTRIES
   WHERE REGION_ID = :P4_REGION_ID
   ORDER BY COUNTRY_NAME
  

• Label: Country
  
• Item Source: Always null
4. Add another item to this region
• Type: Select List
• Select list control type: Select List
• Name: P4_LOCATION_ID
• Display Null Option: Yes
• Null Text: - select location -
  
• List of Values Query

  
  SELECT CITY AS D, LOCATION_ID AS R
    FROM LOCATIONS
   WHERE COUNTRY_ID = :P4_COUNTRY_ID
   ORDER BY CITY
  

• Label: Location
  
• Item Source: Always null

Run the page and you will see that if you change the region all details will be cleared and repopulated. Change the Country and the Location will be cleared and re-populated.



In case

if it doesn't work as expected, check if the error console of your browser (Firefox: Tools/Error Console) shows some errors. I would recommend that you get a Javascript debugger like FireBug, set a breakepoint at vResult = vAjaxRequest.get("XML"); , step into the get("XML") and check the results there. Maybe there is some problem with your SQL statement. The responseText will show what APEX is sending back.