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.

Comments

  • 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

        
  • Jari Laine · 20 Dec 2017  

    Hi Stefania,

    Check your example page 4.

    I did modify JavaScript function and also call to that in dynamic actions.
    See also report columns LOV SQL query and On Demand processes.

    I did change on demand process use Oracle supported APEX_JSON package.

    Regards,
    Jari

        
  • Stefania · 20 Dec 2017  

    The requirement is to load child LOV2 (depends on LOV1) and child LOV3 (depends on LOV2) with some pre-defined values in some situations. It might happen that LOV2 and/or LOV3 are empty or contain pre-defined values but the user decides to add a new value.

    The purpose is to give the users two options:

    1. choose a pre-defined value

    2. create a new value for the LOV.

        

Global Right Column

Search

Archives

Subscribe in a Reader