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.