Following on from a previous post where we highlighted the fact that we couldn’t use APEXLIB for cascading LOV’s on our Ext transformed select lists, we wrote our own version to do the job but it didn’t include support for Tabular forms in our Ext grid… well it didn’t until now!

The implementation was quite tricky and we’re interested if there’s a better way to do it, but with our approach we wanted to ensure the following:

1. We wanted to reuse as much code as possible from our simple form cascading LOV solution, which reused code from Patrick Wolfs APEXLIB framework
2. We wanted the implementation to be a generic write once approach
3. We wanted to reuse the LOV definitions for the report columns and not from any specific hidden items on the page, as using hidden items on a page would require some setup each time
4. We wanted to support additional “Ext vtype” dynamic validations using the comments section

So essentially we had some pretty complex requirements. The main problem we had to work around was the fact that the INPUT items generated by APEX for the Tabular Form had id’s like “f01_0001″ and a name of “f01″, so basically the “name” represented the column and the “id” represented the column row number. What we needed to do was work out the translation of the id/name into the report column name…. the following query does just that!

SELECT to_number(v('APP_ID'))                       application_id
,      to_number(v('APP_PAGE_ID'))                  page_id
,      region_id                                    region_id
,      'F'||lpad(html_item_sequence,2,'0')          name
,      display_sequence                             display_sequence
,      display_as                                   display_as
,      lov_definition                               lov_query
,      lov_display_null                             lov_display_null
,      lov_null_text                                lov_null_text
,      lov_null_value                               lov_null_value
FROM
(SELECT v.column_alias
 ,      LTRIM(v.inline_list_of_values) lov_definition
 ,      upper(v.lov_show_nulls)        lov_display_null
 ,      v.lov_null_text
 ,      v.lov_null_value
 ,      v.display_as
 ,      v.display_sequence
 ,      v.region_id
 ,      v.format_mask
 ,      row_number() over (
           partition by v.region_id
           order by to_number(decode(v.display_as
                                    , 'Display as Text (based on LOV, does not save state)', null
                                    , 'Display as Text (escape special characters, does not save state)', null
                                    , 'Standard Report Column', null
                                    , t.query_column_id)) nulls last
      ) html_item_sequence
FROM   apex_application_page_rpt_cols v
,      wwv_flow_region_report_column  t
,      apex_application_page_regions  r
WHERE  r.source_type = 'Tabular Form'
AND    v.application_id    = t.flow_id
AND    v.region_id         = t.region_id
AND    v.column_alias      = t.column_alias
AND    v.region_id         = r.region_id
AND    v.application_id    = v('APP_ID')
AND    v.page_id           = v('APP_PAGE_ID')

In order to support holding the bind item values in session state (as we are not using any page items and we are reusing code from APEXLIB) we needed to create application level items F01 to F49 as the code looks to update session state based on the bind item name. This is then what our LOV queries look like under the Tabular Form column attributes:

select company_name, company_id
from   company
where  division_id = :F04
order by 1

So now that we have this translation mapping for our column names we then use it to build our JSON meta data objects in the page header which define the bind variables in use for each of the LOV’s dependencies (i.e. children) and the list of LOV’s that have dependencies (i.e. parents) e.g.

var extLovChildren = {
   "F05": ["F04"],
   "F06": ["F05"],
   "F07": ["F06"],
   "F08": ["F04", "F05", "F07"]
}
var extLovParents = {
   "F04": ["F05", "F08"],
   "F05": ["F06", "F08"],
   "F06": ["F07"],
   "F07": ["F08"]
}

We then use/query the above JSON meta data when we perform the Ext transform of our select lists, within the Ext grid region, to identify which ones to add onclick events for, i.e. the parent LOV’s, and which ones need a JSON store i.e. the children, as we will reload the data in the store for the children. Remember when we interrogate our INPUT items they will have the name of “f01″ or “f02″ etc. so we simply use the INPUT html elements name to reference our JSON object e.g.

eval("extLovParents." + select.dom.name.toUpperCase())

The next tricky bit is that when our “select” event fires we need to load the returning JSON object which will have a list of our child LOV’s and their data which needs to be loaded into their Ext stores. This is what the returning JSON object looks like:

{
   "F05": {
      "rows": [{
         "display_value": "Please Select...",
         "return_value": -1
      },
      {
         "display_value": "Option 1",
         "return_value": "160"
      },
      {
         "display_value": "Option 2",
         "return_value": "161"
      },
      {
         "display_value": "Option 3",
         "return_value": "4"
      }]
   },
   "F08": {
      "rows": [{
         "display_value": "Please Select...",
         "return_value": -1
      }]
   }
}

And here’s what the store looks like for the child LOV

var lovStore = new Ext.data.Store({
   id: 'store' + comboid,
   reader: new Ext.data.JsonReader({
      root: pIsTabForm ? comboid.substring(0, 3).toUpperCase() + ".rows" : comboid + ".rows"
   },
   [{
      name: 'display_value'
   },
   {
      name: 'return_value'
   }])
});

Here’s a code fragment which adds the onclick event for the AJAX post & combo reload (we essentially loop through each of the indexes in the returning JSON object and reload the combo JSON stores based on the nested object) and set the value of the combo to the first item in the returned list:

combo.on("select", function (combobox, record, index) {
  var bindItems = lBinds.join(';;');
  var jsonObj = Ext.util.JSON.decode(Ext.app.apexHttpPost('Ext.lov.getLovJSON', new Array('EXT_REFERENCE_ID', comboid.substring(0,3).toUpperCase()), new Array(bindItems, combobox.getValue())));
  for (var idx in jsonObj) {
    Ext.getCmp(idx.toLowerCase()+comboid.substring(3,comboid.length)).getStore().loadData(jsonObj);
    Ext.getCmp(idx.toLowerCase()+comboid.substring(3,comboid.length)).setValue(eval("jsonObj."+idx+".rows[0].return_value"));
  }
});

In the above we pass through a delimited list of the items (f04,f05 etc.) which reference the current item (e.g. f01) and this will stored in session state in the application item “EXT_REFERENCE_ID”. We also pass through the current item (e.g. f01) and its value which updates the application item “F01″ in session state. We then use server side PLSQL (mostly APEXLIB) to (re)execute the LOV queries for the delimited list of the items (f04,f05 etc.) and return them in a single JSON object (our customized code) which is then processed and used to reload dependent LOV stores as mentioned earlier in the post.

The end result is a write once approach and enabled out of the box just by using bind names in your LOV queries, and whilst we have to create a significant number of application items F01-F49 we negate this setup cost by building these into our base template application. The combination of JSON, PLSQL, and Ext really reduces the development overheads and presents solutions for once thought of impossibilities.