Monday, September 22, 2008

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() {
extraParams:{m1:%BIND(:4), m2:%BIND(:5)}
$('input[name^="%BIND(:1)"]').blur( function() {
{ s: $(this).val() } );
} );

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;

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;

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


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

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)

Local number &j;
For &j = 1 To &suggestions.Len
If &fr_ajax_suggest_in_buffer = Null Then
If &fr_ajax_suggest_in_buffer.Find(&suggestions.Get(&j)) = 0 Then


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);
If &fr_ajax_suggest_in_buffer.Find(&suggestion) = 0 Then


Web Development Services said...

PECS is Microsoft & ISO 9001 certified company that provide new media
solutions all across the globe having sales office in UK and India. We
provide solutions related Web Design & Development, SEO and Bespoke
Software for all business domains.

Graham said...

Nice post. A good insight into how the mechanics of AJAX might work with PeopleTools.

I was at OOW last week and saw a demo of Tools 8.50. This had an autocomplete feature built in as a standard (along with significant amount of other AJAX embedded features). See

yadagiri said...

It was really a nice post which made me more curious to know about the Jquery and ajax in peoplesoft.
Could you plz let me know the Jquery.js and Jquery-Autocomplete.js files which you have used for implementing this functionaly in your application and what customization you have made for your requirement.

Nadaraj @ Venkadesh said...

It was really Nice Post. I have tried the same way you mentioned. It is not working for me. I would like to know what change you have done in Jquery.js and Jquery-Autocomplete.js files. can you please send me through email:

Melban said...

Awesome I can't wait to try this. My users will love it. I might hate myself afterwards though.

Anand Mishra said...

I am a student who is learning HTML and Web designing. I get a lot of learning from your blog. Thanks

gael said...

First thanks for sharing this kind of information !
My user wiil love ti to !
I will implement your solution to make a test but, i have more question.
You put the Jquery.js and Jquery-Autocomplete.js on the repertory PIA ... js... ?

thanks for your help.

Shilpa said...

I have followed your steps exactly, and i am not able to get it working! :(

Stanny said...

I had a question how are you passing the query output to the bind variables used on the html.


Anand Kumar Singh said...

Hello John,
I need your help.
I tried to follow the same steps told by you but in my page I am not able to get it working.
It is also not giving any error.

Thanks to help me out.

Ankur said...

Hi John

Thanks you so much !!

1000 PeopleSoft Interview Questions and Answers

senthil said...

Hi John

Thanks you so much , Just followed your steps and Its working

karthik said...

Hi ,

I tried as per your sequence of your steps. However I am not able to get this thing working.
Also, I donot get an error.

Should I bounce the App and Web server. I have added code as you have mentioned , gave permission to the weblibrary etc.

Pls do let me know what I might be missing

karthik said...

Got It working .. Thank You

avadhraj said...

It was very good job for hire php programmer and contract basis from the software outsource development company in India.