As you can tell by the title, i've been a little side tracked over the past couple of weeks from the jqGrid integration (i've also been working on declarative menus based on APEX lists and trees).

A bit of background first... I have to admit that coming from Ext back to jQuery feels like stepping back in time. I'm not trying to cause offence it's just that Ext is backed by a large team of commercial developers and has a strong/tight community. You can argue the same for jQuery but it's behind Ext in functionality and support.

Recently I needed a searchable combo and I was wanting one that was AJAX enabled, allowed filtering and was styleable. The best one I could find for the task at hand was Sexy Combo, and it was nice that it shared a similar feel to Ext. My biggest issue is that I had to search for quite a while just to find a combo, which included reading reviews which resulted in me spending quite a bit of time just choosing this widget. This just drives me crazy!! I can see jQuery UI has got alot of widgets in the pipeline, I just wish it was more feature rich right now!

Now Sexy Combo is pretty light on functionality compared to Ext, it took me quite a while to work out how reload the contents from an AJAX call which returns a JSON object. For simplicity I decided the simplest way was to remove the combo from the DOM and recreate it as there was no function to load the combo with a JSON object, only one to create it.

To make the combo's declaratively cascadable I followed Patrcik Wolf's APEXLIB implementation and made a few tweaks. The rest of the post is dedicated to providing you the code to do this. The thing to note is that this code/approach might be useful in the case of defining combo plugins in APEX 4.0 which APEX may not be able to make dynamically cascadable.

Disclaimer: It's late on Sunday night and this is a long post and a bit of a mess but hopefully it's clear enough to follow the basic steps of what you need to do. The PLSQL might need a little debugging but there's enough here to get the whole approach working.

Pre-Requisities:

• PLJSON
• Sexy Combo jQuery Plugin
• Tyler Muth's jApex plugin

Ok whats required is 1 application level process which contains the following (the supporting PLSQL is further down in the post):



Set the following class for your select lists "ajq-sexy-combo" and add the following JS to your page header/template or or place in an external javascript file.

Note: the code below uses Tyler Muth's jApex plugin.

function jqSexyCombo(pIsTabForm) {

   jQuery('.ajq-sexy-combo').each(function () {
      var select = this;
      // Cascading LOV check for parent
      try {
         var isParent = (pIsTabForm) ? eval("jqTabFormCasLov." + select.name.toUpperCase()) : eval("jqCasLov." + select.id);
      } catch(e) {
         var isParent = null;
      }
      // Cascading LOV check for child
      try {
         var CasLov = (pIsTabForm) ? jqTabFormCasLov : jqCasLov;
         var isChild = new Array();
         for (var idx in CasLov) {
            var lovItems = CasLov[idx];
            for (var i = 0; i < lovItems.length; i++) {
               if (lovItems[i] == select.id || lovItems[i].toLowerCase() == select.name) {
                  isChild.push(idx);
               }
            }
         }
      } catch(e) {
         var isChild = new Array();
      }
      var comboid = select.id;
      var combovalue = jQuery(select).val();
      var addSubmitEvent = (select.className.indexOf("jq-key-submit") !== -1) ? true : false;
      jQuery(select).sexyCombo({
         suffix: "",
         hiddenSuffix: "",
         triggerSelected: true,
         width: 200,
         changeCallback: (isParent) ?
         function () {
            jqCasLovFetch(this, comboid)
         } : null
      });
   });
}
jqCasLovFetch = function (obj, comboid) {
   try {
      var CasLov = jqLov;
      var lBinds = new Array();
      for (var idx in CasLov) {
         var lovItem = CasLov[idx];
         for (var i = 0; i < lovItem.length; i++) {
            if (lovItem[i] == comboid || lovItem[i].toLowerCase() == comboid.substring(0, 3)) {
               lBinds.push(idx);
            }
         }
      }
   } catch(e) {
      var lBinds = new Array();
   }
   if (lBinds.length > 0) {
      var bindItems = lBinds.join(';;');
      jQuery.jApex.ajax({
         appProcess: 'jq.lov.getLovJSON',
         x01: bindItems,
         x02: comboid,
         x03: obj.getCurrentHiddenValue(),
         success: function (data) {
            var jsonObj = JSON.parse(data);
            for (var idx in jsonObj) {
               var selectJSON = eval("jsonObj" + "." + idx + ".row");
               var selectName = jQuery('#' + idx).attr("name");
               var selectId = jQuery('#' + idx).attr("id");
               var selectContainer = jQuery('#' + idx).parent();
               var selectValue = jQuery('#' + idx).val();
               // We need to delete the DOM nodes as we will re-add them, as there's no
               // load function to load a JSON object unfortunately
               jQuery('#' + idx).remove();
               jQuery('[name=' + selectName + ']').remove();
               var newCombo = jQuery.sexyCombo.create({
                  name: selectName,
                  id: selectId,
                  suffix: "",
                  hiddenSuffix: "",
                  triggerSelected: true,
                  value: "DISPLAY_VALUE",
                  key: "RETURN_VALUE",
                  container: selectContainer,
                  initialHiddenValue: selectValue,
                  data: selectJSON,
                  width: 200
               });
               // We may ned to set the combo value if it's in the list
               for (var i = 0; i < selectJSON.length; i++) {
                  if (selectJSON[i].RETURN_VALUE == selectValue) {
                     newCombo.setComboValue(selectJSON[i].DISPLAY_VALUE);
                  }
               }
            }
         }
      });
   }
}
jQuery(document).ready(function(){
   jqSexyCombo(false);
});

and for a non Sexy combo implementation use the following

function jqCasLovInit(pIsTabForm) {
         try { var CasLov = (pIsTabForm) ? jqTabFormCasLov : jqCasLov; } catch(e) { var CasLov = {}; }
         for (var idx in CasLov) {
            jQuery('#'+idx).bind('change', function() { jqCasLovBind(jQuery('#'+idx).val(),idx); });
         }
}
// Cascading LOV fetch event for parent LOV's
jqCasLovBind = function (val, comboid) {
   try {
      var CasLov = jqLov;
      var lBinds = new Array();
      for (var idx in CasLov) {
         var lovItem = CasLov[idx];
         for (var i = 0; i < lovItem.length; i++) {
            if (lovItem[i] == comboid || lovItem[i].toLowerCase() == comboid.substring(0, 3)) {
               lBinds.push(idx);
            }
         }
      }
   } catch (e) {
      var lBinds = new Array();
   }
   if (lBinds.length > 0) {
      var bindItems = lBinds.join(';;');
      jQuery.jApex.ajax({
         appProcess: 'jq.lov.getLovJSON',
         x01: bindItems,
         x02: comboid,
         x03: val,
         success: function (data) {
            var jsonObj = JSON.parse(data);
            for (var idx in jsonObj) {
               var selectJSON = jsonObj[idx]["row"];
               jQuery('#'+idx).children().remove();
               for ( var i=0; i < selectJSON.length; i++ ) {
                 $x(idx).options[i] = new Option(jsonObj[idx]["row"][i].DISPLAY_VALUE, jsonObj[idx]["row"][i].RETURN_VALUE);
               }
            }
         }
      });
   }
}
jQuery(document).ready(function(){
   jqCasLovInit(false);
});

As for making it cascading and declarative I've used a modified version of APEXLIB which fetches the LOV results based on an delimited item list passed in (x01), the difference is that I return all the dependant LOV results in a single JSON object. What's required in the page header is a couple of JSON objects to work out whether the item is a parent LOV and needs to have a "fetch" event binded or a child

var jqLov = {
   "P4_ITEM2": ["P4_ITEM1"],
   "P4_ITEM3": ["P4_ITEM1"]
};
var jqCasLov = {
   "P4_ITEM1": ["P4_ITEM2", "P4_ITEM3"]
};

jqLov lists all tems the list is dependent on whilst jqCasLov lists all items which depend upon it. The above meta data objects are built by calling the following PLSQL procedure called "After Header" - jq_form.p_form_caslovs_json; the underlying PLSQL can be found at the bottom of the post. It's best defining this on page zero.


The result that we expect back from our application process when an onchange/fetch event fires is a JSON object which contains the results of our dependent combos which is in the form of:

{
   "P4_ITEM2": {
      "row": [{
         "DISPLAY_VALUE": "Label 1",
         "RETURN_VALUE": "130"
      },
      {
         "DISPLAY_VALUE": "Label 2",
         "RETURN_VALUE": "131"
      },
      {
         "DISPLAY_VALUE": "Label 3",
         "RETURN_VALUE": "132"
      }]
   },
   "P4_ITEM3": {
      "row": [{
         "DISPLAY_VALUE": "Label 1",
         "RETURN_VALUE": "10"
      },
      {
         "DISPLAY_VALUE": "Label 2",
         "RETURN_VALUE": "11"
      },
      {
         "DISPLAY_VALUE": "Label 3",
         "RETURN_VALUE": "12"
      }]
   }
}

The above JSON object is generated by the On Demand Application Process which we defined earlier, the supporting PLSQL can be downloaded here: caslov.zip

If you have any major issues post them via a comment and I'll look to respond ASAP. Remember to install the pre-requistes first before installing the caslov.sql file.