Single Article

Cascading select list on tabular form

Category APEX and jQuery

Article updated on 27.03.2012

Article updated on 26.07.2012

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.

Social Buttons

Comments

  • Jari Laine 20 Jun 2018  

    Hi Hondo,

    If I understand correctly what you looking for, you need define real LOV for child select list and not trigger change event for parent select list when page loads.

    Regards, Jari

        
  • Hondo 19 Jun 2018  

    Aloha Jari,

    I'm trying to find out if there is a way for the child to hold the table value and only show the select LOV when the parent is changed.

    Thanks in advance,

    Hondo

        
  • Jari Laine 16 Jan 2018  

    Hi Aditya,

    Most probably problem isn't relate to APEX version.
    Did you check is correct value inserted/updated to table?
    Could you please reproduce problem in apex.oracle.com and share developer login details to workspace.

    Regards,
    Jari

        
  • Aditya 16 Jan 2018  

    Hi Jari,

    Thanks for your reply,

    I am using "add row" button for creating new rows and using the system default update, Insert, Delete Functionality present in tabular forms.

    Note: I am using Oracle Apex 4.2.1

    Regards,

    Aditya.

        
  • Jari Laine 14 Jan 2018  

    Hi Aditya,

    How you update values to table? Is correct value updated to table?

    Regards,
    Jari

        
  • Aditya 14 Jan 2018  

    Hi Jari,

    Thanks for the Post.

    I used the code as it is and every thing is working perfect in the cascading select list. When I clicked the "Submit" Button the lov value of the child lov is changing to last value of the select list instead of the one I selected. It would be of great help if you can help me.

    Regards,

    Aditya.

        
  • Stefania 11 Jan 2018  

    All right! I will try that! Thank you very much!

        
  • Jari Laine 10 Jan 2018  

    Hi Stefania,

    Well, that's require quite much more than this blog post example.
    Maybe you use e.g. autocomplete instead of select list and build solution around that.

    Regards,
    Jari

        
  • Stefania 10 Jan 2018  

    Hi Jari,

    On https://apex.oracle.com/en/

    workspace=NEBE

    user=STEFFY82IANCU@GMAIL.COM

    pass=STEFFY82

    You helped and provided a solution for me on page 4.Many thanks once again!

    On top of what we already have on page 4, I would need the following:

    If "Mess attribute" has some records table v1, then the column "Mess attribute val" should be displayed as LOV and filled as we do now.

    If "Mess attribute" has 0 records in table v1, then the column "Mess attribute val" should be displayed as editable text.

    Thank you,

    Steffy

        
  • Stefania 21 Dec 2017  

    Hi, Thank you for your help!

    Steffy

        

Global Right Column

Search

Archives

Subscribe in a Reader