Single Article

Convert APEX 4.2 tabular form popup key LOV to jQuery autocomplete

Category APEX and jQuery

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.

Tabular form autocomplete

Comments

  • Jari Laine · 13 Jun 2017  

    Hi ferdous,

    What actions you have take to debug problem?

    Regards,
    Jari

        
  • ferdous · 13 Jun 2017  

    i got Error: SyntaxError: Unexpected token s in JSON at position 0, i need solve in apex 5.1

        
  • Jari Laine · 8 Sep 2016  

    Hi Michael,

    Actually I have not done that kind solution yet, so can't give any advice. Sorry

    Regards,
    Jari

        
  • Michael · 5 Sep 2016  

    Hi Jari,

    Thanks for this post. It is really helpful. I only have one question as I'm not that familiar with javascript, etc. Hopefully you can help me out with this. I have the autocomplete working for two attributes in my tabular form, but I want to be able to enter values that don't exist in the LOV query. Could you guide me and let me know what I have to change in the javascript to get this done?

    Thanks!

    Michael

        
  • tony miller · 16 Jan 2016  

    Trying to take the code for auto complete text boxes into your alternative tabular forms using an interactive report.

    If you have a free moment, could you suggest how to mesh the two so they will work?

    I have looked at this and looked at this and am at a stand still..

        
  • Jari Laine · 18 Dec 2015  

    Hi Anders,

    Kiitos =)

    Can you see any error messages in browser console?

    Could you please replicate problem in apex.oracle.com and share developer login details to workspace.

    Regards,
    Jari

        
  • Anders · 18 Dec 2015  

    Hyvä Jari!

    We have had some help to get the LOV working i APEX5, see Jonas previous posts.

    Now we have migrated the theme to Universal an suddenly the Navigation Bar List stopped working.

    The menu is visible BUT it dont open if one have the Autocomplete Popup LOV on the same page. Do you think there is a solution for that problem?

        
  • Jari Laine · 27 Nov 2015  

    Hi Bren,

    Did you add to page JavaScript file URL below line?

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

    Regards,
    Jari

        
  • Bren · 27 Nov 2015  

    Hi Jari,

    Thanks for this solution, however I cant seem to access the jquery library that contains ui.autocomplete

    It generates an error that says

    $.ui.autocomplete is undefined

    Im using Oracle 4.2

    Thanks

    Bren

        
  • sophiebelk · 13 Oct 2015  

    I would like to thank you for your help

    I've download you example and it was so helpful for me

    in fact I have changed "data-col-name" to ARTICLE_ID

    it's my column name

    Sophia

        

Global Right Column

Search

Archives

Subscribe in a Reader