Thursday, November 23, 2006

Parsing the HTML Field name into Record and Field Names

There are a lot of ways we can improve the PeopleSoft user interface using Ajax and JavaScript libraries. Using libraries like jQuery, we can add Google Suggests style prompts, spreadsheet grid navigation, or highlighting the active text field. Before we add these features to data entry fields, we usually need to discover two things about the target page:

  1. The type of data entry field and
  2. The name of the field.

HTML provides a few types of data entry fields. PeopleSoft applications extend this model by adding prompt fields and grid fields. Both of these are standard html input text fields. What differentiates a PeopleSoft grid or prompt text field from other fields is the prompt field's name and the grid field's parent. Grid text fields have a parent table with a special name. Prompt fields have the string $prompt$ in the name attribute.

There are several pieces of information stored in the PeopleTools meta-data that would be valuable to know when using Ajax. For example, when using Ajax to implement Google suggests style prompts, you need to know the prompt table, etc. Since edit field names are a concatenation of the record name and field name delimited by an underscore ('_'), it should be possible to parse a field name into the field's source record and field. Unfortunately, record and field names also contain underscores. Therefore, it is impossible to know where a record name ends and a field name begins. Nevertheless, this information is required to determine the field's prompt table.

I've heard that Oracle's PeopleSoft team is planning to enhance the system generated HTML in ways that will facilitate Ajax and other UI enhancements. Hopefully providing us with the record and field name is on the list. Until then, here is an SQL statement that will provide you with the record and field name of a text field. You can call this SQL statement from your Ajax IScript.

SELECT RECNAME
, FIELDNAME
FROM PSRECFIELDDB
WHERE RECNAME '_' FIELDNAME = :1

There is some risk in this method. It is possible that a record name and field name overlap in such a way that the above SQL returns 2 rows. I ran this SQL in the PeopleSoft Financials database and found 1 combination that returned 2 rows. Since the 2 tables were not tables I had noticed before, I don't see too much risk involved in using this method.

I also noticed one other problem with using this method on an Oracle database. Because of the concatenation in the WHERE clause the database ignores the record and field indexes and performs a full table scan. There are several ways to work around this problem. One solution would be to create a function based index. Another alternative would be to create a materialized view that concatenates the fields in the view's select statement.

16 comments:

Chili Joe said...

Jim,

Could you clarify what you meant by a grid field?

There are still other cases where this won't work:

1) Developers could optionally change the page field name.

2) This won't work with a dynamic prompt table (such as %EDITTABLE). Javascript will need to know the value of a field in the DERIVED work record, and most of the time this is hidden from the page (not rendered in HTML). Only the component processor knows what this value is.

3) This won't work with correlated prompts. With correlated prompts, javascript also needs to know the values to match for the higher-level keys of the prompt table. This is very tricky to figure out just by looking at the DOM. Again, it might also be possible that these fields are hidden.

Regards,
Joe

Jim Marion said...

Joe,

What do I mean by "grid field"? As you know, a grid is a table-like scroll placed on a page. A grid field is a data entry field inside that grid. It is very important to differentiate between grid fields and other fields when implementing a solution like Chris Heller's spreadsheet navigation.

You are correct in saying there are some additional issues with implementing Google Suggests style prompts on a global basis without some page specific code. Using dynamic views for prompt tables also complicates this issue. I share your frustration. It would be great if PeopleSoft provided us with access to the component buffer from an IScript. This would go a long way toward enabling Ajax enhancements.

Being able to change the page field name is actually a bonus. If we are willing to change the page field name, then we ought to be willing to add additional JavaScript in an HTML Area further enhance the page.

Implementing Google Suggests style prompts is just one example of how this name parsing solution might be applied. As you point out, there are other issues to resolve. Being able to parse the name could be used as the basis for several developer add-ins. I'll leave other ideas to the creativity of other developers.

Thanks for clarifying this!

Jim

Derek said...

Hmm, what prompted you to write this post?! :) Thanks and I understand the shortcomings that Joe points out. I think that in a lot of cases they are exceptions to the rule which can be checked for in the AJAX code. At least that's what I plan on doing.

Jim Marion said...

Derek,

You know what "prompted" this post ;). For those that don't, but are curious, it was this Monkeygrease discussion thread.

I was also thinking the Ajax code could account for many of Joe's concerns. Specifically, if a page contains one of Joe's cases, then the JavaScript could ignore that prompt. Although, the correlated prompts issue is an important issue since many of the prompts do use keys from other values in the buffer (like effdt, etc). To make this work with hidden fields, you would need to click the box to make hidden fields available to JavaScript and send those values back to the IScript. Basically, you would have to post a replica of the component buffer to the IScript.

It is a good thing that dynamic prompts aren't the only place this technique can be used.

Jim

Derek said...

Hi Jim,

As you've been playing around with AJAX & PeopleSoft, have you found what the best way is to determine what the current Component and Page is for the page you are visiting?

From what I see, it looks like the information is in the "head" tag as a comment and also in some javascript as part of a variable called "strCurrentUrl." I don't know if either of these values are "foolproof" meaning that they would always be available or not. Just thought I would see if you've thought about it at all.

Thanks
Derek

Jim Marion said...

Derek,

Using regex to parse the strCurrentUrl is the only way I know to determine the page and component. I'm pretty sure this is foolproof. I didn't double-check, but I'm pretty sure strCurrentUrl is the value that is used to put the URL on the clipboard from the copy link in the page bar. I've seen Chris Heller use this in a bookmarklet to determine page permissions, etc. Chris demonstrated this at OpenWorld. I encourage you to download his presentation if you missed it.

Jim

Derek said...

Hi Jim,
I'm messing around with grids & ajax stuff. You mention that the grid text fields have a parent table with a special name. Where can I find out where that is? I can't seem to figure that out.

Thanks!
Derek

Jim Marion said...

Derek,

You caught me!! I was looking back through my code and noticed that it isn't the table's name, it is the table's className that will tell you if a table is a "grid" table. Here is the regular expression I use with jQuery to find tables that are used for grids: /PSLEVEL\dGRID.+/.

I've been meaning to post more about this, but haven't had time.

Derek said...

Happy Holidays Jim and thanks for all the valuable input you have on your blog.

Let me bounce this one of you as an extension of the previous grid comments. What I am trying to do is get at the grid column header's table cell to place an element. For example, if the field I want to focus on is Currency Code in the grid, I'd like to be able to place an element (image) next to the column header's label, which would be "Currency" in this case. Any thoughts?

Jim Marion said...

Derek,

A couple of months ago I wrote a script that creates an object I call Grid. This Grid object adds methods to an HTML table that represents a PeopleSoft Grid. I've been meaning to post this as part of an article about adding spreadsheet navigation to PeopleSoft grids, but haven't had time.

As you probably noticed, the header cells are TH whereas the detail rows are TD. Here is some code to find a Grid and iterate through the cells in the header row. Please excuse the formatting. Blogger doesn't allow us to use very many types of HTML tags in comment posts.

/*
 * Code that runs after DOM is available
 */
$(document).ready(function(){
  
  /*
   * find tables
   */
  $("table").each(function(idx) {
    var className = this.className;
    if(className.length > 0) {
      /*
       * PeopleSoft grids have a classname like PSLEVEL1GRIDWBO.
       */
      if(className.match(/PSLEVEL\dGRID.+/)) {
        /* found a grid */
        $(this).find("th").each(function(i) {
          /* do something with element th at index i */
        }
      }
    }
  }
}


How are you going to tell if the cell is a currency cell? One idea is to add rules to your monkeygrease.xml file for each component/page that will include this functionality. Then, within that rule, include a javascript file that has an array of column images, 1 image for each column. The array would be indexed the same as the jQuery each function above.

Let me know how it turns out!

Jim

Jim Marion said...

By the way...

To add an element to the end of TH contents using jQuery I think you would do something like:

$(this).find("th").each(function(i) {
  /* $(this) is now the "th" element */
  $(this).append("<img src=.../>"
}

Unknown said...

Jim,

Is it possible to call an iScript function from other functions? I mean, can we use the iScript functions as normal functions and call them anywhere required in our application?

Thanks,
Madan

Jim Marion said...

You bet. Just declare and call it like any other FUNCLIB function.

Just curious, and for the good of the community, what do you have in mind? Can you share your scenario?

Unknown said...

Sorry for the delay. Thanks and yes it worked. I am able to access the iscript function as normal function after I declare it.

My scenario is that I am using a cref (PeopleSoft URL based) that accesses the iscript function. Based on some condition, I need to trigger the same function again without changing the URL. This really worked for me. Thanks.

s3f4 said...

thanks good documant

Michael said...

Has anyone written a jquery snippet on a PSGRID to generate an id and append it for all of the display-only editboxes on a grid with the row identifiers based upon the column header information? And

yes, this would be for an ancient codeline (PT8.19 on PS8.0) which doesn't provide any of the nice things everyone else seems to be enjoying. I *do* have jquery, etc working fine, just not advanced enough yet to know how to grab the column headers into an array and build it into the rows.

Michael.