0 Replies Latest reply: Jan 9, 2013 4:53 PM by Jon Finke RSS

    Autocomplete to Combobox, where is the LOV...

    Jon Finke
      Apologies for the title of the post...

      I am hoping I am just missing some minor item, and a push in the right direction will get me working again.

      A few years back, most likely under Apex 4.0, I started using Jquery - Autocomplete for looking up employee names, hostnames, etc. This used some Javascript that someone had posted (don't recall who), and it worked pretty well. I would add a stanza like this to the page def (to be run after the page loads). The X02 passed some extra info to the on demand procedure to help with the search.
       $("#P28_REQUESTOR").autocomplete('APEX', {
                      apexProcess: 'PERSON_SEARCH_ACTIVE',
                      width: 600, multiple: false,
                      x02: 'Hostmaster:Owner Ok',
                      minChars: 3, selectFirst: false,
                      formatItem: function(row, i, max, term) {
                              if  ( row[1] > 0 ) 
                                  return row[0]+" "+row[2]+" "+row[3];
                               return row[0]},
                      max: 100,
                      cacheLength: 1
                  });
      
          
         $("#P28_REQUESTOR").result(function(event, data, formatted) {
               $("#P28_REQUESTOR_ID").val(data[1]); });
      The Person Seach Active on demand procedure was something like: (Note - some details have been left out to give a better example - in the end, it doesn't matter at all)
      declare
         target_name  varchar2(255);
         scope_id    varchar2(16);
      BEGIN
         OWA_UTIL.mime_header ('text/html', FALSE);
         HTP.p ('Cache-Control: no-cache');
         HTP.p ('Pragma: no-cache');
         OWA_UTIL.http_header_close;
      
         target_name :=  wwv_flow.g_x01;
         scope_id := wwv_flow.g_x02;
         
         Simon.Apex_People_Select.Trace('On Demand called for ' ||Target_Name || '/' || scope_id);
         FOR c IN (SELECT search_name,display_name,result_id
                     from table
                   (Simon.HM_Network_Maint.search_network(scope_id, Target_Name)))    
         LOOP
               HTP.p(c.search_name || 
                     '|' || c.result_id || 
                     '|' || c.display_name );
         END LOOP;
      
      END;
      The g_x01 value is what the user is typing into the text box - once they hit three characters, it gets sent to Oracle for processing via the on demand procedure. The g_x02 (and 03, 04,..) are additional parameters - perhaps from a parent LOV. General concept with the returned data is the first element is the text to be searched, the second is the actual value (typically an integer - primary key), and the third value is what ends up in the text box once a value is selected (the searching value often has additional identifying info.)

      This approach worked pretty well, although it did not play well with cascading LOVs. But once I upgraded to Apex 4.2, I started hitting other issues with jQuery version differences, and some odd failures. To make matters worse, the original developer of the Javascript module, abandoned it - was not doing additional work with it.

      Combobox
      I did NOT want to re-invent the wheel, and it seemed to be a much better approach to move into PlugIn world - ideally using a plugin that someone ELSE wrote and was maintaining. I looked at the LOV Friendly Autocomplete plug in - and even installed it a few places - and discovered that it did not play well with my earlier autocomplete code.

      My concern with using the LOV Friendly Autocomplete - is that I didn't know how to provide any of the "Target Name" search info to the LOV (since that is what is being typed by the user at the time).

      I also started looking at the Combobox plug in - which seemed even closer with Lazy Loading=Yes (I also liked the ability to add new items on the fly) - but I was not sure how to provide the "Target Name" info the the Pipelined function that generates the list of values.

      I want to get rid of all of my old Autocomplete javascript and move to one (or more) plugins - (Which is why the details above don't matter, just trying to provide some background)

      Is this a good direction (Combobox or LOVFA)? How/Where do I connect my search (lov generation)?

      Edited by: Jon Finke on Jan 9, 2013 5:52 PM