AJAX Cascading Select List in APEX

This example shows you how to create a cascading select list in an Oracle APEX Form.

The first select list is a list of Projects (from Financials) and the second list is a list of tasks within that Project.

1) Create an Application Item to store the value (PROJECT_ID) of the option chosen in the first select list:

CAS_SEL_PROJ_ID_ITEM

2) Create an application process which outputs the list of tasks, based on the PROJECT_ID selected.

Process Point: On Demand
Name: CAS_SEL_LIST
Type: PL/SQL Anonymous Block

Process Text:

BEGIN
   OWA_UTIL.mime_header ('text/xml', FALSE);
   HTP.p ('Cache-Control: no-cache');
   HTP.p ('Pragma: no-cache');
   OWA_UTIL.http_header_close;

   HTP.p('<select>');

   FOR c IN (select
            pt.TASK_ID,
            pt.TASK_NUMBER,
            pt.TASK_NAME
            from
            PA_TASKS pt
            where
            pt.PROJECT_ID = :cas_sel_proj_id_item
            order by to_number(pt.task_number)
   )
   LOOP
      HTP.p('<option value="' || c.task_id || '">' || c.task_number ||' '|| c.task_name || '</option>');</span><br /><span class="Moobi_Code">   END LOOP;

   HTP.p('</select>');
END;

3) On the page where you would like to place you cascading select lists, create a select list item:

Name: P3_PROJECT_CODE
Display as: Select List
Display Null: Yes

List of Values Definition:

select
segment1||' - '|| name d,
project_id r
from
apps.PA_PROJECTS_ALL@rr_prod
where
project_status_code LIKE 'APPROVED'
order by SEGMENT1

4) Create a second select list item, which will be populated based on the value of the first LOV:

Name: P3_TASK_CODE
Display as: Select List
Display Null: Yes

List of Values Definition: (this needs to be defined so that options can be populated if the page is refreshed and P3_PROJECT_CODE contains a value)

select
pt.TASK_NUMBER ||' '||
pt.TASK_NAME d,
pt.TASK_ID r
from
apps.PA_TASKS@rr_prod pt
where
pt.PROJECT_ID = :P3_PROJECT_CODE

 

5) On the APEX page that you have added your LOV's, add the following javascript to the HTML Header of the page definition.

 <script language="JavaScript" type="text/javascript"></p> <p class="Moobi_Code">function get_select_list_xml1(pThis,pSelect)
{
    var l_Return = null;
    var l_Select = html_GetElement(pSelect);
    var get = new htmldb_Get(null,html_GetElement('pFlowId').value,
              'APPLICATION_PROCESS=CAS_SEL_TASK',0);
    get.add('CAS_SEL_PROJ_ID_ITEM',pThis.value);
    gReturn = get.get('XML');
    if(gReturn && l_Select){
        var l_Count = gReturn.getElementsByTagName("option").length;
        l_Select.length = 0;
        for(var i=0;i<l_Count;i++){
            var l_Opt_Xml = gReturn.getElementsByTagName("option")[i];
            appendToSelect(l_Select, l_Opt_Xml.getAttribute('value'),
                                     l_Opt_Xml.firstChild.nodeValue)
        }
    }
    get = null;
}


function appendToSelect(pSelect, pValue, pContent)
{
    var l_Opt = document.createElement("option");
    l_Opt.value = pValue;
    if(document.all){
        pSelect.options.add(l_Opt);
        l_Opt.innerText = pContent;
     }else{
        l_Opt.appendChild(document.createTextNode(pContent));
        pSelect.appendChild(l_Opt);
    }

}
</script>

6) Open up the P3_PROJECT_CODE item, and add the call to the javascript, in the HTML Form Elements Attribute field:

onchange="get_select_list_xml1(this,'P3_TASK_CODE');"</span></p> <p class="Moobi_Body"> 

This example has been adapted from Denes Kubicek's example at: http://htmldb.oracle.com/pls/otn/f?p=31517:119:1076131717350499:::::