Creating Record Definitions

Viewing Record Definitions

You can see four views of the record by selecting View from the main toolbar: Field Display, Use Display, Edits Display, and PeopleCode Display.

• Common Elements in all Display modes: Num, Field Name, and Type.
• Field Display mode shows Basic Field Definitions: Len, Format, Short Name, and Long Name.
  
• Use Display mode shows Key-Related Characteristics and Default Values: Key, Ordr
  (order), Dir(direction), CurC(currency control), Srch(search), List, Sys(system), Audt(audit), and Default.
  
• Edits Display mode shows all Editing Options: Req(required), Edit, Prompt Table, Set Control Field, Rs Dt(reasonable date), and Event.
  
• PeopleCode Display mode contains a column for each PeopleCode program type.
• Note: You cannot add PeopleCode to a field until you save the record definition.

Naming Record Definitions

• The name length can be up to 15 characters, with the exception of the Temporary Table type, which has a maximum length of 13.
• The name must begin with a letter and can contain underscores to make it more readable.
  
• Avoid special characters, such as # or $, which can cause problems in some database environments.
• Record Naming Conventions: _TBL, _VW, _DVW, _WRK, _SBR, _QVW, _WL, R_, AUDIT_, WEBLIB_, FUNCLIB_, and DERIVED_

• Select the Record Type tab to define the type of record definition: SQL Table, SQL View, Dynamic View, Derived/Work, SubRecord, Query View,  and Temporary Table.
• Non Standard SQL Table Name is used to override the standard convention of prefixing PS_.
• Keys for dynamic views should not be effective date fields.

Setting Record Properties

•  Access the Record Properties, General tab to set General Properties: Description, Record Definition, Owner ID and Last Updated (Date/Time and By User).
• Access the Record Properties, Use tab to set Use Properties: Set Control Field, Record Relationships (Parent Record, Related Language Record, Query Security Record, and Optimization Delete Record), and Record Audit (Record Name and Audit Options -Add/Change/Selective/Delete). 
• Use tab, Record-Level Auto-Update Options (System ID Field and Timestamp Field)
• Use tab, Record Information Options (Tools Table, Managed, and Upgrade Job Not Needed)
• Note: The Append ALL (Dynamic Views) option applies to dynamic views. If it is set, at runtime the system appends the WHERE clause generated at runtime to all the SELECT statements in a dynamic view with a UNION.
        Also, if it is set, at runtime the system generates FILL as the table alias in the WHERE clause condition. For example, FILL. LIKE ’%O%’. You need to specify which table corresponds to the FILL in the dynamic view definition. For example, FROM tableA FILL. This is because the same record field name may exist in two different records.
  

Setting Record Field Properties  

• Setting Record Field Use Properties: Key, Duplicate Order Key, Alternate Search Key, Descending Key, Search Key, Search Edit, List Box Item, From Search Field and Through Search Field, Default Search Field, Disable Advanced Search Options, Audit (Field Add/Field Change/Field Delete), System Maintained, Auto-Update, Record Field Label ID, Default Value (Constant or Record Name and Field Name), and Default Page Control.
  
• Currency Control Field is available only for Number fields.
• Time Zone (Specified Time Zone and Time Zone Control Field) is available only for DateTime and Time fields.
• Note: System Maintained is for documentation purposes only and doesn’t affect processing.
• Setting Record Field Edit Properties: Required, Edit Type (No Edit and Table Edit), Table Edit (Type, Prompt Table, and Set Control Field), and Reasonable Date.

Control Tables and TableSets

This Post is moved to PeopleSoft Notes Blog

Planning Records

This Post is moved to PeopleSoft Notes Blog

Creating Field Definitions: Advanced

Using Multiple Labels

• Add/Delete a label from field definition
• Select record field labels from Use tab of Record Field Properties dialog box.
  
• Note: The label ID must be unique so that each field definition label can be identified separately, and it must be entered using uppercase letters with no spaces.
  
  
  

  View Field Definition Properties
  
• The General tab in the Field Properties dialog box provides a place for you to enter comments documenting the purpose of the field definition. 
• The Owner ID drop-down list box enables you to select a valid owner, as in application project or role.
• The Last Updated group box contains information—date, time, and user ID—about the last time that the field definition was modified.
• The Translate Values tab is available only for character fields.

Changing Field Definitions

• Change field lengths and/ field types.
• Rename/Delete a field.
• Note: The above changes to field definition requires modification of the underlying SQL table.

Setting Custom Field Formats

• Understanding Format Notation
• Lexical characters delimit character expressions and designate interpretation rules.
• Literal characters represent only themselves.
• Meta characters represent a class of characters.
• Meta Symbols: #; 0; .; 9; A; Z; @
• Lexical Symbols: *; +; []; {}; ?; !; ~; \; ()
  

• Changing format families. Max Display Length

• Editing formats.
• Add, update, and delete stored and display formats.
• Enter a description for the family.
• Specify options, such as uppercase and smart punctuation, for the family.
  

• Using family options.
• Smart Punctuation
• Make uppercase
  

• Test formats.
• Stored Format Name
• Display Format Name
• DB->Display
• Display->DB
  

Creating Field Definitions: Basic

• Create a new field definition. 
• Note: Char is the default field type for a new field.

• Specify field type for a new field definition.

1. Character (Char)
   
2. Long Character (Long)
3. Number (Nbr)
4. Signed Number (Sign)
5. Date (Date)
6. Time (Time)
7. DateTime (DtTm)
8. Image (Img)
9. ImageReference (Iref)
10. Attachment (Att)

• Specify attributes for a new field definition.
• Label ID
• Long Name
• Short Name
• Def (default)
• Not Used: Not Used is read-only in field definition properties. You can change its value using PeopleCode function SetDBFieldNotUsed, causing the field to be:
  • Skipped as part of the unique index.
  • Exposed to PeopleCode for read and write operations.
  • Ignored in Query.
  • Ignored in nVision.
  • Unavailable to users in search pages or list boxes if it is a search key or list box item.
• Chart Field: Read-only in the field definition properties dialog box. It is used for character fields and by financial applications to specify a usage pattern. You can change the value using PeopleCode.

• Specify character field attributes.
• Field Length: Max Length 254
  
• Format Type : Uppercase, Mixedcase, Numbers Only, SSN, SIN, Raw Binary, Name, Postal Code North America, Postal Code International, Phone Num North America, Phone Number International, and Custom
  

• Specify long character field attributes.
• Maximum Length: If you don’t specify a maximum length, or specify 0 (zero), you can enter an unlimited number of characters, depending on the capacity of your database system.
  
• Raw Binary: Raw binary is intended for binary data and byte strings. For example, raw binary can store graphics, sound, documents, and arrays of binary data.
  

• Specify number field attributes.
• Integer Positions
• Decimal Positions
• Signed
  

• Specify date field attributes.
• Length is 10 (MM/DD/YYYY)
  
• Default to 2000 if year less than or equal to (Default is 50)
  

• Specify time field attributes. 
• Length is 15 (HH:MI:SS:999999)
• Time Formatting: You can use %CLIENTDATE as the default constant for the Time field. %CLIENTDATE adjusts the date as appropriate to the time zone of the browser.
  

• Specify DateTime field attributes.
• Length is 26(YYYY-MM-DD-HH-MI-SS.999999)
• Time Formatting: %CLIENTDATE can be used as default.

• Specify image field attributes.
• Maximum Length: Depends on database platform
  
• Image Format: PeopleSoft supports BMP, DIB, GIF, CUT, EPS, JPG, PCX, PCT, RLE, and TGA image types. At runtime, the user can upload BMP, DIB, GIF, and JPG images to the database.

• Specify ImageReference field attributes
• Use the ImageReference field to store static images that can be changed at runtime with PeopleCode. By associating image definitions with an ImageReference field on a page, you can display images dynamically.
  

• Specify Attachment field attributes
• Attachment maps to a BLOB database type to hold the contents of a file attachment.
  However, PeopleSoft recommends using the existing field, FILE_DATA, if a BLOB column is needed. For attachments, use the subrecord ATTACH_DETAIL, which contains FILE_DATA.

Using the Translate Table

As a general rule, store field values in the Translate table if the field meets the following criteria:

1. Field type is Character.
2. Field length is 1 to 4 characters.
3. Field values consist of a relatively small, static set of values that are not maintained by the user.
4. No other fields relate to this field.

• Populate Field Value, Effective Date, Inactive, Long Name and Short Name to add values to the Translate table.
• To save the Translate table, you must be authorized to modify field definitions. There is a special Translates Only security access level for fields that allows translate values to be updated, but not other field attributes.
• When you select the Yes/No Table Edit, the system automatically points to the PSYESNO field in the Translate table
• Translate Table Attributes are: FIELDNAME, LANGUAGE_CD, FIELDVALUE, EFFDT, VERSION, EFF_STATUS, XLATLONGNAME, XLATSHORTNAME, LASTUPDDTTM and LASTUPDOPRID

8 Steps of Application Development

This Post is moved to PeopleSoft Notes Blog