Skip to Main Content
Posted on

Return multiple value using jQuery UI autocomplete

Category APEX and jQuery

Here is how you can return multiple values when using jQuery UI autocomplete.

You can see working example from here.

First create text item P46_SEARCH where we set autocomplete. Then create display only items P46_ENAME, P46_EMPNO, P46_JOB and P46_MGR where return values from autocomplete.

Next create On Demand process called EMP_DATA:

DECLARE
  l_sql VARCHAR2(32000);
BEGIN

  l_sql := '
    SELECT e.ename,
      e.empno,
      e.job,
      e.mgr
     FROM emp e
    WHERE UPPER(e.ename) LIKE ''' || UPPER(APEX_APPLICATION.G_x01) || '%' || '''
    ORDER BY 1
  ';

  APEX_UTIL.JSON_FROM_SQL(l_sql);

EXCEPTION
  WHEN OTHERS THEN
    HTP.p ('{"row":[]}');
END;

Then add to page HTML header JavaScript:

<script type="text/javascript">
$(function(){
 $("#P46_SEARCH").autocomplete({
  source: function(req,src){
   $.ajax({
    type:"POST",
    url:"wwv_flow.show",
    dataType:"json",
    data:{
     p_flow_id:"&APP_ID.",
     p_flow_step_id:"&APP_PAGE_ID.",
     p_instance:"&APP_SESSION.",
     p_request:"APPLICATION_PROCESS=EMP_DATA",
     x01:req.term
    },
    success:function(jd){
     src($.map(jd.row,function(jr){return{value:jr.ENAME,empno:jr.EMPNO,job:jr.JOB,mgr:jr.MGR}}));
    }
   });
  },
  focus:function(ev,ui){return setItems(this,ui);},
  select:function(ev,ui){return setItems(this,ui);}
 });
});
function setItems(pThis,ui){
 $s(pThis,ui.item.value);
 $("#P46_ENAME").text(ui.item.value);
 $("#P46_EMPNO").text(ui.item.empno);
 $("#P46_JOB").text(ui.item.job);
 $("#P46_MGR").text(ui.item.mgr);
 return false;
}
</script>

If you like have text item instead display only item for P46_ENAME, P46_EMPNO, P46_JOB and P46_MGR, change JavaScript function setItems to be:

function setItems(pThis,ui){
 $s(pThis,ui.item.value);
 $s("P46_ENAME",ui.item.value);
 $s("P46_EMPNO",ui.item.empno);
 $s("P46_JOB",ui.item.job);
 $s("P46_MGR",ui.item.mgr);
 return false;
}

Check more about jQuery autocomplete options from jQuery UI site.

See also relating OTN forum post.

Comments

  • Jari Laine 8 Dec 2015

    Hi Arif,

    This works also with APEX 5.
    https://apex.oracle.com/pls/apex/f?p=39006:11

    Regards,
    Jari

  • Md. Arif Hossain 3 Dec 2015

    This is Work on Apex 5

    if yes please let me know.

    I am trying but it's not work.

  • Koloo Enock 25 Oct 2015

    Jari,

    Thanks i resolve the issue please ignore my previous comment.

    regards

  • Koloo Enock 25 Oct 2015

    Jari,

    iam having issue with this great code approach in Apex 5 - using the same steps as stated including references to the below in js URL section

    #JQUERYUI_DIRECTORY#ui/#MIN_DIRECTORY#jquery.ui.autocomplete#MIN#.js

    #JQUERYUI_DIRECTORY#ui/#MIN_DIRECTORY#jquery.ui.menu#MIN#.js

    everything look right but in the code inspector/chrome iam getting the error "Uncaught TypeError: Cannot read property 'row' of null"

    What do you think might be the issue?

    your help is really appreciated...regards

  • Jari Laine 16 Sep 2015

    Hi Scott,

    Great!

    I propose that you always write on demand process name at upper case everywhere.

    Regards,
    Jari

  • Scott Dortch 15 Sep 2015

    Jari

    Thanks for your continued help. I figured it out. Apparently the name of the on demand process is case sensitive. I had it as 'sku_data' and was calling for 'SKU_DATA'

    Scott

  • Scott Dortch 15 Sep 2015

    Jari

    I think I must be seriously blind :)

    I tried to move your solution to my live application and it did not work. So next I tried to make it work in the test app you worked in and still had no success.

    Your page works perfectly (of course) but mine does not. I feel like I have replicated your page exactly.

    Can you enlighten me on what I missed?

  • Jari Laine 15 Sep 2015

    Hi Scott,

    I did create page 6 to your sample and fixed problems. Biggest issues:

    1. JavaScript error "$ is not defined" raised because jQuery wasn't loaded before function call -> JavaScripts from page header text moved to page JavaScripts section.
    2. Query in on demand process was incorrect
    3. Wrong on demand process name in JavaScript
    4. Wrong item name in JavaScript

    You can compare page 5 and 6 to see all changes.

    Also you could replace jQuery ajax call with APEX build in function apex.server.process.

    Regards,
    jari

  • Scott Dortch 15 Sep 2015

    OK. I have finally got it together.

    Login URL is:

    https://apex.oracle.com/pls/apex/f?p=4550:1:8307264440760:::::

    w: xxx

    u: xxx

    p: xxx

    I have loaded a small sample of data in 2 tables VENDORS and PRODUCTS. You will see the display items I would like to populate from the SEARCH box in the top region. What am I doing wrong?

  • Scott Dortch 14 Sep 2015

    Jari

    Thank you for your continued help. Still having problems. I will try to get a replica uploaded to apex.oracle.com and forward the credentials to you when complete.

    Scott