Creating New Field Definitions

This section lists prerequisites and discusses how to:

  • Create a new field definition.

  • Specify attributes for a new field definition.

  • Specify Character field attributes.

  • Specify Long Character field attributes.

  • Specify Number field attributes.

  • Specify Date field attributes.

  • Specify Time field attributes.

  • Specify Datetime field attributes.

  • Specify Image field attributes.

  • Specify ImageReference field attributes.

  • Work with additional Oracle data type mappings.

  • Work with additional Microsoft SQL Server 2005 data type mappings

You should be familiar with the general terminology and functions of PeopleSoft Application Designer.

To create a new field definition:

  1. Click the New button on the PeopleSoft Application Designer toolbar.

    The New dialog box appears.

  2. Select Field.

  3. Click OK.

    A new character field opens in the definition workspace area.

  4. Select one of the following field types from the Field Type drop-down list box.

    Note: Make sure you name your fields according to accepted naming conventions.

    See Saving Definitions.

    Field or Control

    Description

    Character (Char)

    Alphanumeric field of fixed length.

    Long Character (Long)

    Alphanumeric field of variable length used for textual entries, such as comments or descriptions.

    Number (Nbr)

    Positive numeric field of fixed length for which decimals are allowed.

    Signed Number (Sign)

    Positive or negative numeric field of fixed length for which decimals are allowed.

    Date (Date)

    Date field of constant length. The system edits dates and prohibits incorrect ones, such as day 42 or month 20. Date fields always store a four-digit year.

    Time (Time)

    Time field of a constant length. Built-in edits prohibit impossible times, such as hour 26, minute 70, or second 94.

    Datetime (DtTm)

    Date and time field of constant length. Built-in edits prohibit impossible dates and times, as defined in the individual date and time fields. Datetime fields always store a four-digit year.

    Image (Img)

    Image field to store images in a user-defined format, such as JPG or GIF.

    ImageReference (Iref)

    Field in which to change an image dynamically at runtime using PeopleCode.

    Attachment (Att)

    Field that maps to a BLOB database type to hold the contents of a file attachment. However, you should use the existing field, FILE_DATA, if you need a BLOB column. For attachments, use the subrecord ATTACH_DETAIL, which contains FILE_DATA.

    After you select a field type, a field definition window appears in the definition workspace. Use the field definition window to specify attributes to complete your new field definition.

Access the field definition window for a new field definition.

All field types share the following property settings.

Note: Multiple views of this dialog box are available, depending on which field type you select. Fields that are common to all views are documented first.

Field or Control

Description

Label ID

Enter a unique identifier for the field that is less than 18 characters, without spaces, and in uppercase. Make this the default label by selecting the Def check box in the last column; otherwise, the first label is the default. You can set only one field label as the default.

Note: You can specify multiple labels for one field and change them at runtime using PeopleCode.

Label IDs must contain alphanumeric characters only and cannot consist entirely of spaces.

See Using Multiple Labels.

Long Name

Enter up to 30 characters. The name that you enter here appears as the RFT Long option for a page field. This label is also used for the tool tip, or hover text, for links at runtime if it is different from the assigned display value of the link.

Note: Do not use the \ (backslash) character in the long name of cubes and members. Unexpected behavior might occur.

Short Name

Enter up to 15 characters as an alternate name to use on pages and reports if you have insufficient space to display the long name. If you leave this field blank, the system automatically copies the first 15 characters of the long name into this field. The name that you enter here appears as the RFT Short option for a page field.

Note: If you name the field the same as the default label ID, you can identify it more easily when using PeopleCode.

Note: Do not use the \ (backslash) character in the short name of cubes and members. Unexpected behavior might occur.

Def (default)

When you enter the first label ID, the system automatically selects the Def check box. The system uses this default field if you have multiple labels. You can have only one default field.

Not Used

This check box is always read-only in the field definition properties dialog box. You can change its value using PeopleCode, 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 PS/nVision.

  • Unavailable to users in search pages or list boxes if it is a search key or list box item.

See SetDBFieldNotUsed.

Chart Field

This field is 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.

Encrypt

Select this option to encrypt the field using Oracle Transparent Data Encryption (TDE).

Note: The Encrypt option is disabled for field types that do not support encryption, for database platforms other than Oracle, as well as for Oracle database versions that do not support TDE.

See Encrypting Fields.

Use character fields for names, codes, and any fields with letter values. Also use them for numbers for which you want to contain formatting attributes and the data itself. Examples of such fields are those in which you enter telephone numbers and zip or postal codes. You cannot use character fields for calculations. You can enter decimal numbers in character fields, but you cannot use them mathematically until you convert them back to numeric fields.

You can specify character field attributes at design time, and you can access most of them at runtime with PeopleCode.

Field or Control

Description

Field Length

Enter a whole number between 1 and 254 for the length of the field.

Format Type

Select one of the following values:

  • Uppercase: Converts the field value to uppercase and signifies that no other formatting options apply to this field. Use this option for code values, such as department ID, for which it does not matter whether the user enters the value in uppercase or lowercase. Uppercase is the default format.

  • Mixedcase: Stores uppercase and lowercase characters as entered. Use this option for fields that contain textual data, such as a department or company division name. For example, Accounting - Receivables is more readable than ACCOUNTING - RECEIVABLES.

  • Numbers Only: Forces entries to be numeric, which is useful for enforcing numeric values without redefining the field as a number field. This option automatically populates fields with leading zeros. For example, if the user enters 1 in a three-digit field, the system changes this value to 001.

  • SSN: Formats the entry in U.S. Social Security Number format (999-99-9999). Define the field length as 9, even though the display length is 11. The system automatically adds dashes when formatting the field for display, but the number is stored in the database without the dashes.

  • SIN: Formats the entry in Canadian Social Insurance Number format (999-999-999) and performs the standard check-digit verification for SIN. Define the field length as 9, even though the display length is 11. The system automatically adds dashes when formatting the field for display, but the number is stored in the database without the dashes.

  • Raw Binary: Allows character fields containing embedded null values, such as encrypted values.

  • Name: Indicates that the field entry uses the PeopleSoft standard name convention:

    [Lastname] [Suffix],[Prefix] [Firstname] [Middle name/Initial]

    The entry can contain alphanumeric characters, spaces, periods, hyphens, and apostrophes. Uppercase and lowercase characters are preserved as entered, that is, mixed case formatting is used automatically.

    Important! While a space is a valid character, it is not allowed immediately after the comma separating the last name from the first name.

    Valid entries might include:

    • O’Brien,Michael

    • Jones IV,James

    • Phillips MD,Deanna Lynn

    • Reynolds Jr.,Dr. John Q

    • Phipps-Scott,Ms. Adrienne

    • Knauft,Günter

    Note: For names that include Chinese, Japanese, or Korean (CJK) ideographic characters, different standard name conventions apply. See Using CJK Ideographic Characters in Name Character Fields.

  • Postal Code North America: Formats the entry to U.S. zip code format or Canadian postal code format. Canadian postal codes are edited to ensure that alphabetical and numeric characters are entered in the correct positions.

    The following entry formats apply. Stored formats appear in parentheses following the entry format:

    • 99999 (99999)

    • 99999-9999 (99999-9999)

    • 999999999 (99999-9999)

    • A9A9A9 (A9A 9A9)

    • A9A 9A9 (A9A 9A9)

  • Postal Code International: Allows the entry of international postal codes. You must enter at least 7 digits. If you enter 9 digits, the system assumes that the value is a U.S. zip code; if the entry is in A9A9A9 format, the system assumes that the value is a Canadian postal code. The system formats the value accordingly. For all other entries, no formatting is applied.

  • Phone Num North America (phone number North America): Formats the entry in North American telephone number format.

    The following entry formats apply. Stored formats appear in parentheses following the entry format:

    • 9999999 (999-9999)

    • 999-9999 (999-9999)

    • 9999999999 (999/999-9999)

    • 999/999-9999 (999/999-9999)

  • Phone Number International: Allows the entry of international telephone numbers. If you enter 7 or 10 digits (and no other characters), the system assumes that the value is a North American phone number and formats it accordingly. For all other entries, no formatting is applied.

  • Custom: If you select this value, the Family Name and Display Name fields become available for entry. These selections enable you to apply additional formatting attributes that affect how the field appears on a page.

Depending on your database environment, the maximum length of a long character field ranges from several thousand characters to 32,767 characters (64,000 bytes). Using a long character field instead of a regular character field enables more information to be added, and the user can insert a tab at runtime by pressing Ctrl+Tab.

The maximum Large Object (LOB) size allowed by PeopleTools is 50 MB.

Note: For Db2 for z/OS, the maximum Large Object (LOB) size is approximately 32700 KB, depending on the page size (as determined by the Buffer Pool) and the internal Db2 lengths of the rest of the columns in the row.

Field or Control

Description

Maximum Length

To control the length of a long character field, enter the maximum number of bytes that you want the system to write to this field. If you do not specify a maximum length, or specify 0 (zero), you can enter an unlimited number of characters, depending on the capacity of your database system.

When working with fields, be aware that your chosen database system limits the amount of data contained in a field. For Oracle the field size is truncated to 1333 characters. Consider this limitation when choosing to use fields of a Long Character data type.

For Oracle: If the maximum length of a long field is less than 4001 bytes, the field can be stored more efficiently as a VARCHAR2(4000). To support the PeopleSoft Unicode implementation on the Oracle platform, we defined long columns with a specified length of 1330 or less as VARCHAR2 columns. If the length specified was greater than 1330, then the column was generated as a long VARCHAR or long raw, depending on the setting of the Raw Binary check box. This setting allows for the maximum possible usage of a tripled VARCHAR2 character length for Unicode-defined databases.

For Db2 for z/OS: Character fields defined with a length specification greater than 254 are automatically defined as LONG VARCHAR in EBCDIC databases and LONG VARGRAPHIC in Unicode databases.

Note: For all supported database platforms, it is more efficient to define a reasonable length for long character fields than to simply use 0. Specifying 0 forces PeopleTools to define LOBs automatically for those databases for which LOBs are supported. Due to potential performance implications, LOBs should not be used indiscriminately.

Raw Binary

Select to store data that is not converted when moving data between different systems. Raw binary is intended for binary data and byte strings. For example, raw binary can store graphics, sound, documents, and arrays of binary data.

When you create a new field, two field types are available for numbers:

  • Number fields

  • Signed number fields

Both field types are fixed in field length and allow the entry of positive numbers. Only signed numbers, however, allow the entry of negative numbers. Otherwise, both number field types are the same.

Unlike character fields, a number field can contain decimals but does not contain special formatting, such as the formatting for telephone numbers. Use number fields for calculations. If you use number fields for codes, you sacrifice flexibility to change your coding structure to alphanumeric in the future.

This example illustrates the fields and controls on the Number Field type. You can find definitions for the fields and controls later on this page.

 Number Field type

Field or Control

Description

Integer Positions

Specify the number of integer positions to the left of the decimal. If the number contains only decimal positions, leave this field blank.

Decimal Positions

Specify the number of decimal positions to the right of the decimal. If a number contains only integer positions, leave this field blank. In calculations, the system rounds up the result to the number of decimal positions defined here.

Note: Number fields cannot exceed 31 positions. Therefore, the sum of integer and decimal positions cannot exceed 31.

The field length notation differs between PeopleSoft Application Designer and SQL. If you specify a field length of 8 integer positions and 3 decimal positions in PeopleSoft Application Designer, SQL processes that as a length notation of 11.3.

SQL describes field length in terms of precision and scale. Precision is the total number of integer and decimal positions. Scale is the number of decimal positions. Thus, 11.3 means 8 integer positions and 3 decimal positions; 11.0 means 11 integer positions and 0 decimal positions.

When you enter 0 in the Decimal Positions field and enter 5 or 10 in the Integer Positions field, the RawBinary format option appears. The RawBinary format converts a number field value to a full-length 16- or 32-bit integer. See the example in the table that follows.

Signed

Select this check box for a field that is used for arithmetic calculations that might contain a negative value.

This table shows how the integer position affects the RawBinary storage format:

Integer Position

Maximum Value

Number Stored As

5

65535

16-bit integer

10

4294967295

32-bit integer

Date fields contain calendar dates. A date field has a field length of 10 and is maintained by the system. The default format of a date field is defined by the database and can be overridden by your browser settings.

Field or Control

Description

Default to 2000 if year less than or equal to

Specify the last two digits of a year, which is typically what users enter in a date field. If you enter 50, the default century becomes 2000 when someone enters 0 through 50 as the year in a date field. If someone enters a number between 51 and 99 in a date field, the default century becomes 1900. The default value is 50.

Note: When working with date and time fields on pages and search pages, carefully consider search criteria. Assume the date, time, and time zone fields are separate with the condition that the time is time zone-controlled by the time zone field and the time is associated with the date field. In this case, you need to provide date, time and time zone on the search page if any one of those search criteria is required.

Time fields are fixed-length fields (15 positions, format of HH:MI:SS.999999) that contain the time of day. The maximum time precision varies, depending on your database.

Field or Control

Description

Time Formatting

Specify the formatting for this field. HH represents hours, MI represents minutes, SS represents seconds, and 999999 represents microseconds.

Note: 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.

The Datetime field is a fixed-length field (26 positions, format of YYYY-MM-DD-HH-MI-SS.999999) that holds dates and times. The maximum date and time precision depends on your database.

Note: You can use %CLIENTDATE as the default constant for the Datetime field. %CLIENTDATE adjusts the date as appropriate to the time zone of the browser.

Use Image fields to store application data that takes the form of images. Image fields are functionally no different from any other type of field. Just as end-users employ character fields to store names and addresses, they can employ image fields to keep track of all types of digital pictures. This field enables you to store pictures of assets, company logos (in SQL tables), or scanned images of employees (as part of their personal data rows).

Field or Control

Description

Maximum Length

Specify the maximum number of bytes available to store an image. If you do not specify a maximum length, the maximum length is determined by your database platform. If a user attempts to cut and paste an image that is larger than the maximum length or database capacity, an error message appears.

The following databases apply these maximums:

  • Oracle (2 gigabytes).

  • Db2 for z/OS: Approximately 32700 kilobytes, depending on the page size, (as determined by the Buffer Pool) and the internal Db2 lengths of the rest of the columns in the row.

  • Microsoft SQL Server (2 gigabytes).

Image Format

Select an image format. The format specified on the field definition restricts valid images to that type only.

Image fields support BMP, CUT, DIB, GIF, EPS, JPG, PCT, PCX, PNG, RLE, TGA, and SVG formats. At runtime using the PeopleCode InsertImage function, users can upload BMP, DIB, JPG, PNG, and SVG images to the database.

Note: For the record that contains an image field, the record must have at least one key if the image field is to be displayed on a page. This is requirement so that unique image file names can be generated.

Note: When using images of the SVG format, be sure that the XML declaration of the SVG image is of an expected format. For example:

<?xml version="1.0" encoding="utf-8"?>

or,

<?xml version="1.0" standalone="no"?>

If the XML declaration is not formatted correctly, this can result in errors, such as errors when creating the definition, not being able to view height and width in Application Designer, not being able to view the image at runtime, and so on.

Use the ImageReference field to store static images that you can change at runtime using PeopleCode. By associating image definitions with an ImageReference field on a page, you can display images dynamically. For example, to have different images appear on an employee profile depending on the status of the employee’s current review, use the ImageReference field to reference the current review field and display the appropriate image.

If your site has upgraded to a PeopleSoft 9.x application and performed the appropriate conversion process during the application upgrade, different data type mappings exist between PeopleSoft field types and the underlying Oracle system.

See The upgrade documentation and release notes for your release for information about the conversion process.

The following table describes how the PeopleSoft field types map to the underlying Oracle data types:

PeopleSoft Field Type

Pre-Conversion Oracle Data Type Mapping

Post-Conversion Oracle Data Type Mapping

Character

ANSI: VARCHAR2(n)

Unicode:VARCHAR2(n*3)

ANSI: VARCHAR2(n)

Unicode: VARCHAR2(n)

Note: For Unicode the VARCHAR2 column is interpreted as the number of Unicode characters due to init.ora parameter NLS_LENGTH_SEMANTICS being set to "CHAR".

Long Character

If 1 ≤ n ≤ 2000

ANSI: VARCHAR2

Unicode:VARCHAR2(n*3) CHECK (Length <field name>) ≤ n)

If n = 0 or n > 2000

ANSI: LONG VARCHAR

Unicode :LONG VARCHAR

ANSI: VARCHAR2(n) when 1 ≤ n ≤ 4000

CLOB when n = 0, or n > 1333

Unicode: VARCHAR2 (n) when 1 ≤ n ≤ 1333

CLOB when n = 0, or n > 1333

Note: For Unicode the VARCHAR2 column is interpreted as the number of Unicode characters due to init.ora parameter NLS_LENGTH_SEMANTICS being set to "CHAR".

Long Character with Raw Binary attribute

ANSI: LONG RAW

Unicode: LONG RAW

ANSI: BLOB

Unicode: BLOB

Image

ANSI: LONG RAW

Unicode: LONG RAW

ANSI: BLOB

Unicode: BLOB

Attachment

ANSI: LONG RAW

Unicode: LONG RAW

ANSI: BLOB

Unicode: BLOB

Date

DATE

DATE

Datetime

DATE

TIMESTAMP

Time

DATE

TIMESTAMP

If your site has upgraded to a PeopleSoft 9.x application and performed the appropriate conversion process during the application upgrade, different data type mappings exist between PeopleSoft field types and the underlying Microsoft SQL Server 2005 system.

See The upgrade documentation and release notes for your release for information about the conversion process.

The following table describes how the PeopleSoft field types map to the underlying Microsoft SQL Server data types:

PeopleSoft Field Type

Pre-Conversion Microsoft SQL Server Data Type Mapping

Post-Conversion Microsoft SQL Server Data Type Mapping

Character

ANSI: CHAR

Unicode: NCHAR

ANSI: VARCHAR(n)

Unicode: NVARCHAR(n)

Long Character

ANSI: TEXT

Unicode: NTEXT

ANSI: VARCHAR(MAX)

Unicode: NVARCHAR(MAX)

Long Character with Raw Binary Image Attachment

IMAGE (ANSI and Unicode)

VARBINARY(MAX) (ANSI and Unicode)