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.