Cascading select list on tabular form

Here is yet another way create cascading select list on the tabular form.

First place to page JavaScript Function and Global Variable Declaration:

(function($){
  
 /*** Cascading select list ***/
 $.fn.htmldbCascade=function(parent,onDemand,options){
  
  options=$.extend({
   trigger       : false,
   extrVal       : false,
   nullShow      : false,
   nullValue     : "",
   nullDisplay   : "%",
   disAlias      : "DIS",
   retAlias      : "RET",
   loadingTxt    : "Loading ...",
   loadingCss    : {"width":"80px"}
  },options);
  
  return this.each(function(i){
 
   var self=$(this);
   var lParent=$(parent).eq(i);
   var lSelfVal=self.val();
   if(!lSelfVal){lSelfVal="";};
 
   if(!lParent.data("htmldbCascade")){
    lParent.change(function(){
 
     var lParentVal=$v(this);
     if(!lParentVal){lParentVal="";};
      
     $.extend(options.loadingCss,{"height":self.parent().height()});
  
     self
      .hide()
      .empty()
      .parent()
      .find("div.ui-autocomplete-loading")
      .remove()
      .end()
      .append($("<div/>",{"html":options.loadingTxt,"css":options.loadingCss})
      .addClass("ui-autocomplete-loading"));
  
     if(options.nullShow){
      appendOpt(self,options.nullDisplay,options.nullValue);
     };
  
     $.post("wwv_flow.show",{
      p_flow_id:"&APP_ID.",
      p_flow_step_id:"&APP_PAGE_ID.",
      p_instance:"&APP_SESSION.",
      p_request:"APPLICATION_PROCESS="+onDemand,
      x01:lParentVal
     },function(jd){
      var lExists=false;
      $.each(jd.row,function(i,d){
       if(d[options.retAlias]===lSelfVal){lExists=true;};
       appendOpt(self,d[options.disAlias],d[options.retAlias]);
      });
      if(options.extrVal&&!lExists){
       appendOpt(self,lSelfVal,lSelfVal);
      };
      self
       .val(lSelfVal)
       .show()
       .parent()
       .find("div.ui-autocomplete-loading")
       .remove();
      if(options.trigger){self.trigger(options.trigger);};
     },"json");
  
    }).data("htmldbCascade",true).trigger("change");
   }
  });
  /*** Append option ***/
  function appendOpt(pThis,pDis,pRet){
   pThis.append( $("<option/>",{"html":pDis,"value":pRet}) );
  };
 };
  
})(apex.jQuery);

Add to page HTML header:

<style type="text/css">
.ui-autocomplete-loading{background: url("#IMAGE_PREFIX#libraries/jquery-ui/1.8/themes/base/images/ui-anim_basic_16x16.gif") no-repeat scroll right center transparent;}
</style>

Change child LOV column Display As to Select List(query based LOV), Display Extra Values to Yes and Display Null to No. Place to List of values definition:

SELECT NULL d,
  NULL r
FROM DUAL
WHERE 1 = 2

Create application item called G_TEMP. Set item Session State Protection to "Restricted - May not be set from browser".

Create page or application On Demand process that will return your LOV values example:

DECLARE
  l_sql VARCHAR2(32700);
BEGIN
  IF APEX_APPLICATION.G_x01 IS NOT NULL THEN
    APEX_UTIL.SET_SESSION_STATE('G_TEMP', APEX_APPLICATION.G_x01);
    l_sql := '
      SELECT empno AS RET,
          ename AS DIS
        FROM emp
        WHERE deptno = :G_TEMP
        ORDER BY ename
    ';
  APEX_UTIL.JSON_FROM_SQL(l_sql);
  ELSE
    HTP.prn('{"row":[]}');
  END IF;
  APEX_UTIL.SET_SESSION_STATE('G_TEMP', NULL);
END;

Please note that query column alias naming is important! Use alias RET for LOV return value and alias DIS for display value.

Create dynamic Action. Select Advanced

• Name: Set cascading LOV after refresh and onload
• Event: After Refresh
• Selection Type: Region
• Region: {select your report region}
• Condition: No Condition
• Action: Execute JavaScript code
• Fire On Page Load: True
• Code:

  $("[name=f01]").htmldbCascade(
   "[name=f02]",
   "GET_MGR_LOV",{
    nullShow:true,
    nullDisplay:"- Select -"
  });
  

• Selection Type: None

In JavaScript code use child LOV select list name attribute as jQuery selector and replace f01 according your child LOV column select name. Other parameters are parent select list jQuery selector, On Demand process name and options e.g. do you like show null value.

Edit tabular form "Add Row" button and change action to "Defined by Dynamic Action".

Create another dynamic Action. Select Advanced

• Name: Set cascading select listfor new row
• Event: Click
• Selection Type: Button
• Button: {select your form add row button}
• Condition: No Condition
• Action: Execute JavaScript code
• Fire On Page Load: False
• Code:

  addRow();
  $("[name=f01]:last").htmldbCascade(
   "[name=f02]:last",
   "GET_MGR_LOV",{
    nullShow:true,
    nullDisplay:"- Select -"
  });
  

• Selection Type: None

In second dynamic action JavaScript we call addRow function. Rest of code is same as fist dynamic action, except add jQuery :last selector. We need add last selector to get only added row. See example from above where add last selector.

See working example.

Article updated on 27.03.2012 and 26.07.2012