In a previous post we talked about how we integrated the tabular form into an Ext Grid purely by the Region and Report templates and that in doing so we were restricted from using a Popup LOV. This was due to the fact that the APEX engine generates the javascript inline in the report which broke our template. Our intention with the APEX ExtJS integration is to not give up any functionality we’re looking to enhance it, hence why we are focused on providing an alternative if we can’t support some existing APEX functionality!

This post is dedicated to working around that problem by simply using a class setting in the “Form Element Options” section under the report definition to build our own Ext flavoured Popup LOV.

Remember we’re not going to tell you step by step how to do it, it would take a few blog pages (and I don’t have the time) besides what do you learn from spoon feeding! We’ll simply give you the high level approach and give you any gotchya’s to watch out for.

First step for the popup LOV replacement, we need to build the javascript function, similar to our date and text area enhancement, which will take a normal text field and transform it into a nice search popup with the added bonus of pagination all via AJAX. See the following Ext demo for a working model and javascript widget source. Here’s an extract of ours:

function extPopupLOV(pID, pRegionID) {

   // define widget URL
   var u = (window.location.href.indexOf("?") > 0) ? window.location.href.substring(0, window.location.href.indexOf("?")) : window.location.href;
   var baseURL = u.substring(0, u.lastIndexOf("/"));
   var l_RegionID = new String(pRegionID);
   l_RegionID = l_RegionID.substr(1, l_RegionID.length);
   var widgetURL = baseURL + '/wwv_flow.show?p_flow_id=' + Ext.getDom('pFlowId').value + '&p_flow_step_id=' + $v('pFlowStepId') + '&p_instance=' + Ext.getDom('pInstance').value + '&p_request=APPLICATION_PROCESS=Ext.widget';

   var els = Ext.get(pID).select("[class=ext-form-popup-lov]");
   els.each(function(el) {
      if (el.dom.className.indexOf("aext-form-popup-lov") == -1) {
         el.dom.className += " " + "aext-form-popup-lov";
         var ds = new Ext.data.Store({
            proxy: new Ext.data.HttpProxy({
               url: widgetURL
            }),
............

         var search = new Ext.form.ComboBox({
            store: ds,
            displayField: 'title',
            typeAhead: false,
            loadingText: 'Searching...',
            queryParam: 'x03',
etc.

The following overrides were required to ensure we changed our parameter names when the AJAX post was performed. Note: we use the same parameter name mapping in the Grid paging so they won’t be affected by this override.

/*
   Paging Toolbar Override for parameter names

http://extjs.com/forum/showthread.php?t=17403

*/
Ext.override(Ext.PagingToolbar, {
   paramNames: {
      start: 'x01',
      limit: 'x02'
   }
});
Ext.form.ComboBox.override({
    getParams : function(q){
        var p = {};
        if(this.pageSize){
            p['x01'] = 0;
            p['x02'] = this.pageSize;
        }
        return p;
    }
});

Secondly we need to write a little PLSQL behind the scenes to grab our list of values query from the column definition and allow filtering upon it, execute it, and return the data back in a JSON object. This uses the same Widget Application process previously described in an earlier post. This again highlights the beauty of your APEX application being just metadata, all we have for the item is a name e.g. “f03″ but we can use this to query the data dictionary (along with the region id defined in the grid) to determine which column it is and what the defined LOV query is which we will execute and return back in a JSON object. Here’s the main APEX/Oracle elements that are required to achieve it!

• apex_application_page_rpt_cols – we need to locate the LOV query
• wwv_flow_region_upd_report_column – we need to use this table to work out which column the HTML item belongs to, i.e. query_column_id
• dbms_sql.describe_columns – we need to describe our LOV query to work out the column to perform our filter on
• apex_util.json_from_sql – we need to build a JSON object from the query

We then added the calls “extPopupLOV(‘grid’+pRegionID,pRegionID);” to our “Add Rows” javascript function on the tabular form, and also to the “pStore.on({load” function when the grid renders. That’s it!

I’d like to highlight that this has taken roughly 6 hours to complete for a 100% reusable design which simply requires you to set a class attribute in the “Form Element Options” field to enable the functionality, so when you’re thinking about weighing up the options to use ExtJS or not, bottom line is it’s going to save you a hell of a lot of time whilst producing a better end result. I think you’ll be making more than just your boss happy! If you think it’s too complicated to use, remember everything new takes a little time to get your head around, when you started with APEX were you a guru from day 1? And once you understand what it means to have you data in JSON objects easily accessible and event based you’ll see that you will reduce round trips to the server and open up interaction with a lot more widgets (not just Ext) and Third Party applications, like Google for instance.