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,

    I have tested it with Version 2.2.1. Are you using Version 2.0?

    In that case some modifications have to be done.

    1.) In the ApexLib.js file, all ocurrences of $x have to be replaced by html_GetElement => $x doesn’t exist in V2.0
    2.) You can only use the version which is loaded into the FLOW_… schema. The other version doesn’t work because the APEX_* dictionary views do not exist in V2.0. What do you have to do. a) Replace the FLOWS_020200 thru FLOWS_020000 in the header of the package. b) Maybe you have to remove the where-clause restriction NVL(UPPER(ITEM_COMMENT), ‘@’) NOT LIKE ‘%$APEXLIB_IGNORE_LOV$%’, I’m not sure if the comment column exists in V2.0. Don’t have a possibility to check that yet.

    I will come up with a solution on sunday or monday.

    Patrick

  2. Hi,

    I was wondering if I could use this example to populate a select list from a text field. i.e. the first select list would be a text field.

    Thanks

  3. Hi,

    just to make sure that I did understand you correct.

    The first field is a text field where you can enter any value and the second field is a select-list which uses the value entered in the text field to retrieve the data.

    If you mean that than I can confirm that it will work.

    Patrick

  4. Hi,

    Yes that is what I meant.
    When I try nothing happens.
    I have followed the document, but I get no errors and no data.
    Do I need to put a JS on the text item? onblur?

    Thanks

  5. No Patrick that doesn’t work either.
    I am using Application Express 2.2.1.00.04.
    I have compiled ApexLib_Lov_for_userschema.pks/pkb in my schema.

    I put the JS in page 0.

    I then created the application items and process.

    I then created my select lists.

    Do I need to but a Javascript in my HTML form element attribute?

  6. Hi,

    maybe it’s easier to sort that out when you contact me by e-mail. It’s in my profile.

    BTW, you don’t have to add any JavaScript code, it’s done by the library.

  7. hi,

    Did you ever get that TextBox option to work?

    The first field is a text field where you can enter any value and the second field is a select-list which uses the value entered in the text field to retrieve the data.

    Thanks

  8. Hi,

    works also with a textfield as master. I have added an example for that onto the demo page (http://apex.oracle.com/pls/otn/f?p=33231:4::::::)

    Run your application in debug mode. You should see something like “Analysing page(ApexLib_Lov.generateLovDependencies)” at the bottom. And it should also display “detected Px_xxx as cascading lov”.

    If you don’t see that, check for typos in your lov sql statement. Or can you provide a test case on apex.oracle.com so that I can take a look.

    Patrick

  9. Patrick,

    3.3 step is not consistent with the image. I’m about arguments of ApexLib_Lov.generateLovDependencies

  10. If I’m able to put ApexLib.js into /i/ of Application Server I don’t have to double this code on the page 0? Right?

  11. Hi Edward,

    I have uploaded a new image, thanks!

    You are right, you don’t have to include the full content of ApexLib.js into page 0. You just have to include the following call.

    <script src=”/i/javascript/ApexLib.js” type=”text/javascript”></script>

    Or if you don’t like to add such a page 0 region, you can also include this call into your page templates after the call of

    <script src=”/i/javascript/htmldb_get.js” type=”text/javascript”></script>

    Patrick

  12. I have just used your ApexLib framework to implement some cascading select lists in an app I have built in Oracle XE and it works like a dream!

    Thanks for your efforts in putting it together and providing such useful examples.

    One question I do have though. Before I used the technique described here, I had created some javascript links that a user could click that would set the values for my 3 select lists in one click, therefore saving them having to manually select each time. This no longer works and I was wondering if there is some work around to achieve the same result?

    Kind regards,

    Stuart

  13. Hi Stuart,

    great to hear that it works like a dream! :-)

    About your other problem, is it possible that you put up an example on apex.oracle.com so that I can take a look? You find my e-mail address in the profile info or in the HowToInstall.html

    Patrick

  14. Hello,
    I have been using the dynamic LOV for many pages in my apps and it works brilliantly. Then all of a sudden, I get some javascript error on some of my pages..
    html_GetElement(vClearFieldId) has no properties
    and the other select list doesn’t get populated any more.
    Any idea why??

  15. Does the cascading LOV work if you put the two LOVs on Page 0?

    I created some LOVs on page 1 and everything works. If I move the region to Page 0, the second LOV is not updated

  16. Patrick,

    I was looking at the example online and now you use a popup lov. This is the reason I’ve integrated your framework. However, it’s not working for me…

    I have XE and Apex 2.1.

    I noticed that when you open your popup lov, your new passback function is being used. But on mine its still the old one. I can find how to change this. Could you please help me?

    Dan

  17. Hello, Apexlib does not work to me correctly, exists some form to check the installation, Thanks

  18. Hi Robert,

    what does not work correctly? The best would be to write me a mail with your symptoms.

    You can find my mail address in the HowTo Install or in the Blog Profile.

    Patrick

  19. Hi Partick,

    The information in this is very useful. I need ur help in my application. I have a Tabular form in which I have given dropdown list for 2 columns. Now I want to populate data in the second dropdown based on the selected value in the first dropdown. How do I achieve this… Plesae help me..

    Regards,
    Pallavi

  20. Hey,

    Thanks a ton! This was exactly what I needed. I have one request though, I would like to be able to update a text area based on values chosen in the combo boxes. The text box uses a PL/SQL source wich uses bound info from the three combo boxes.

    I thought maybe the code in apexlib_sql.executePlSqlCode could be used to referesh the info in the text box and if i can dig around in it I might be able to patch it up, but i figured you’d probably be way faster at it!

    Thanks for a great tool!

  21. Hi Patrick,

    I implimented the ApexLib framework successfully. I have a question though. I have cascading LOVs in which one is dependent of the other. The user can cascade down to select a report they want to run. If the report uses a date range, they can select this as well via a date picker popup. Anyway, The reports generated are Oracle Reports. I’m trying to have the report open on a new page and have a parameter passed to the report. The parameter is from one of the LOVs that the user selects, IE, a City. I can’t seem to get it to pass to the ORacle Report at initial run without submitting the page. Is there a way to submit the page so that the value of the LOV is passed to the report?
    Thanks in advance!
    Lou

  22. Do you know how this functionality could work with HTML DB v1.6.1? I can get the Select with Submit fields to work but not the popups.

    I appreciate any feedback.

    Thanks.
    Mark

  23. Does not seem to work with Popup Key LOV (Displays description, returns key value). (version 3.0)

  24. Hi Patrick
    there’s a version of cascading select list compatible with ApEx 3.1.2?

  25. Patrick,
    I’ve a question (regarding a slightly different situation, but I compare it with the example):
    If I choose a region (ig Europe), a country (ig Belgium), then I want to see ALL of the locations (instead of, in this case, NONE of the locations).
    My situation is slightly different because I need to make a choice out of the whole list of the locations. How can I arrange this?
    Neil

  26. Hi Patrick,

    I have looked at your website and have integrated ApexLib Framework. I would like to integrate your multiselect cascade example, how can I do that? Can you please help me?

    I have following requirement:

    I have 1 dropdown (dd1 from table1) and 2 multiselect list (say ms1 from table2 & ms2 from table3) on my page from database tables (all are dynamic).

    # I need to populate ms1 based on dd1. It is fine and ok but how can I select values already stored on the database and how can I populate other values on top of selected ones in ms1.
    # And based on ms1 how can I cascade ms2?

    eg:

    dd1 values: 1, 2, 3, 4 etc…(selected 2)

    Populate ms1: 2.1, 2.2, 2.3, 2.4, 2.5 (selected 2.2, 2.3, 2.4 as 2 is selected on dd1)

    Populate ms2: 2.2.1, 2.2.2, 2.3.1

    Your help would be appreciated to point me to the right direction.

    Thanks,
    Bhavin

  27. Patrick,

    Thanks for all of your hard work. I manage to get the generic cascading select list to work. I wanted to make minor changes to the “null text” on the select list for P4_REGION_ID and P4_COUNTRY_ID. I modified it with the work “All” and the “null value” as “%”. This way my report will show all of the results for region and country. This is working. I try run my report with the different selection and I notice of some sort of bug. If I select a specific region, and then select “All” again, the country drop down list will only show the entry “all”. If I select any other specific region, it is working as expected. It seems that the value of “%” is not being interpret as a number. Can you provide some guidance on this? Let me know if I’m doing something wrong. Any help would be appreciated.

    Rod

  28. Well this article is perfect.But if I have a scenerio in report (while creating page I select form + tabular) and write query as Ex: select empno,class,department from emp table.Note: I have introduced class as column which contains data A,B,C .Based on condition of class I have to display department.I want to display both class and department as lov.
    Ex: If class ‘A’ then display department ’10,20′ in lov. If class ‘B’ then display department ’40,50′ in lov.
    Whether it is possible?

  29. hello patrick and install the framework on my apex but not dynamic lists work for me! that do not have reviewed all the steps and only when the office work for me after changing the value of the list and not work for me to be!

  30. Have installed ApexLib 2.0. I have a two text fields whose values are dependent on a select list. But the text fields do NOT get updated when a value is chosen from the select list. Does your generic cascading LOV solution work to populate non-select list page items?

  31. Hi Patrick. With the previous version of your framework, the Out-of-the-box cascading lovs where working fine. Then we applied the upgrade of your framework and they are not working anymore. I do not know if we miss something. Even with the ‘example’ application that you provide it does not work. Any help?

    Regards!

  32. Hi Patrick,
    We are having the same problem as Jeannine. We have been developing an app using your framework and using latest version has resulted in cascading lovs not working. I have noticed we are now getting the following error in javascript:
    Expecting p_company or wwv_flow_company cookie to contain security group id of application owner.
    Error
    ERR-7620 Could not determine workspace for application ().

    OK

    Any ideas?

    Note removing cascade select lists to popups etc works fine.

    Cheers

    Simon

  33. Hallo Patrick,

    ich befasse mit seit kurzem mit APEX und habe vorgestern die APEXLib, APEXBuilder installiert, weil mich speziel die ‘cascading LOV’s’ interessieren.

    Ich kann also sagen, dass ich auf dem Gebiet APEX ein Newbie bin.

    Ich bin ganz brav die HowTo’s durchgegangen und die Installationen verliefen problemlos.

    Nun habe ich das Problem, dass beim aufrufen der APES-Pages sich der FireFox mit der Fehlermeldung bemerkbar macht : ‘apexlib is not defined’

    ich habe in der Dokumentation und auch in den NewsFeeds keine Lösung dafür gefunden.

    Für Hilfe wäre ich dankbar.

    MfG
    Arkadius

  34. Hallo,

    den von mir in meinen letzten Posting beschriebenen Fehler habe ich behoben.
    Jetzt bekomme ich die gleiche Fehlermeldung wie Simon.

    Ich benutze Oracle XE, APEX 3.2.1.00.11, APEXLib 2.0

    Gibt es schon Lösungsvorschläge?

    Grüße

    Arek

  35. Simons Problem regarding the cascading LOV have been solved. Somehow the Application Process ApexLib_getGenericResult was faulty.
    After deleting and creating again everything worked as expected.

  36. Morning Patrick
    I am using a Multiselect list on page 117.
    When I select items from this Multiselect list and pressed a button it displayed the report results fine.
    After a while when I select items from Multiselect list suddenly it started behaving strange by deselecting
    items I selected and returning null values probably, so the report is not displayed.
    Do you have any clue.
    My page doesn’t have any processes.
    Your reply is highly appreciated.

    Regards
    Rao

  37. Hi Rao,

    can you provide a link where I can reproduce this behavior? And if you could provide the steps to do it that would be great.

    Regards
    Patrick

  38. Any solution on how to cascade a multiselect list into another multiselect list in apex 3.1.1?

Comments are closed.