Reusing Fields

On the same premise as the article on reusing views, this article explains how to find fields so that you can reuse them instead of creating your own.

Again this is a good idea because it 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.

The following query will help you find fields for reuse (this SQL was written for Oracle databases):

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 - you'll have to reverse engineer that one yourself ;)

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License