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

Wednesday, July 30, 2008

PeopleTools Grid Controls and Subpages

I had the situation today where I needed to change the column header in a grid on a subpage via PeopleCode. I tried adding the following to the PageActivate PeopleCode on the subpage:

GetGrid(Page.PS_Subpage, "GridName").ColumnName.Label = "MyNewColumnName";

I kept receiving an error that the grid could not be found on the page. I thought that was interesting, double-checked my code to verify that I didn't have any typos and tried again. Same error. It then occurred to me that maybe the processor doesn't interpret subpages as a different page object. I changed my code to reference the page that was hosting the subpage...

GetGrid(Page.PS_HostPage, "GridName(from subpage)").ColumnName.Label = "MyNewColumnName";

... and my code started working.

We still have the 8.46 toolset, so this might not be an issue with newer versions of PeopleTools.

Wednesday, July 2, 2008

PeopleTools: How-To rename table names on a page via SQL

I have frequently run into the situation when cloning a page where you want to rename a table on a page, but want to keep all field properties the same (ie, custom label, fieldname, etc.). In PeopleTools, when you change the record name on a field, all of the properties get wiped out, so you end up doing a lot of unnecessary cutting and pasting to reset all of the properties. A better way of doing this is to just update the page record directly via SQL:

update pspnlfield set recname = :newrec where pnlname = :pagename and recname = oldrec;

Thursday, May 15, 2008

CommunityOne 2008 Recap

This was one of the best conferences I have ever attended. I am very interested in open-source software (for a myriad of reasons) and this conference was almost completely about open-source. The keynote from Ian Murdock covered both the business and philosophical aspects of open-source software. I found the keynote very enlightening and enjoyable. If you would like to watch the videos from the opening keynote, they are available here.

At best, I consider myself a Netbeans n00b, so all of the Netbeans sessions were really cool and very informative. I haven't seen all of the bells and whistles of Netbeans. We went to several of the Netbeans sessions.

I also attended sessions on OpenESB/BPEL,Ubuntu and Web Services with Glassfish. All very cool to see what is possible with all of this open source software that is available.

I was telling John Wa (other author on this blog) that I got the same feeling of excitement from this conference I used to get while I was in college, going to big shows like Comdex. You see all of the possibilities and it inspires and excites you, making you want to get home and try some of it out. I well definitely make every attempt to attend next year.

Best Practice Center: PeopleSoft and Oracle Fusion Middleware

Here is a link to a site that Oracle recently brought up regarding best practices for integrating PeopleSoft and Oracle Fusion Middleware:

Saving images out of an ODF (OpenOffice) Document

We recently had the need to extract some images out of a functional specification document that we received. In OpenOffice, when you right-click on an image, there is no option to "Save Image to File". In order to pull the images of the document, you need to save the document in the ODF (native OpenOffice) format. Open your favorite decompression tool (7-zip is our fav) and open the ODF file. When you open the file in your decompression tool, you will notice several folders, one of which is "Images". This is the folder that stores all of your images from your document.

Friday, May 2, 2008

Pre-JavaOne 2008 Conference: CommunityOne

Gary, John and I are heading up to San Francisco on Monday for the CommunityOne conference. I plan on blogging more about it Tuesday when we get back to the office. If you are planning on attending, drop a comment on this post.

Friday, April 11, 2008

PeopleTools 8.46 and Microsoft Windows Vista SP1

As luck would have it, my Windows XP drive took a dive earlier this week. Rather than wasting a lot of time figuring out what the problem is, I thought it would be better to put my Windows Vista drive back into my laptop and keep working. I built a disk image of Windows Vista Enterprise a couple of months ago, but switched back to Windows XP around the end of January (for no reason... really...). With all of the recent hoopla about Service Pack 1 for Windows Vista, I thought it was a good time to give it another shot. I loaded the drive back into the laptop, fired it up, got all of the Windows Updates (20+), all of my open-source software was updated (Notepad++, Firefox, WinMerge, Tortoise SVN, Filezilla all had multiple releases since the end of January) and then I did Service Pack 1. It loaded without error, though the suggested load time message from Microsoft of an hour plus was more like 2 hours plus.

We are still running PeopleTools 8.46 here and yes, I know 8.46 is not officially supported by Oracle on Windows Vista. That being said, I haven't run into any problems running 8.46 on Vista. There are the occasional crashes, which aren't any more or less frequent than they are in Windows XP. One thing I have noticed though since Vista SP1 is that the PeopleSoft Query window in two-tier (I am old-school and like the two-tier query interface) keeps crashing with a "Out Of Memory" error. I have checked each time and I still have about 900 megs of physical memory left, but the error is still showing up.

Not that this is a huge issue, just wanted to document it somewhere. I might be the only person on the planet trying to write a query on PeopleTools 8.46 in two-tier mode on Windows Vista SP1. If there is someone else out there with the same issue, you are not alone... ;-)

Thursday, February 28, 2008

Jonathan Coulton

This is quasi-technical.

I am sure I am late to the party on this (as usual) but Yahoo ran a story on Monday about Jonathan Coulton that caught my eye. Interesting story about him that I won't rehash here. I went to his site and listened to a couple of his tunes. Very entertaining. If you are a developer, check out the "Code Monkey" tune.

Oracle Mix Idea

Good morning and Happy New Year (I can say that in this late February post as I am the first person from our team to post in 2008). Last week I posted an idea on Oracle Mix (a very cool new Oracle site brought to you by the geniuses at Oracle Appslab) regarding converting Application Designer to a Java-based tool. App Designer is currently a Win32 app, requiring MS Windows. I would love to tell you I had great, altruistic means for the post, but truth be told, I just want to run App Designer in Linux. And I am sure that I am not the only one. I am sure there are several kindred spirits out there running Mac and Linux boxes that have at one time or another also wished App Designer was cross-platform.

IMHO, making App Designer platform independent is a good business move. Having an IDE where the customer can decide what platform they want to run it on is a good thing. A company that is not necessarily a Windows shop wouldn't have to sink the extra cash on a virtualization solution or purchase unnecessary extra machines.

Anyway, my $02. If you want to vote on my Mix idea, the url is: