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:
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
0 = Character
1 = Long character
2 = Number
3 = Signed number
4 = Date
5 = Time
6 = Date/Time
8 = Image OR Attachment
9 = Image Reference
%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.