Ajax autocomplete for PeopleSoft page field

I had to write a simple page recently where users would enter time spent on volunteer work. One of the fields they would enter would be the name of the organization. Since the organization could be anything there no requirement to build a setup table of valid organizations. However, in order to try to keep consistent, it seemed like a good idea to provide an autocomplete feature.

As the user types in the name of the organization (after the first 2 characters are entered), an ajax call is made in order to get a list of all organizations (up to 50) that exist already in the database that have the same first 2 characters. The user can choose to select one of the suggested values or continue typing whatever they like.

This feature was implemented by using the following:

1. HTML Object

You first need to grab a copy of jquery and the jquery autocomplete libs (see google) and place them on the PIA web server. The HTML object takes a couple bind parameters: 1) Page field name, 2) Ajax URL (a WEBLIB, see below) that returns suggestions for the autocomplete, 3) Ajax URL to post additional suggestions to (i.e., values changed in buffer), 4) Message Catalog message set number, 5) Message Catalog message number.

A little more on some of these. The addition Ajax call noted in #3 above is for those situations were the user may add values that only exist in the buffer. Since the WEBLIB function in #2 only returns database values, there needs to be some way to capture values entered in the buffer so they too can be displayed as suggestions. Items 4 and 5 need to point to a message catalog number that contains the sql statement the WEBLIB in #2 will use to return the list of suggestions. For example:

select distinct org_name from ps_volunteer where org_name like :1 || '%'

The sql statement will receive a single bind parameter, and that parameter will be the first few characters of what was entered in the field on the page.

Here is the HTML object code:

<link rel="stylesheet" type="text/css" href="/common/js/jquery.autocomplete.css" />
<script type="text/javascript" src="/common/js/jquery.js"></script>
<script type="text/javascript"  src="/common/js/jquery.autocomplete.js"></script>
<script type="text/javascript">
$(document).ready(function() {
 $('input[name^="%BIND(:1)"]').autocomplete(
"%BIND(:2)",
  {
   delay:10,
   minChars:2,
   matchSubset:1,
   matchContains:1,
   cacheLength:10,
   autoFill:true,
   extraParams:{m1:%BIND(:4), m2:%BIND(:5)}
  }
 );
    $('input[name^="%BIND(:1)"]').blur( function() { 
  $.post("%BIND(:3)", 
    { s: $(this).val() } );
    } );
});
</script>

2. Add the HTML to the page

Now it's time to add the HTML code above to the page that contains the field you want to add autocomplete to. First, I added an HTML control to the bottom of the page in app designer. Then, in order to make this somewhat reusable I created an application class that I call in page activate peoplecode to populate the HTML field.

Here is the page activate code:

import FR_AJAX:Suggestion;

Local FR_AJAX:Suggestion &ajax = create FR_AJAX:Suggestion();

HR_LINK_WRK.HTMLAREA1.Value = &ajax.GetPageJavaScript("ORG_NAME", 31751, 12);

Here is the app class code:

class Suggestion
   method GetPageJavaScript(&pageFieldName As string, &messageSet As number, &messageSetNbr As number) Returns string;
end-class;

method GetPageJavaScript
   /+ &pageFieldName as String, +/
   /+ &messageSet as Number, +/
   /+ &messageSetNbr as Number +/
   /+ Returns String +/
   Local string &urlGetSuggestions = GenerateScriptContentRelURL(%Portal, %Node, Record.WEBLIB_FRAJAX, Field.ISCRIPT1, "FieldFormula", "IScript_Suggest");
   Local string &urlAddSuggestion = GenerateScriptContentRelURL(%Portal, %Node, Record.WEBLIB_FRAJAX, Field.ISCRIPT1, "FieldFormula", "IScript_AddToSuggestions");
   Local string &ajax = GetHTMLText(HTML.FR_AJAX_SUGGEST, &pageFieldName, &urlGetSuggestions, &urlAddSuggestion, &messageSet, &messageSetNbr);
   Return &ajax;
end-method;

3. WEBLIB code for the Ajax calls

Finally, here is the code that returns the suggestions for the autocomplete and also adds suggestions that aren't in the db to the list of suggestions.

Global array of string &fr_ajax_suggest_in_buffer;

Function IScript_Suggest()
   Local string &query = %Request.GetParameter("q");
   Local string &message_set = %Request.GetParameter("m1");
   Local string &message_nbr = %Request.GetParameter("m2");
   
   If None(&query, &message_set, &message_nbr) Then
      Return;
   End-If;
   
   %Response.SetContentType("text/html");
   
   If &fr_ajax_suggest_in_buffer <> Null Then
      Local number &i;
      For &i = 1 To &fr_ajax_suggest_in_buffer.Len
         Local string &s = &fr_ajax_suggest_in_buffer.Get(&i);
         If Lower(&s) <> Lower(&query) And
               Find(Lower(&query), Lower(&s)) = 1 Then
            %Response.WriteLine(&s);
         End-If;
      End-For;
   End-If;
   
   Local string &sqlText = MsgGetExplainText(Value(&message_set), Value(&message_nbr), "");
   Local SQL &sql = CreateSQL(&sqlText, Lower(&query));
   Local array of string &suggestions = CreateArrayRept("", 0);
   Local string &suggestion;
   While &sql.Fetch(&suggestion)
      &suggestions.Push(&suggestion);
   End-While;
   &sql.Close();
   
   Local number &j;
   For &j = 1 To &suggestions.Len
      If &fr_ajax_suggest_in_buffer = Null Then
         %Response.WriteLine(&suggestions.Get(&j));
      Else
         If &fr_ajax_suggest_in_buffer.Find(&suggestions.Get(&j)) = 0 Then
            %Response.WriteLine(&suggestions.Get(&j));
         End-If;
      End-If;
   End-For;
   
End-Function;

Function IScript_AddToSuggestions()
   Local string &suggestion = %Request.GetParameter("s");
   If All(&suggestion) Then
      If &fr_ajax_suggest_in_buffer = Null Then
         &fr_ajax_suggest_in_buffer = CreateArrayRept("", 0);
         &fr_ajax_suggest_in_buffer.Push(&suggestion);
      Else
         If &fr_ajax_suggest_in_buffer.Find(&suggestion) = 0 Then
            &fr_ajax_suggest_in_buffer.Push(&suggestion);
         End-If;
      End-If;
   End-If;
End-Function;