Spicing Up Your Vanilla With a Custom Look & Feel

Last week at OHUG 2010, I presented a case for customizing the user interface of PeopleSoft applications. Sherry Rogers, HRIS Manager at Cerner Corporation, was generous enough to join me, demonstrating the custom look and feel we developed for Cerner's eBenefits module (screenshots of the demonstration) and detailing the reactions and results. Here's the slide deck:

Improving the usability of PeopleSoft applications is less effort then you might think, and the savings from increased employee productivity, fewer calls to the help desk, and reduced training are often substantial. Improved usability can be especially crucial for outward facing applications like recruiting; the last thing you want is a talented applicant's first impression of your organization to be a clunky and confusing web app. And while it's commonly believed that straying too far from vanilla puts you at risk, making the right technical design choices will dramatically reduce your exposure at upgrade time.

Have you encountered problems with PeopleSoft's usability? What have you done about it, or what are your plans? Let us know. 

Local Sub-Templates for XML Publisher

When building an XML Publisher report, I spend most of my time working with the Word Add On. Since everything is local, it's easy to make a change to the report then test it with the Preview feature. However, if the report includes sub-templates, then there's a small difference in the reference to the sub-template.

Here's what the reference to a sub-template looks like when it's stored on the server:

    <?import:psxmlp://ROSO_SUB_TEMPLATE?>

Here's what the local version looks like:

    <?import: file:///C:/temp/MySubTemplate.rtf?>

Note that the stored reference uses the Sub Template Id, while the local reference just uses the file name.

Generate Excel Spreadsheet from PeopleCode

PeopleBooks documents the built-in function GenerateQueryContentURL. It's a helpful function that lets you create a link to a query.

If you append the query criteria as URL parameters, you can bring the query up without prompting the user. This is handy when you've got a data page that contains your criteria—the link allows you to show more details to the user.

When you combine it with another PeopleTools function, ViewContentURL, you can launch a new window with the results of the query. It's a nice way to provide the user with additional data without derailing what they are currently doing.

It gets really interesting when you go off-PeopleBooks and use GenerateQueryContentURL's undocumented cousin: GenerateQueryContentExcelUrl. It takes the same parameters (and also can have the parameters appended to it).

Put it all together and you can launch an Excel spreadsheet with a single line of PeopleCode:

ViewContentURL(GenerateQueryContentURL(%Portal, %Node, "QUERY_NAME"
   , True, False) | "&BIND1=US001&BIND2=123456789");

Checking Query Security via SQL

Query security always seems to be an issue: "I can't see the query!"

Here are a couple of SQL statements to review the setup. The last one tells you why a user can't see a query.

-- List of records available to query
SELECT * FROM PSQRYACCLSTRECS
WHERE CLASSID = 'ROSO_PERMISSION_LIST_NAME'
ORDER BY RECNAME;

-- List of Query Tree Access Groups
SELECT * FROM PS_SCRTY_ACC_GRP
WHERE CLASSID = 'ROSO_PERMISSION_LIST_NAME';

-- Records without security
SELECT * FROM PSQRYRECORD
WHERE QRYNAME = 'ROSO_QUERY_NAME'
AND RECNAME NOT IN (
   SELECT DISTINCT RECNAME FROM PSQRYACCLSTRECS 
   WHERE CLASSID = 'ROSO_PERMISSION_LIST_NAME');

Rowset Manual Sort

The Rowset Sort method allows you to sort in either ascending or descending values based upon one or more fields. However, occasionally there is a need to sort in a non-linear way. For example, if a field includes the values "First", "Second", "Third", and "Fourth", the sort order is obvious to us, but not to the Sort method.

One route to do this type of sorting is to add an additional field. Assign that field values that will sort correctly. Each row with the value "First" gets a 1, each row with the value "Second" gets a 2... Then call the sort method and pass the new field in.

Why not build your own custom sort method? We can do so by leveraging the fact that PeopleTools drops rows that are not visible to the bottom of the rowset. It's a trick that generally trips everyone up at least once. It's also why when you are hiding rows that you need to go from the bottom back to the top.

Here's some psuedo-code that shows how you might build a custom sort method:

method Sort(&data as rowset, &order as array of string, &recordName as string, &fieldName as string)

    /* start by making all rows not visible */
    &data.HideAllRows();

    /* loop through data, looking for ordering values */
    local integer &i, &j;
    local field &f;

    for &i = 1 to &order.len
        local string &orderValue = &order[&i];
        
        for &j = 1 to &data.ActiveRowCount
        
            /* does this row have the sort value? */
            &f = &data(&j).GetRecord(@("Record." | &recordName)).GetField(@("Field." | &fieldName));
            if &f.Value = &orderValue Then
                &data(&j).Visible = True;
            end-if;
        end-for;
    end-for;

    /* in case any values were missing, they'll still be hidden */
    &data.ShowAllRows();
    
End-method;

You might assume from the use of the Visible property that this only works for buffer rowsets. However, it turns out that PeopleTools drops hidden rows to the bottom even on stand-alone rowsets. So this works for those, too.

Meta-SQL %InsertSelectWithLongs

While %InsertSelect is a great tool, it's always had one drawback: It leaves out long character fields. If the table you're inserting into has long fields that need to be populated, you need to find another solution.

Turns out there is another solution. It's not clear to me when it was introduced, but %InsertSelect has a partner in crime: %InsertSelectWithLongs. It does exactly what you think, it includes longs in the field lists.

It doesn't show up in the PeopleBooks for 8.48 or 8.49, though it does make an appearance in 8.50. I found it while googling. The folks over at Salibury University (the fightin' Sea Gulls, as it turns out) posted a PeopleTools 8.49 PeopleBooks Issues and Corrections doc which states that the handy meta-sql had been left out. I verified it works on PeopleTools 8.48.

Set Processing vs Complexity

There was a time when I was a fervent member of the Church of Set-Based Processing. I preached %InsertSelect and TAO tables, and I would brook no dissent. At the time I was writing app engines to bring data into Projects, and it's probably a fair bet to say that PROJ_RESOURCE is one of the largest tables in the Financials database.

On a recent project I had a requirement to allocate available quantity against incoming orders. I dutifully built an app engine that handled most of the work in a set, only dropping to a row-by-row loop at the end. It took a little debugging, I got turned around once or twice, but in the end it was fast and—if you don't mind me saying—somewhat clever.

But here's the thing: while the business analyst was congratulating me on how fast the process ran, he mentioned some of the rules he'd like to add in the future. (I know, new requirements. Who would have guessed?) I had architected for some flexibility, but had no room for the complexity he wanted to add. I could twist the clever knife a little deeper, but then woe be the developer that had to debug or add functionality down the road.

I wondered then if another tool might have served me better. Rather than temp tables and recursive DoSelect's, what if I had built logic with app classes? It's much clearer to write rules in PeopleCode than in recursive app engine steps with state records. And by their nature app classes are easier to extend.

When a similar requirement came up later in the project I did a little testing. I wrote some test code that created a set of objects in a PeopleCode event. They were very simple, bean-like objects that held keys and a few additional fields. I created 5,000. Then 10,000. Then 50,000. It took only seconds. The tests to update the table behind the objects performed similarly.

Writing business rules in PeopleCode is an order of magnitude easier. You can encapsulate complexity in classes and methods in a way that app engine sections just cannot approach. The process is easier to maintain, easier to test, and much, much easier to extend.

Now let's be clear, I'm not saying I'm leaving the Church of Set-Based Processing. (I still resolved my run control criteria with an InsertSelect into a temp table in the first step, then worked from there.) But I did learn that implementing complex business rule need not be complex. Sometimes PeopleCode and app classes are the right tool for the job.

PeopleTools 8.50 PeopleBooks

Just noticed that Oracle published the PeopleBooks for PeopleTools 8.50.

Screen Capture Software: Greenshot

Whether we like it our not, documentation is part of the job. And good documentation is worth it's weight in gold.

When I'm putting together guides for end users, I find screen shots indispensable. Of course, Snagit is the king. But half the time I'm on a client laptop and don't have Snagit.

A little google search turned up Greenshot, a nice open source option. It's a little bare bones (it doesn't have that cool sawtooth edge), but it's got pretty much everything you need.

Using Maps in PeopleCode

Maps, also known as hashmaps, are key-value pairs that let you store and retrieve data using a key. I wrote PeopleCode for years without using one, but after jumping back and forth between PeopleCode and Java, I can't write any code without maps.

What's great about maps is their simplicity. It's not that they do anything fancy, they just make writing code easier and make that code easier to understand. Wikipedia provides a good example of a map—a dictionary. The words are the keys and the definitions are the values.

One example I've used maps for is caching. If you've got a database lookup that occurs for each row in consideration, but there are only a couple of distinct lookup values, cache those values in a map instead of querying the database each time. For each row, check to see if the lookup is in the map: if it is, return it; if it is not, look it up from the database then store it in the map and return it.

Maps start to get more powerful when you realize you can store anything in one. Another example I used a map for is a totals grid. I had a list of items (a sales order or a purchase order) and wanted to display the total quantity for each item. The keys to my map were a concatenation of the BU and Item Id. The value returned by the map was the row representing that item in the totals grid.

Without the map I would have done a loop through the totals rowset each time, looking for the row in question. With the map, it was one line of code.

Can't find the Map object in the PeopleTools API? That's because it's not a delivered class. Instead, it's one that was added by application developers and must be imported to be used. In the HR database, look for application package HRTR_UTILITIES. The classes there refer to maps as hashtables. Check it out, it's good code with good comments.

Here's the public class declaration for StringHashtable, which uses strings for both the keys and values:

 class StringHashtable
   method StringHashtable();
   method Put(&key As string, &value As string);
   method Get(&key As string) Returns string;
   method GetKeys() Returns array of string;
   method IsKey(&key As string) Returns boolean;
   method GetValues() Returns array of string;
end-class;

Accessing the Run Conrol from App Engine PeopleCode

With the continued evolution of PeopleCode, including features like the XML Publisher, I find myself building more and more Application Engines that are primarily PeopleCode based. In many cases, in fact, I’ve got only two steps: a SQL action to populate the state record with the run control and a PeopleCode action to do the work.

Mostly it was habit. The SelectInit has been my friend for a long time. But I just realized I can bring the run control record directly into the PeopleCode step without the need of a SQL step to populate it first.

The state record is always available to a PeopleCode event with a simple GetRecord. The app engine always populates the state record with the process instance—you get that for free. Armed with that, a SQLExec to the process request table gets you the Run Control Id. Since run control tables are keyed by Run Conrol Id and User Id, you’re in business.

Local string &id;
SQLExec("SELECT RUNCNTLID FROM PSPRCSRQST WHERE PRCSINSTANCE = :1", GetRecord().PROCESS_INSTANCE.Value, &id);
Local Record &runControl = CreateRecord(Record.G9_JOBC_DESC_RC);
&runControl.OPRID.Value = %UserId;
&runControl.RUN_CNTL_ID.Value = &id;
&runControl.SelectByKey();

Of course, this counts on the app engine running through the process scheduler. Without that there’s no row in the process request table. If you like to run app engines directly from the App Designer this won’t work. (If you're debugging, just hard code your Run Control Id, then you're back in business.)

Rowset Fill Method and the JOIN Keyword

The Rowset method Fill is used to populate a stand-alone rowset from the database. It take a where clause as a parameter. The underlying record is used as a select record.

The Fill method flushes the rowset then reads records from the database into successive rows. The records are read from the database tables corresponding to the primary database record of the scroll into that record. The records are selected by the optional wherestring SQL clause, in which the optional bindvalues are substituted, using the usual bind placeholders (:n).

Often times the criteria for selection comes from a record other than the one your selecting from. Up until this point I’ve been writing WHERE EXISTS clauses to check that type of criteria. However, it turns out that you can have more than one record in the statement by use of the JOIN keyword.

Local Record &job = CreateRecord(Record.JOB);

&job.Fill("JOIN PS_JOBCODE_TBL JC ON FILL.SETID = JC.SETID 
AND FILL.JOBCODE = JC.JOBCODE WHERE JC.GRADE = :1", &paramGrade);

Now you’ve got SQL that’s easier to write and easier to maintain than a WHERE EXISTS clause.

Select All / Clear All via JavaScript

Good UI dictates that if you have a grid that allows users to check one or more rows, then you should also have Select All / Clear All buttons available to the user. Most of the delivered grids in PeopleSoft with the Multiple Row selection option include these. The code is straight forward, and you can even copy an existing example.

However, as with all PeopleCode, your choice is to defer it to the next server trip or force a server trip. Since it's an action that needs immediate feedback, you can't defer it. Thus you're left with a page flicker for a somewhat innocuous task.

In the never ending hunt to make usuable software, I decided to step outside PeopleTools and put together JavaScript to accomplish the task. By pushing the work to the browser, it happens immediately and without a server trip. While I impressed myself, the users did not notice—but I had what I wanted, software that doesn't get in the users' way.

Here's the JavaScript that does the work:

var ROSO_CHECKBOX_MGR = {
   grid: "",
   title: "",

   addButton: function(f, check) {
      $A($$('a[id^="'+f+'"]')).each(function(pb) {
         pb.href='javascript:ROSO_CHECKBOX_MGR.check('+check+')';
      });
   },

   check: function(v) {
      $(ROSO_CHECKBOX_MGR.grid).select('[title="' 
            + ROSO_CHECKBOX_MGR.title + '"]').each(function(cb) {
         if (cb.checked != v) {cb.click();} });
   }
};

The code above is a JavaScript library called ROSO_CHECKBOX_MGR_JS. The first function, addButton, attaches listeners to the Select All and Clear All buttons. The second function, check, is invoked by the listener and loops through the rows in the grid to select or clear the buttons.

Here's the JavaScript that invokes the library:

<script type='text/javascript' 
      src='%Javascript(PROTOTYPE_V1601_JS)'> </script>
<script type='text/javascript' 
      src='%Javascript(ROSO_CHECKBOX_MGR_JS)'> </script>
<script type='text/javascript'>
   U_CHECKBOX_MGR.grid = 'GRID_REC_NAME$scroll$0';
   U_CHECKBOX_MGR.title = '%bind(:1)';
   U_CHECKBOX_MGR.addButton('GRID_WRK_SELECT_ALL_PB', true);
   U_CHECKBOX_MGR.addButton('GRID_WRK_CLEAR_ALL_PB', false);
</script>

The first thing to notice is the import of the trusty Prototype library. The next line imports the library from above. Within the script tag, there's not much left to do. It sets the id of the grid (as generated from the Page Field name and the component processor), then requires the Hover Text from the checkbox (it gets passed in from the PeopleCode). The Hover Text, which is the title attribute in the HTML, is used to identify all the check boxes. Finally, the addButton function is called once for each button, Select All and Clear All.

The real payoff in this case was the grid I implemented this feature on. It could commonly contain more than 1,000 rows. As such, it wasn't a half-second annoying screen flicker that I had avoided, but a 3-or-more second server trip.

Pre-process XML Publisher Reports with XSLT

While I'm a fan of the XML Publisher, I find that anytime I try to get too complex with my RTF template I get myself into trouble. The User Guide provides some examples, but inevitably I want to do something a little different and it just won't come together. It might be that I've got the syntax wrong, it might be that there's a bug in the tool, or it might simply be that it doesn't do that.

Oracle has a good forum for support as well as a pretty good blog . Both are good tools when you are stuck. And since it's based upon open source tools, you've got Google as a reference, too.

However, for my most recent XMLP report, I decided to do some pre-processing of the data with XSLT, so that the xml data the report was built upon looked a lot like the report itself. The challenge of the report was that it required four columns per page, then 1-to-n number of rows for each column. Again, probably doable in the XML Publisher template, but at best it would be fragile.

Instead of trying to do it all within the plug-in tool, I did the heavy lifting in PeopleCode. First I created and populated a rowset with my data; then I used the PeopleTools application package PSXP_XMLGEN:RowSetDS which turns a rowset into an xml string with the method getXMLData; finally I called built-in function TransformEx to run the xml string through an XSL template.

With the transformed xml, creating the XML Publisher report was quick and easy. Now I'm using the XML Publisher to do tasks like formatting and page breaks—things it does very well. It also means the client will be able to make changes to the report template without having to address any complex logic.

Status Monitor Pop Up

Since the days of client-server and the desktop installed application, PeopleSoft has struggled with how to treat a pop up window. In the old days, the trusty DoModal let you ask the user for a small amount of info while keeping the main page clear of the details. Running in a browser, however, the DoModal function doesn't provide a good user experience.

There are a few ways to address this. You can launch new browser windows without the PeopleSoft portal navigation on the left. That provides a slimmer pop up. You can even get fancy with your JavaScript and remove the menu bar and tool bar. However, you've really just done a transfer to a new component. You're no longer part of the original component.

Then along comes the Status Monitor in the Approval Workflow Engine. A brief over view: The status monitor is an html area on the page that shows you the approval steps for the transaction. It's a nice, big and bright visual representation of the process. Additionally, it lets you add approvers to the transaction. If you click on the plus icon, up pops a small browser window asking you for a new approver. When you enter the approver and click OK, the window goes away and the Status Monitor back on the original page gets updated with the new approver.

How does it do it? How did it solve the pop up problem? I'm glad you asked, because I think the answer is a pretty slick solution.

When the plus button gets clicked, JavaScript opens up a new pop up window (there's no server trip here for the main component). The url of the pop up window is an iScript. The iScript returns html to create a small form in the pop up window.

So far, so good, but here's where it gets interesting. When the OK button on the pop up is clicked, two things happen. The first is a submit to another iScript containing the info the user filled out. That iScript post loads the submitted data into a global variable. The second thing that happens is in the response from the iScript. The response includes JavaScript that calls back to the main page then closes the pop up.

The call back to the main page is a function that sets a field then submits the main page. The field that was updated (a work field on the Status Monitor subpage) has FieldChange PeopleCode on it. When that FieldChange runs, it checks the global variable and adds the new approver. When the main page comes back, it has the new approver in the Status Monitor.

When I saw the feature I was impressed. When I dug into the code I was even more impressed. I have to tip my cap to the folks behind this one. They took tools we're all familiar with and put them together to build something new.