Extracting Record and Field Metadata

As part of a data integration effort, I created a program - an Application Engine - to extract useful metadata for PeopleSoft record and field definitions to an Excel spreadsheet. For good measure, it also adds some summary information about the actual data in each of the database fields.

To start I needed to dig into the PeopleTools tables where the metadata is stored. A couple of excellent references for this task are The PeopleSoft Customizer and David Kurtz's excellent book PeopleSoft for the Oracle DBA.

Here are the metadata tables I used.

• PSRECDEFN - The record header table
• PSRECFIELDALL - All fields in the record, including subrecord fields
• PSRECFIELD - Record-field data. Sub-record fields are listed under the sub-record recname, so it can take several queries to get all the data for a record.
• PSDBFLDLABL - Label data for each field with DEFAULT_LABEL = 1 being the default label

You can also get some (but not all) metadata info in PeopleCode by using CreateRecord() to instantiate a Record object and looping thru the fields.




Local array of string &records;


&records = CreateArray("JOB", "EMPLOYMENT",...);
Local Record &recfield = CreateRecord(Record.RHRECFIELD);


Local integer &i = 0;
While &records.Next(&i)
   Local string &recname = &records [&i];
   &recfield.RECNAME.Value = &recname;
   Local Record &rec = CreateRecord(@("Record." | &recname));
   Local integer &f;
   For &f = 1 To &rec.FieldCount;
      Local Field &field = &rec.GetField(&f);
      &recfield.FIELDNAME.Value = &field.Name;
      &recfield.FIELDNUM.Value = &f;
      &recfield.KEY_FLG.Value = &field.IsKey;
      &recfield.REQUIRED_FLAG.Value = &field.IsRequired;
      &recfield.YES_OR_NO.Value = &field.IsYesNo;
      &recfield.Insert();
   End-For;
   
End-While;



I added some steps to get sub-record fields, translate the field types, get the XLAT values, etc. And finally run some stats on the actual data values.

It is not a polished program. There is a little hard coding and I just run it from App Designer. If you are interested I've exported the project to a zip file. It is available to download.