Implementing an ExtJS Combobox in Apex (pt. 1/2)

Select lists are still quite limited in Apex. The following blogposts give a tutorial how to implement an enhanced select list with the usage of the ExtJS-Framework.

This combobox will contains a freely html-formated (even with conditional logics) select list and features autocompletion and pagination. Here's an example screenshot:

Apex Item-Plugin including an ExtJS Combobox

The implementation will be described in two parts. The first blogpost contains the basics of integrating an ExtJS-Combobox as item plugin. The second post covers the implementation of the remote data store within this widget.

The code is tested with Apex 4.x and ExtJS 4.1.1a. Keep an eye on the version level of your installed ExtJS. Each release from Sencha is "kind of ground shaking".. for example the used parameter "hiddenName" was established in Ext3. With Ext4 it was firstly removed, later reintroduced again, but not working correctly. By now it handles like in Ext3 again. Unfortunately this is by far not a single case in the lifecycle of ExtJS.  

Now let's get started with a ExtJS combobox based on a simple local data store. At first create the following package to provide the callback functions for the item plugin. When using the local data store we just need a render function at the moment.

create or replace package pkg_plugin_ext_combobox as

  subtype typeAttr is apex_application_page_items.attribute_01%type;
  
  function fRender (p_item                in apex_plugin.t_page_item,
                    p_plugin              in apex_plugin.t_plugin,
                    p_value               in varchar2,
                    p_is_readonly         in boolean,
                    p_is_printer_friendly in boolean) return apex_plugin.t_page_item_render_result;

end pkg_plugin_ext_combobox;

create or replace
PACKAGE BODY  pkg_plugin_ext_combobox AS


function fRender
    (p_item in apex_plugin.t_page_item, p_plugin in apex_plugin.t_plugin, p_value in varchar2,
     p_is_readonly in boolean, p_is_printer_friendly in boolean) 
    return apex_plugin.t_page_item_render_result is

   pDisplayColumn typeAttr := p_item.attribute_01;
   pValueColumn   typeAttr := p_item.attribute_02;
   pItemWidth     typeAttr := p_item.attribute_03;
   pEmptyText     typeAttr := p_item.attribute_05;
   pXTemplate     typeAttr := p_item.attribute_06;

   vResult        apex_plugin.t_page_item_render_result;  

   vItemValue     varchar2(32767) := sys.htf.escape_sc(p_value);
   vItemName      varchar2(200);
   vJsCode        varchar2(32767);
   vColumns       sys.dbms_sql.desc_tab2;

begin

   if (apex_application.g_debug) then
      apex_plugin_util.debug_page_item (p_plugin => p_plugin, p_page_item => p_item, p_value => p_value,
            p_is_readonly => p_is_readonly, p_is_printer_friendly => p_is_printer_friendly );
   end if;

   vItemName := apex_plugin.get_input_name_for_page_item(false);

   sys.htp.p('<select id="' || p_item.name || '"></select>'); 

   vJsCode := q'^
      Ext.onReady(function()
      {  Ext.define("vModel_#ITEM_ID#",
              { extend: "Ext.data.Model",
                fields: ["NAME","EMPLOYEE_ID","PHONE_NUMBER", "SALARY"]
              });
   
         vStore_#ITEM_ID# = Ext.create("Ext.data.Store",
         {   model: "vModel_#ITEM_ID#",
             idProperty: "EMPLOYEE_ID",
             data: [{ "NAME": "Ben Nebelt", "EMPLOYEE_ID": 10, "PHONE_NUMBER": '10-10', "SALARY": 4000},
                    { "NAME": "Karl Toffel", "EMPLOYEE_ID": 20, "PHONE_NUMBER": '10-20', "SALARY": 5000},
                    { "NAME": "Volker Nbrot", "EMPLOYEE_ID": 30, "PHONE_NUMBER": '10-30', "SALARY": 6000}]
         });

         var vCombo_#ITEM_ID# = Ext.create('Ext.form.field.ComboBox', 
         {   transform: '#ITEM_ID#',
             store: vStore_#ITEM_ID#,
             value: '#VALUE#',
             queryMode: 'local',
             displayField: '#DISPLAY_COLUMN#',
             valueField: '#VALUE_COLUMN#',
             hiddenName: '#ITEM_NAME#',
             emptyText: '#EMPTY_TEXT#',
             #XTEMPLATE#
             selectOnFocus: true,
             minChars: 1,
             listConfig:
             {  resizable: true,
                resizeHandles: 'all',
                maxHeight: 400,
                maxWidth: 800
             },
             width: #WIDTH#
         });
    });
      ^';

   wwv_flow_utilities.fast_replace(vJsCode, '#ITEM_ID#', p_item.name);
   wwv_flow_utilities.fast_replace(vJsCode, '#ITEM_NAME#', vItemName);
   wwv_flow_utilities.fast_replace(vJsCode, '#VALUE#', vItemValue);
   wwv_flow_utilities.fast_replace(vJsCode, '#DISPLAY_COLUMN#', pDisplayColumn);
   wwv_flow_utilities.fast_replace(vJsCode, '#VALUE_COLUMN#', pValueColumn);
   wwv_flow_utilities.fast_replace(vJsCode, '#EMPTY_TEXT#', pEmptyText);
   wwv_flow_utilities.fast_replace(vJsCode, '#WIDTH#', pItemWidth);
   wwv_flow_utilities.fast_replace(vJsCode, '#XTEMPLATE#', pXTemplate);

   apex_javascript.add_onload_code(p_code => vJsCode);
 
   vResult.is_navigable := true;
  
   return (vResult);

end fRender;

end pkg_plugin_ext_combobox;

I will focus on the characteristics and pitfalls of integrating an ExtJS combobox in Apex. So you should be already experienced with the basics of Apex Plugins and the ExtJS framework.

Mainly the function posts a select item containing only the id (line44), which will be transformed to the ExtJS combobox (line 62). So most features will come from ExtJS (but for example the item labeling is still apex standard).  

The code basis is already adjusted for implementing the remote store. You can see that i.e. the "displayField" and "valueField" are set through the item plugin attributes although the (temporary) local data store is hardcoded. To keep the javascript code more readable, i include placeholders (#..#) for the dynamic attributes and replace them afterwards via "wwv_flow_utilities.fast_replace".

In general, mostly the standards for an apex item plugin respectively ExtJS combobox are used. The combobox is resizable (line 73-78) and includes the possibility to use a XTemplate (line 70). To get along with the combobox, create the apex item plugin now. I will only list the specific settings for the plugin, all other can remain to default (or your own choice).

Item plugin "extComboBox"

• Render Function Name: "pkg_plugin_ext_combobox.fRender"
• Standard Attributes: enable at least "Is visible widget", "Has source attributes", "Has list of values" to be prepared for the remote store. And, all-important, set the attribut "Maximum columns" to "999", so the forthcoming lov sql's can return more than 2 columns.
• Component attributes:

1. Attribut "Display column"
   Type "Text", Required "Yes", Display Width "20", Maximum Width "40"
   Help text "column name from data store to be displayed in combobox"
2. Attribut "Value column"
   Type "Text", Required "Yes", Display Width "20", Maximum Width "40"
   Help text "column name from data store to be submitted from combobox"
3. Attribut "Item width (px)"
   Type "Integer", Required "No", Display Width "4", Maximum Width "4", Default value "250"
   Help text "Width of combobox in pixels"
4. Attribut "Records per page"
   Type "Integer", Required "No", Display Width "4", Maximum Width "4", Default value "25"
   Help text "Number of records showed per page"
5. Attribut "Empty text"
   Type "Text", Required "No", Display Width "20", Maximum Width "40", Default value "<please select>"
   Help text "Displayed text when item value is null"
6. Attribut "XTemplate"
   Type "Textarea", Required "No", Display Width "", Maximum Width "2000"
   Help text "ExtJS Xtemplate"

Now create an apex item based on the plugin. In the plugin attributes set the "Display column" to "NAME" and the "Value column" to  "EMPLOYEE_ID". If the attribut "XTemplate" is not set, the select list is unformatted and only the display column is listed. To get an impression how powerful XTemplate's are, you could enter the following code:

tpl: Ext.create('Ext.XTemplate', ['<tpl for=".">',
            '<div style="margin: 4px;" class="x-boundlist-item">',
            '<div><b>{NAME}</b></div>',
            '<div>Phone : {PHONE_NUMBER}</div>',
            '<div style="color: {[values.SALARY < 5000 ? "red" : "black"]};">Salary : ${SALARY}</div>',
            '<div style="font-size: xx-small; color: grey;">(ID = {EMPLOYEE_ID})</div>',
            '</div>',
            '</tpl>']),

With XTemplate you can loop through your records and format the output via html. Even conditional logic is possible, see line 5 where all salaries less than 5000 will be marked red. Mostly important for the apex integration is to include the class "x-boundlist-item", otherwise the list entries are not selectable!

Now then, the combobox should look like this:

The result of this blogposting

In the next blogpost we will replace the local data store with a remote one. Things are getting interesting..:-)