Reusing Fields


Wherever possible you should re-use fields in PeopleSoft rather than creating your own. This minimises customisations and if used appropriately it reduces development effort. A good compromise you can achieve with fields is that you can use an existing field but add a new label if the field format is fine but the field label does not suit.

Its always a good idea in any case to do a quick search before creating a new field. The following query will help you find fields for reuse:

NOTE: this SQL was written for Oracle databases but shouldn't be hard to convert.
select
    F.FIELDNAME,
    (case
        when F.FIELDTYPE = 0 then F.FIELDTYPE || ' = Character'
        when F.FIELDTYPE = 1 then F.FIELDTYPE || ' = Long Character'
        when F.FIELDTYPE = 2 then F.FIELDTYPE || ' = Number'
        when F.FIELDTYPE = 3 then F.FIELDTYPE || ' = Signed Number'
        when F.FIELDTYPE = 4 then F.FIELDTYPE || ' = Date'
        when F.FIELDTYPE = 5 then F.FIELDTYPE || ' = Time'
        when F.FIELDTYPE = 6 then F.FIELDTYPE || ' = DateTime'
        when F.FIELDTYPE = 8 then F.FIELDTYPE || ' = Image OR Attachment'
        when F.FIELDTYPE = 9 then F.FIELDTYPE || ' = ImageReference'
        else 'Unknown' end) as FIELDTYPE,
    L.LABEL_ID,
    L.LONGNAME,
    L.SHORTNAME,
    L.DEFAULT_LABEL,
    F.LENGTH,
    F.DECIMALPOS,
    F.FORMAT,
    F.FORMATLENGTH,
    F.IMAGE_FMT,
    F.FORMATFAMILY,
    F.DISPFMTNAME,
    F.DEFCNTRYYR,
    F.IMEMODE,
    F.KBLAYOUT,
    F.DESCRLONG,
    F.LASTUPDDTTM,
    F.LASTUPDOPRID
from
    PSDBFIELD F inner join PSDBFLDLABL L
    on F.FIELDNAME = L.FIELDNAME
where
    F.FIELDTYPE = 'field_type_0-9'
    and F.FIELDNAME like '%field_search%' 
    and L.LONGNAME like '%label_search%'
order by
    F.FIELDNAME
;

Simply replace the field_type_0-9 with the appropriate numeric code (not sure what happened to field type 7?):

  • 0 = Character
  • 1 = Long character
  • 2 = Number
  • 3 = Signed number
  • 4 = Date
  • 5 = Time
  • 6 = Date/Time
  • 8 = Image OR Attachment
  • 9 = Image Reference

Then replace %field_search%with your partial field name search e.g. %OPR% or replace %label_search% with a partial label search e.g. %User%. If you want to wild card either of these, just use a single %.

You may want to add more restrictive conditions such as the field length (e.g. LENGTH = 30) or if you want to get adventurous, the appropriate field format code but you'll have to reverse engineer that one yourself.


Revision #1
Created Mon, Aug 5, 2019 2:55 PM by PeopleSoft Wiki
Updated Mon, Aug 5, 2019 2:59 PM by PeopleSoft Wiki