Generic solution for cascading select lists/lovs

Note: Before you read on, cascading LOVs are now natively implemented into APEX 4.0. See my related blog posting Oracle APEX 4.0: Cascading LOVs/Select Lists.

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 Oracle Application Express (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 install 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.

61 thoughts on “Generic solution for cascading select lists/lovs

  1. Hi Patrick!

    We are using apexlib in our project and when we try to integrate extjs message box, we have a trouble the cascade lovs fail and firebug report an “vFieldValue is undefined
    [Break on this error] vAjaxRequest.add(pLovEntry.oUsedFi…ace(/\:/g, String.fromCharCode(1))); ” error in apexlib-full.js line 6287.
    What can we try? tx in advance!

  2. Hi Alf,

    there is some kind of overlapping with apexlib and ext_js, i’ll try and include a workaround for that in the upcoming ApexLib 2.1

    Peter

  3. Tx Peter!
    For a while, do you have some idea of what I can try to solve it?
    Exists a way to implement a Jquery msgbox to avoid extjs? I want to use the msgbox to show the confirm window when the user try to delete rows…
    Tx in advance

  4. Hello,

    Thank you, I try it on Firefox and it’s working well.

    But apparently I have a problem with internet explorer, the child select list doesn’t show any item (only the -select-) when I select an item in the parent select list.

    I tried to debug this with IE, and I’m getting the following message:
    ‘undefined’ is null or not an object.

    from the code

    {if(typeof V.getElementById!==”undefined”&&!W){var T=V.getElementById(U[1]);return T?[T]:[]}},NAME:function(V,Y,Z){if(typeof Y.getElementsByName!==”undefined”){v…

    Any idea what could be the problem?

  5. Hi Patrick,

    I tried to use ApexLib in my application, in order to use CascadingLov Select lists. I have Oracle XE and Apex 3.2.1.00.10, and last version of ApexLib I downloaded from link I found on this page.

    So, I have installed this, but It doesn’t work. I tried to add ApexLib_Lov.generateLovDependencies call in Page 0 PL/SQL Region source, but I got error when I wanted to apply changes – there is not generateLovDependencies program in ApexLib_Lov package.

    What is a mistake?

  6. I’ve set up a cascading select on a page with a number of other selects.
    The “parent” select is called “P67_IDENTIFIER”.
    The “child” select is called “P67_AUDIT_TYPE”.

    The Ajax “post’ generated is:
    p_request=APPLICATION_PROCESS%3DApexLib_getLovResult&p_instance=861217320521086&p_flow_id=117&p_flow_step_id=0&p_arg_names=APEXLIB_REFERENCE_TYPE&p_arg_values=ITEM&p_arg_names=APEXLIB_REFERENCE_ID&p_arg_values=25643526044389806&p_arg_names=P67_IDENTIFIER&p_arg_values=18

    The response I get is:
    ORA-20555: Item P67_AUDIT_COORDINATOR doesn’t exist.

    “P67_AUDIT_COORDINATOR” is a unrelated select list on the page. I have no idea why an error regarding it is been returned. Any ideas?

Comments are closed.