9 Replies Latest reply on Jan 7, 2014 11:51 AM by Rambo79

    Javascript Autocomplete by Tom Petrus and VC

    Rambo79

      Hi

      I am using the following on my interactive report to provide a google search like autocomplete as the user enters a name, surname, date etc

       

      This works find apart from when searching for a surname such as 'Rees' it does not bring this back but will bring back suggestions like 'Crees'. 'Freestone' etc

       

      Same for forename when searching for 'Ian' it does not bring this back 'Ian' but brings back 'Adrian', 'Brian' etc

       

      This only happens on certain names however searching for 'Paul', 'Steven' 'Brian' works fine

       

      If anyone could shed any light into what's causing this it would be most helpful

       

      function srch2(pItem, pColumn) {   
         $.post('wwv_flow.show', 
                {"p_request"          : 'APXWGT',
                "p_widget_action"     : 'FILTER',
                "p_widget_action_mod" : 'ADD',
                "p_widget_mod"        : 'ACTION',
                "p_widget_name"       : 'worksheet',
                "p_flow_id"           : $v('pFlowId'),
                "p_flow_step_id"      : $v('pFlowStepId'),
                "p_instance"          : $v('pInstance'),
                "x01"                 : $v('apexir_WORKSHEET_ID'),
                "x02"                 : $v('apexir_REPORT_ID'),
                "x03"                 : pColumn,
                "f01"                 : ['COLUMN', pColumn, '=', $v(pItem), null, 'minutes']
               }, 
               function(data){
                  //when the filter has been successfully applied the report has to be refreshed
                  if(data=="true"){
                     gReport.pull();
                  };
                }
               );
         
         //clear the item
         $s(pItem,'');
      };
      
      $(document).ready(function(){
         //hide IR toolbar
         $('#apexir_TOOLBAR').hide();
      
         $('#P4_AC').bind("result", function(){    
            //on selecting a value then fire the search function
            srch2(this, 'INDSNAME');
         });
         
         // ac item P4_AC2
         $('#P4_AC2').bind("result", function(){    
            srch2(this, 'INDFNAME');
         });
      
         // ac item YEARD
         $('#YEARD').bind("result", function(){ 
            srch2(this, 'NEWYEAR');
         });
      
      
       // ac item REG
         $('#REG').bind("result", function(){ 
            srch2(this, 'INDREG');
         });
      
      
       // ac item ENTNO
         $('#ENTNO').bind("result", function(){ 
            srch2(this, 'INDENT');
         });
      });
      
        • 1. Re: Javascript Autocomplete by Tom Petrus and VC
          Tom Petrus

          The code posted has no relation to the problem you encounter as it will only add a filter on an interactive report and binds actions to the "result" event on a couple of items. The "problem" will be in either the javascript code of the autocompletion or the plsql/ajax process retrieving data for the autocomplete list. Can you share that code (or is this a plugin available somewhere) or even better, do you have an example set up at apex.oracle.com ?

          • 2. Re: Javascript Autocomplete by Tom Petrus and VC
            Rambo79

            Hi Tom

             

            The javascript posted in the opening thread is under the javascript section of the page section of page rendering

             

            Could it be thaton the interactive report it mentions "This query returns more than 100,000 rows, please filter your data to ensure complete results."

             

            This is the SQL used under region source

            select
            ROWID,
            ID,
            INDSUB,
            INDDOB,
            INITCAP(INDSNAME) INDSNAME,
            INITCAP(INDFNAME) INDFNAME,
            INDINIT2,
            INDINIT3,
            INDMUM,
            INDMUMMAID,
            INDENT,
            INDREG,
            INDCROSS,
            TO_CHAR(INDDOB, 'YYYY') AS NEWYEAR,
            INITCAP(INDSNAME)||' '||INITCAP(INDFNAME) ||' '|| INDINIT2 ||' '|| INDINIT3 AS FULLNAME,
            DELDOC from   BIRTHDETAILS
            
            
            ORDER BY INDDOB asc
            
            
            
            • 3. Re: Javascript Autocomplete by Tom Petrus and VC
              Tom Petrus

              You'll need to freshen up my memory though - I didn't create the autocomplete item or the js code for it, Vikram did. I do remember helping (you) out with gettint the ir filter applying working. I'm specifically referring to the js/sql used for the autocomplete item or how you configured it. Is there somewhere I can get or see the code of the item plugin (or custom js used for it, is it a js file ?)? Because my guess is that it is somewhere in that code or config something is off.

              • 4. Re: Javascript Autocomplete by Tom Petrus and VC
                Rambo79

                Hi Tom

                 

                Could it be that on the interactive report it mentions "This query returns more than 100,000 rows, please filter your data to ensure complete results."


                There is no plugin its just using the two pieces of code I mentioned in the threads above


                This is the original thread

                https://forums.oracle.com/thread/2412281

                • 5. Re: Javascript Autocomplete by Tom Petrus and VC
                  Tom Petrus

                  But the issue is in the autocomplete item, no? How is this item defined? What item type is it? If it is a standard apex autocomplete item, then what is defined in the query? I mean, I'm really missing a piece here and it is the code that will retrieve the autocomplete suggestions. If it somehow works of the IR data then yes, maybe the abundance of data may be a problem.

                  • 6. Re: Javascript Autocomplete by Tom Petrus and VC
                    Rambo79

                    Hi

                     

                    Its not using the standard apex autocomplete.

                     

                    There are text forms with autocomplete on the page, so for example on the surname field on its list of values the following SQL is used

                     

                    SELECT DISTINCT INITCAP(INDSNAME) FROM BIRTHRECORDS
                    ORDER BY 1
                    

                     

                     

                    On under the element > HTML Form Element Attributes the following is used

                     

                    onkeydown="if($f_Enter(event)){srch($v('P10_AC')); return false;}"
                    

                     

                    This is used in conjunction with the javascript code I posted earlier

                    • 7. Re: Javascript Autocomplete by Tom Petrus and VC
                      Rambo79

                      any ideas with this?

                      • 8. Re: Javascript Autocomplete by Tom Petrus and VC
                        Tom Petrus

                        Rambo, I'm really having trouble deciphering what goes wrong. Can you please set up an example on apex.oracle.com. Ideally resembling your situation as good as possible, but since I think the issue is with the autocomplete item, just having such an item set up in the same way you have on your own instance would help a lot. The missing piece is still the logic where the autocomplete suggestions are generated from.

                        • 9. Re: Javascript Autocomplete by Tom Petrus and VC
                          Rambo79

                          Hi

                           

                          Got this working the problem was under settings and in the search field it was set to 'Contains and Ignore Case' it should be set to 'Exact and ignore case'