Convert APEX 4.2 tabular form popup key LOV to jQuery autocomplete

Currently in APEX 4.2.4 there is no native autocomplete item for tabular from. If you need one, you can use e.g. jQuery UI autocomplete widget.

Here is how you can enable autocomplete for APEX tabular form Popup Key LOV items. This solution can be used for Popup LOV (query based LOV) or Popup Key LOV (named LOV) if named LOV source is query.

First create tabular from using wizard. In this example I did create tabular form top of EMP table.

Edit page attributes and add to page JavaScript file URLs

#IMAGE_PREFIX#libraries/jquery-ui/1.8.22/ui/minified/jquery.ui.autocomplete.min.js

And page JavaScript Function and Global Variable Declaration

/* array for search result cache */
var mgrCache = {};

(function ($) {

$.fn.htmldbAutoComplete = function (opt, optui) {

  var lSelf = this;

  // Result highlight
  $.ui.autocomplete.prototype._renderItem = function (ul, item) {
   var term = this.term.split(' ').join('|');
   var re = new RegExp("(" + term + ")", "gi");
   var t = item.label.replace(re, "<b>$1</b>");
   return $("<li></li>")
   .data("item.autocomplete", item)
   .append("<a>" + t + "</a>")
   .appendTo(ul);
  };

  // Autocomplete options
  optui = $.extend({
    delay : 500,
    change : function (ev, ui) {
     $(this).val() ? ui.item || __setmyval(this, $(this).data("last-value"), $(this).siblings("input").data("last-value")) : __setmyval(this, null, null);
    },
    select : function (ev, ui) {
     __setmyval(this, ui.item.value, ui.item.retval);
    },
    source : function (req, res) {
     if (opt.cacheVar) {
      if (req.term in opt.cacheVar) {
       res(opt.cacheVar[req.term]);
       return;
      }
     }
     apex.server.process(opt.process, {
      x01 : req.term,
      x02 : this.element.data("col-name")
     }, {
      success : function (data) {
       opt.cacheVar ? (opt.cacheVar[req.term] = data.row, res(opt.cacheVar[req.term])) : res(data.row);
      }
     });
    }
   }, optui);

  $("form").submit(function () {
   lSelf.attr("disabled","disabled");
  });

  opt.removeIcon && lSelf.siblings("a").remove();

  return lSelf
  .removeAttr("disabled")
  .removeAttr("onfocus")
  .autocomplete(optui)
  .each(function () {
   __setmylastval(this);
   $(this).siblings("input").change(function() {
    __setmylastval(this);
   });
   this.onfocus = null;
  })
  .change(function () {
   $(this).val() || __setmyval(this, null, null);
  });

  /* private functions */
  function __setmyval(pThis, pDis, pRet) {
   $(pThis).val(pDis).data("last-value", pDis).siblings("input").val(pRet).data("last-value", pRet);
  }

  function __setmylastval(pThis){
   var lThis = $(pThis), lThat = lThis.siblings("input");
   lThis.data("last-value", lThis.val());
   lThat.data("last-value", lThat.val()); 
  }

}

})(apex.jQuery);

First line var mgrCache = {}; is needed if you like cache autocomplete search results.

Change "Add Row" button action to Defined by Dynamic Action.

Edit tabular form and change e.g. MGR column Display As to Popup LOV (query based LOV).
Add to Element Attributes

data-col-name="MGR"

And List of values definition

SELECT ENAME
  ,EMPNO
FROM EMP

Create On Demand process called GET_LOV

DECLARE
  l_cursor	PLS_INTEGER;
  l_status	PLS_INTEGER;
  l_col_cnt PLS_INTEGER;
  l_row_cnt	SIMPLE_INTEGER := 0;
  l_column	dbms_sql.desc_tab2;
  l_value	VARCHAR2(32767);
  l_sql		VARCHAR2(32767);
BEGIN

  /* Get LOV query */
  SELECT COALESCE(c.inline_list_of_values, l.list_of_values_query) as qry
  INTO l_sql
  FROM apex_application_page_rpt_cols c
  LEFT JOIN apex_application_lovs l
	ON c.named_list_of_values = l.list_of_values_name 
	AND l.application_id = :APP_ID
  WHERE c.application_id = :APP_ID
  AND c.page_id = :APP_PAGE_ID
  AND c.column_alias = APEX_APPLICATION.G_x02
  ;
  
  /* Get LOV query columns */
  l_cursor := dbms_sql.open_cursor;
  dbms_sql.parse (l_cursor, l_sql, dbms_sql.native);
  dbms_sql.describe_columns2(l_cursor, l_col_cnt, l_column);
  dbms_sql.close_cursor(l_cursor);
  
  /* Check that query do have return and display value columns */
  IF l_column.count != 2 THEN
    raise_application_error (-20001, 'Invalid list of values query');
  END IF;
  
  /* Add autocomplete search to LOV query */
  l_sql := 'SELECT * FROM (' 
	|| l_sql 
	|| ') WHERE INSTR(UPPER('
	|| l_column(1).col_name
	|| '), :term) > 0'
	|| ' ORDER BY 1'
	;
  
  l_cursor := dbms_sql.open_cursor;
  dbms_sql.parse (l_cursor, l_sql, dbms_sql.native);
  dbms_sql.bind_variable(l_cursor, ':term', UPPER( APEX_APPLICATION.G_x01));
  dbms_sql.describe_columns2(l_cursor, l_col_cnt, l_column);
  
  FOR i IN 1 .. l_column.count
  LOOP
    dbms_sql.define_column(l_cursor, i, l_value, 32767);
  END LOOP;
  
  l_status := dbms_sql.execute(l_cursor);
  
  /* Construct return data */
  LOOP
    EXIT
  WHEN (dbms_sql.fetch_rows(l_cursor) <= 0 );
 
	l_row_cnt := l_row_cnt + 1;
    IF l_row_cnt = 1 THEN
      htp.prn('{"row":[{');
	ELSE
	  htp.prn(',{');
    END IF;
	
	FOR i IN 1 .. l_column.count
    LOOP
      dbms_sql.column_value(l_cursor, i, l_value);
      IF i = 1 THEN
        htp.prn('"value":"' || l_value || '"');
      ELSIF i = 2 THEN
        htp.prn(',"retval":"' || l_value || '"}');
      END IF;
    END LOOP;	
  END LOOP;

  IF l_row_cnt = 0 THEN
	htp.prn('{"row":[]}');
  ELSE
	htp.prn(']}');
  END IF;
  
  dbms_sql.close_cursor(l_cursor);
  
END;

Create dynamic action

• Name: Set autocomplete
• Event: After Refresh
• Selection Type: Region
• Region: {select tabular form region}
• Condition: -No Condition-
• Action: Execute JavaScript code
• Fire On Page Load: True
• Code:

  this.affectedElements.find("input[data-col-name=MGR]")
  .htmldbAutoComplete({
   process:"GET_LOV", /* On demand process name */
   removeIcon:false,  /* Remove popup lov icon true/false */
   cacheVar:mgrCache  /* Global variable name to hold search result cache. This is optional */
  });
  

• Selection Type: Region
• Region: {select tabular form region}

Create another dynamic action

• Name: Add row
• Event: Click
• Selection Type: Button
• Button: {select Add Row button}
• Condition: -No Condition-
• Action: Execute JavaScript code
• Fire On Page Load: False
• Code:

  apex.widget.tabular.addRow();
  this.affectedElements.find("input[data-col-name=MGR]:last")
  .htmldbAutoComplete({
   process:"GET_LOV",
   removeIcon:false,
   cacheVar:mgrCache
  });
  

• Selection Type: Region
• Region: {select tabular form region}

Run page and type e.g. A to MGR column field to see autocomplete in action.

See working example.

You can also download sample.