Creating Field Definitions

This chapter provides an overview of field definitions and discusses how to:

Click to jump to parent topicUnderstanding Field Definitions

Fields are the basic building blocks in your PeopleSoft system; in database terms, they represent columns in a table or view. Field definitions are stand-alone development definitions, defined in PeopleSoft Application Designer, and can be shared across multiple record definitions. Changes to field properties affect all records that include that field.

Each field definition includes attributes, such as data type, field name, long name, short name, field length, and various formatting values.

Fields in Record Definitions

By grouping fields that share a common theme, you build a record definition. For example, a department table (record) might include a department ID field, department name field, location field, and so on.

For each field definition that you add to a record, you define additional characteristics, called record field properties, that are specific to the way the field is used in that particular record. Record field properties are not shared with other records in which the field is used.

Click to jump to parent topicCreating New Field Definitions

This section assumes that you are already familiar with the general terminology and functions of PeopleSoft Application Designer.

This section discusses how to:

Click to jump to top of pageClick to jump to parent topicCreating a New Field Definition

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 workspace area.

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

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

    See Saving Definitions.

    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 JPEG or GIF.

    ImageReference (Iref)

    Use this to change an image dynamically at runtime using PeopleCode.

    Attachment (Att)

    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.

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

Click to jump to top of pageClick to jump to parent topicSpecifying Attributes for a New Field Definition

Access the field definition window for the new field definition.

The following property settings are shared by all field types.

Note. Multiple views of this dialog box are available, depending on which field type you select. PeopleSoft documents fields that are common to all views first.

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.

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.

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.

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 nVision.

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

See SetDBFieldNotUsed.

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.

Click to jump to top of pageClick to jump to parent topicSpecifying Character Field Attributes

Character fields are used for names, codes, and anything with letter values. You 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 in 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 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 doesn’t matter whether the user enters the value in uppercase or lowercase. This 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. This 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 to 001.

SSN: Formats the entry in U.S. Social Security Number format (999-99-9999). Define 9 as the field length, 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 9 as the field length, 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 NULLs, such as encrypted values.

Name: Requires that the field entry appear in the PeopleSoft standard name format:

[lastname] [suffix],[prefix] [firstname] [middle name/initial]

The entry can contain alphabetic characters, spaces, periods, hyphens, and apostrophes. Uppercase and lowercase characters are preserved as entered—in other words, mixed case formatting is included automatically. Valid entries might include:

  • O’Brien,Michael.

  • Jones IV,James.

  • Phillips MD,Deanna Lynn.

  • Reynolds Jr.,Dr. John Q.

  • Phipps-Scott,Adrienne.

  • Knauft,GŁnter.

Note. If the name contains Japanese characters, the first and last names must be separated by a space instead of a comma. The Japanese name format can contain hankaku katakana, Zenchiku katakana, hiragana, kanji, and romaji characters. It can also include spaces, periods, and hyphens.

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 alpha 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. The field length must be at least 7 digits. If 9 digits are entered, 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. Then, the value is formatted 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.

Click to jump to top of pageClick to jump to parent topicSpecifying Long Character Field Attributes

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 the user to insert a tab at runtime by pressing ctrl+tab.

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

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 PeopleSoft's Unicode implementation on the Oracle platform, PeopleSoft defined "long" columns with a specified length of 1330 or less as VARCHAR2 columns. If the length specified was greater than 1330, the column was generated as a long VARCHAR or long raw, depending on the setting of the Raw Binary check box. This allows for the maximum possible usage of a tripled VARCHAR2 character length for Unicode-defined databases.

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.

Click to jump to top of pageClick to jump to parent topicSpecifying Number Field Attributes

When you create a new field, notice that there are two field types for numbers:

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.

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 Structured Query Language (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 below.

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

Click to jump to top of pageClick to jump to parent topicSpecifying Date Field Attributes

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.

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.

Click to jump to top of pageClick to jump to parent topicSpecifying Time Field Attributes

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.

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.

See Also

%ClientDate

%Date

Click to jump to top of pageClick to jump to parent topicSpecifying DateTime Field Attributes

The DateTime field is a fixed-length field (26 positions, format of YYY-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.

See Also

%ClientDate

%Date

Click to jump to top of pageClick to jump to parent topicSpecifying Image Field Attributes

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).

Maximum Length

Specify the maximum number of bytes available to store an image. If you don’t 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 (maximum length appears in parentheses):

  • DB2/zOS (up to 32 kilobytes*).

  • Oracle (2 gigabytes).

  • Sybase (2 gigabytes).

  • SQL Server (2 gigabytes).

  • Informix (2 gigabytes).

  • DB2 UDB for Linux, Unix, and Windows (32 kilobytes).

* Depends on the page size, the buffer pool, and the other data that is being stored in that row.

Image Format

Select an image format. Format determines the type of image that you can select and write to the database for this field. 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.

Click to jump to top of pageClick to jump to parent topicSpecifying 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. 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.

Click to jump to top of pageClick to jump to parent topicWorking With Additional Oracle Data Type Mappings

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

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

The following table describes how the PeopleSoft field types involved 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 is 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 > 4000

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 is 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

Click to jump to top of pageClick to jump to parent topicWorking With Additional Microsoft SQL Server 2005 Data Type Mappings

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

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

The following table describes how the PeopleSoft field types involved 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)

Click to jump to parent topicUsing Multiple Labels

This section provides an overview of multiple labels and discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding Multiple Labels

You can define additional label pairs and an identifier for any field. Multiple fields are useful when you want different labels to appear on different pages. For example, on one page you can set the label to Begin Date such as to mark the beginning of a vacation, while on another page the label might be Class Start Date to mark the beginning of a training course.

Multiple labels are displayed in alphabetical order and sorted by label ID. You can sort the labels in a field by double-clicking the column header. The sorting is not saved, but it is helpful for quickly viewing the labels.

Click to jump to top of pageClick to jump to parent topicAdding a New Label

Open the field definition in the definition workspace.

To add a new label, enter the label ID, long name, and short name in the last row. Each label can have related language labels. After the multiple labels are defined, you can select appropriate labels when designing a record or page. You can also specify different labels in PeopleCode. To add a line feed for Long Name and Short Name, enter \n (backslash and the letter n). For example, work \n month adds a line feed between the words work and month.

When you create a new label, the default label is still the first label ID. You can change the default label by selecting Def for a different row. There can be only one default label.

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.

Click to jump to top of pageClick to jump to parent topicDeleting a Label

Open the field definition in the definition workspace.

To delete a label, right-click a field label row and select Delete Label from the pop-up menu. Labels that are currently in use cannot be deleted. To determine where the label is used, select the label and select Find Definition Reference from the pop-up menu. The results appear in the Find Definition Reference output window.

Click to jump to top of pageClick to jump to parent topicSelecting Record Field Labels

Access the Record Field Properties dialog box.

For the record definition, the Record Field label ID drop-down list box is available on the Use tab of the Record Field Properties dialog box. The drop-down list box displays the label ID, short name, and long name that you set in the field definition. *** Use Default Label *** is the default selection. The record field label changes automatically whenever you change the default label in the field definition.

Click to jump to parent topicOpening an Existing Field Definition

There are several ways to open a field definition:

Click to jump to parent topicViewing Field Properties

There are several ways to view field 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.

See Also

Using the Translate Table

Click to jump to parent topicChanging Field Definitions

This section provides an overview of the effects of changing field definitions and discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding the Effects of Changing Field Definitions

When you develop new record definitions or modify existing ones, you might need to change characteristics or attributes for a field. If you change attributes in a field definition, the change affects every occurrence of the field in every record definition. So, before you change basic attributes in a field definition, consider how it affects all of the record definitions in which the field occurs. If the change isn’t appropriate for every occurrence of this field, consider defining a new field instead.

If you still find that a change is needed, PeopleSoft recommends that you determine the extent of the change by finding out how many records are affected. Do this by using the Find Definition References tool. Open the field definition and select Edit, Find Definition References, or right-click and select the option from the pop-up menu. If you’re in a record definition window, another method is to select the field, right-click the field name, and select Find Definition References≠-Field from the pop-up menu.

The Find Definition References tab in the Output window displays a list of all record definitions, page definitions, messages, channels, file layouts, and PeopleCode programs in which the field is used. Double-click a line of output to open the corresponding definition in the definition workspace.

Note. Changing the name or length in a field definition requires modification of the underlying SQL table. Do this either by running the build process and specifying Create Tables or Alter Tables, or by carrying out an action issued by your system administrator. For example, if you change the length in a field definition and 30 record definitions contain the same field name, you have 30 records that must be created or altered.

Click to jump to top of pageClick to jump to parent topicChanging Field Types

Change a field type by opening a field and selecting another field type from the drop-down list box. However, this feature is intended as a design-time change—that is, before your tables have been created in the database. While some field type conversion can be done, results vary, depending on your database platform.

Note. If you change a field from one type to another, all records containing that field must be rebuilt by running the Alter process. Use Find Definition References to find all incidences of the changed field.

See Altering Tables.

Click to jump to top of pageClick to jump to parent topicRenaming Field Definitions

You can use either the Rename Definition dialog box or the Open Definition dialog box to rename a field definition.

Using the Rename Definition Dialog Box

To rename a field definition from the Rename Definition dialog box:

  1. Select File, Rename to open the Rename Definition dialog box.

  2. Select Field as the definition type.

  3. Enter the name of the field.

    If you aren’t sure of the name or the correct spelling, use the search criteria fields to locate the field to rename. The list displays field names that match your search criteria.

  4. To rename a field in the list, select the field and click Rename, or double-click the field.

    The field name becomes available for entry, and you can type the new name.

  5. When you finish typing the new name, press enter or click the Rename button.

    The Rename Field dialog box appears. Select the records in which you want the field to be renamed. The De-select All button toggles to Select All.

  6. Click OK.

Using the Open Definition Dialog Box

To rename a field definition from the Open Definition dialog box:

  1. Select File, Open to access the Open Definition dialog box.

    This dialog box enables you to set up search criteria for locating field names.

  2. Select Field as the definition type.

    If you have additional information, use the Name field. You can further narrow your search criteria by selecting a field type or the name of the project that contains the field to delete.

  3. Click the Open button or press enter when you have completed your search criteria.

  4. You can rename a field in the Open Definition dialog box by selecting the field, right-clicking the field name, and selecting Rename.

    You can also select the field name and click it a second time. The field name becomes available for entry and you can enter a new name.

  5. When you finish typing the new name, press enter or click the Rename button.

Results of Renaming Field Definitions

When you rename a field, the system automatically changes all occurrences of it, including data on tables with columns named FIELDNAME where the data matches the field being renamed.

The only references that are not renamed are the text portions of SQL functions, such as SQLExec and Scroll Select. If you have already used SQL Create to create the underlying tables for the record definitions that contain the field that you renamed, you must re-create or alter those tables. If the tables contain data that you want to preserve, use the SQL Alter function to rename the database table fields.

Click to jump to top of pageClick to jump to parent topicDeleting Field Definitions

You can use either the Delete Definition dialog box or the Open Definition dialog box to delete a field definition.

Using the Delete Definition Dialog Box

To delete field definitions by using the Delete Definition dialog box:

  1. Select File, Delete to open the Delete Definition dialog box.

    Set up search criteria for locating field names.

  2. Select Field as the definition type.

  3. If you have additional information, use the Name field.

  4. Select a field type or the name of the project that contains the field that you want to delete.

    This further narrows your search criteria.

  5. When your search results appear in the output window, delete the field definition by selecting the field and clicking the Delete button or double-clicking the field name.

Note. You cannot delete a field that is currently used in any records. Before you delete a field definition, you must first remove it from records in which it appears.

Using the Open Definition Dialog Box

To delete field definitions by using the Open Definition dialog box:

  1. Select File, Open to access the Open Definition dialog box.

    This dialog box enables you to set up search criteria for locating field names.

  2. Select Field as the definition type.

  3. If you have additional information, enter it in the Name field.

  4. You can further narrow your search criteria by selecting a field type or the name of the project that contains the field that you want to delete.

  5. When you have completed your search criteria, click Open or press enter.

  6. Right-click the field that you want to delete from the list of field names displayed.

  7. Select Delete from the pop-up menu.

Click to jump to top of pageClick to jump to parent topicPrinting Field Definitions

If you plan to make changes to your field definitions, PeopleSoft recommends viewing your field definition before you proceed. You can view it on your screen or print it to a one-page report that combines information from several resources. This can also serve as a paper audit trail to document your database.

To print a field definition, select File, Print from the field definition window (or click the Print button in the toolbar) to open the Print dialog box, where you specify printing options. You can also print field definitions from the Open Definition dialog box. On the list resulting from your search, right-click the name of the field definition that you want to print, and select Print from the pop-up menu to view your printing options.

Click to jump to parent topicSetting Custom Field Formats

This section provides overviews of custom field formats and format notation and discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding Custom Field Formats

PeopleTools has formatting provisions at both the field and page levels. The field format specifications affect internal values that are stored in the database, and the page formats affect the visual presentation of values.

There are several formatting options for U.S. Social Security Numbers, Canadian Social Insurance Numbers, postal codes, telephone numbers, and various currencies, dates, and times. Custom field formats extend these formatting provisions to support the same types of data in other countries. They also support other types of formatted information, such as bank codes, credit card numbers, part numbers, or serial numbers.

Click to jump to top of pageClick to jump to parent topicUnderstanding Format Notation

Stored and display formats are defined by strings that contain lexical, literal, and meta characters:

Use the format symbols in the following tables to create the format notation for your own custom field formats:

Meta Symbol

Description

#

Digit placeholder.

If the number has more digits to the right of the decimal point than there are # symbols to the right in the format, the system truncates the number to as many decimal places as there are # symbols to the right.

If the number has more digits to the left of the decimal point than there are # symbols to the left in the format, the system displays the extra digits.

If the number has fewer digits to the right of the decimal point than there are # symbols to the right of the decimal point in the format, the system adds spaces.

If the number has fewer digits to the left of the decimal point than # symbols to the left of the decimal point in the format, the system also adds spaces.

Example format: ###.##

Input: 1234.567 - Matches? Yes - Output: 1234.56

0 (zero)

Digit placeholder.

Follows the same rules as for #, except that if the number has fewer digits than there are 0s in the format, the system displays the extra 0s.

Example format: 000.00

Input: 1.2345 - Matches? Yes - Output: 001.23

(period)

Decimal point.

This symbol determines how many digits (0 or #) appear to the right and left of the decimal point.

If the format contains only # symbols to the left of this symbol, numbers less than one appear with a decimal point.

This symbol has meaning only in conjunction with the # and 0 symbols.

Example format: ###.##

Input: .12345 - Matches? Yes - Output: .12

9

Required numeric placeholder.

If the number does not have the same number of digits as there are 9s, the system displays an error message.

Example format: 999

Input: 123 - Matches? Yes - Output: 123

Input: 12 - Matches? No

A

Matches any alphabetic character: A− Z or a−z.

Example format: AA

Input: Sd - Matches? Yes - Output: Sd

Input: 4A - Matches? No

Z

Matches any alphabetic or numeric value: A−Z, a−z, or 0−9.

Example format: ZZ

Input: 3g - Matches? Yes - Output: 3g

Input: A3C - Matches? No

@

Matches any character.

Example format: @@@

Input: 1q? - Matches? Yes - Output: 1q?

 

Lexical Symbol

Description

*

Matches zero or more occurrences of this character expression.

Example format: a*b

Input: b - Matches? Yes - Output: b

Input: ab - Matches? Yes - Output: ab

Input: aab - Matches? Yes - Output: aab

+

Matches occurrences of this character expression.

Example format: a+b

Input: b - Matches? No

Input: ab - Matches? Yes - Output: ab

Input: aab - Matches? Yes - Output: aab

[ ]

Denotes a character expression that matches the input character if the input character is the same as any character in the list enclosed by square brackets.

If the expression matches, the input character appears. The system interprets all characters enclosed in square brackets as literal characters.

Example format: [ab]c

Input: ac - Matches? Yes - Output: ac

Input: bc - Matches? Yes - Output: bc

{ }

Denotes a character expression that matches the input character if the input character matches any character in the list enclosed by curly braces.

If the expression matches, the first character in the list (not the input character) is copied to output. All characters enclosed in curly braces are interpreted as literal characters.

Example format: {ab}c

Input: ac - Matches? Yes - Output: ac

Input: bc - Matches? Yes - Output: ac

?

This expression is optional. It is copied to output only if it appeared in input.

Example format: a?b

Input: ab - Matches? Yes - Output: ab

Input: b - Matches? Yes - Output: b

!

This expression is optional. It is copied to output regardless of whether or not it is matched.

Example format: a!b

Input: ab - Matches? Yes - Output: ab

Input: b - Matches? Yes - Output: ab

~

This expression is optional. It is copied to output regardless of whether or not it is matched.

Example format: a~b

Input: ab - Matches? Yes - Output: b

Input: b - Matches? Yes - Output: b

\

Example format: a\?b

Input: ab - Matches? No

Input: a?b - Matches? Yes - Output: a?b

( )

Groups expressions.

Example format: (abc)!99

Input: abc12 - Matches? Yes - Output: abc12

Input: 12 - Matches? Yes - Output: abc12

Click to jump to top of pageClick to jump to parent topicChanging Format Families

Custom field formats enable you to create your own format definitions using format notation and apply them to fields. These formats are organized into format families, which can include one or more unique formats.

To change format families:

  1. Select Tools, Miscellaneous Definitions, Field Formats to access the Format Families dialog box.

    The Format Families dialog box lists the defined format families and enables you to edit, delete, or create new families. You can also determine where each family is used in your applications and perform tests.

  2. To define a new format family, click the New button.

    The New Family dialog box appears.

  3. Enter a name for your new format family, and click OK.

    The Formats dialog box appears.

  4. In the Stored group box, enter a name and format.

    This defines how the format family is to be stored in the database and printed in reports.

  5. Enter the format notation that you want to use for storing the data in the Format field.

    Because a stored format cannot exist alone, a display format is always required. If you are adding to a family that has existing stored formats, a display name and format pair is optional. PeopleSoft Application Designer automatically places all of the current display formats in the new stored format. The display format default is the stored format that you can redefine later.

  6. In the Display group box, enter a name for the format.

    This defines how the format family appears online.

  7. Enter the format notation that you want to use for displaying the data in the Format field.

  8. Click the Add button to add the new format family, and then click OK when you are finished.

Max Display Length

Max Display Length enables you to override the default length that PeopleSoft Application Designer uses to determine the length of the edit field in custom format. This is useful when the displayed data is longer than the defined field length. For example, you might store data as 999 (field length in the database is 3 characters), but the display format of the data is 9-9-9 (5 characters).

PeopleSoft Application Designer uses the field length of 3, by default, for the edit field, which is not long enough to hold the 5 characters of the reformatted data. By entering five in the Max Display Len (maximum display length) field, the page uses 5 characters for the edit field without requiring that the field length in the database be 5 characters long.

Click to jump to top of pageClick to jump to parent topicEditing Formats

The Edit Formats dialog box presents a tree where you define the stored and display formats for a family and all options and attributes. This dialog box enables you to:

Click OK to save changes.

This section discusses how to:

Adding a New Stored Format

To add a new stored format:

  1. Open the format family to which you want to add a format.

  2. Select the format family name.

  3. Enter the stored and display names and format notation.

  4. Click Add.

    The new format appears in the tree below the existing formats.

Adding a New Display Format to a Stored Format

To add a new display format to a stored format:

  1. In the tree, select the stored format name to which you want to add a display format.

  2. In the Display group box, enter a new name and format notation.

  3. Click the Add button to add the display format.

    The new display format appears in the tree, attached to the stored format to which it belongs.

Updating a Display Format

To update a display format:

  1. Select the display format in the tree view.

    Note. Clicking the Update button applies only to display formats.

  2. Select a display format and enter the enabled fields.

  3. Click Update to update the display.

Deleting Stored Formats

To delete a stored format:

  1. Select the display format.

  2. Click the Delete button.

    PeopleSoft Application Designer displays a warning message indicating that deleting a display format might strand stored data. Select Yes to continue with the deletion.

Note. Stored formats cannot be deleted directly. Therefore, all display formats for a specific stored format must be deleted first. This ensures that data is not stranded in the database under an invalid format. When the last display format is deleted for a stored format, the stored format is automatically deleted.

Click to jump to top of pageClick to jump to parent topicUsing Family Options

Family options include:

Smart Punctuation

Smart punctuation automatically adds or removes punctuation characters to the data that users enter. For instance, if you enter a phone number like 8005551212 and press tab to exit the entry field, it is reformatted as 800-555-1212.

The system uses the punctuation characters specified in the punctuation list to strip out unwanted input and to modify the pattern specified before applying the pattern to the data.

Note. Smart punctuation and the standard set of characters are enabled by default.

You can modify the standard set of characters by selecting Custom in the options drop-down list box and typing another set of punctuation characters to use. Smart punctuation processes the characters defined in the standard set or custom set as follows:

Warning! Use meta characters and lexical characters in the punctuation sets with caution. Step two of the transformation process modifies the format pattern so that PeopleSoft Application Designer might sometimes convert special characters to literals (when they’re placed inside the left or right brace).

For example:

The display pattern is modified to 999[-]!999[-]!9999 because the "-" is in the punctuation set. The transformed data (8005551212) is matched against the display pattern of 999[-]!999[-]!9999. The match is successful, so the raw data is transformed into 800-555-1212.

Now, the input data has successfully matched the expected display format. The next step is to transform this data into what we want stored. In this case, the stored format has no dashes, as in 999999999.

The transformed data (800-555-1212) is stripped of the smart punctuation characters (8005551212), and the pattern of the stored format is 999999999. A match is made so that the system stores 8005551212 in the database.

Finally, the stored data is reformatted back to the display using 8005551212 and the display pattern. Output to the display is reformatted to 800-555-1212.

Make Uppercase

Custom format fields support mixed case. With uppercase activated, PeopleSoft Application Designer transforms the data into uppercase when you press tab to exit the field.

Click to jump to top of pageClick to jump to parent topicTesting Formats

The Test Format dialog box enables you to test a specific format or unformatted path for a family without having to build a page.

Stored Format Name and Display Format Name

Select a stored and display format pair to test.

DB->Display

Click this button to process the input field like data from the database. The system applies the stored format pattern to the input, then applies the display format pattern to the result. The output appears in the Transform field, which is the same output displayed for a field using this format.

Display->DB

Click this button to process the input field like user input from a page. The system applies the display format pattern to the input, then applies the stored format pattern to the result. The transformed output appears in the Transform field. This output reflects what is stored in the database if the operation is successful. On an actual page, when the user presses tab to exit the field, the system calls the DB->Display function to reformat the user input for the display.

Click to jump to parent topicUsing the Translate Table

This section provides an overview of the Translate table and discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding the Translate Table

The Translate table is a prompt table, which is similar to an all-purpose data dictionary, to store values for fields that don’t need individual prompt tables of their own. As a general rule, store field values in the Translate table if the field meets the following criteria:

Note. If the only values for a field are Y (yes) and N (no), you don’t need to enter them in the Translate table. The Translate table comes with a field for Y and N named PSYESNO. When you select the Yes/No Table Edit, the system automatically points to the PSYESNO field in the Translate table.

Example of When to Use the Translate Table

Consider the relative attributes of a department ID field and a gender field in a company database. In the following table, Department ID is not a good case for using the Translate table, because it requires its own prompt table. However, the Gender field is a good case for using the Translate Table:

Characteristic

Department ID

Gender

Field type.

Character.

Character.

Field length.

Longer than 3 characters.

1 character.

Field values.

Could be many values, 20 or more.

Two possible values.

Other related fields.

Related to other fields, like department manager.

Not related to other fields.

Requires maintenance.

Yes, users might have to add departments.

No, gender is a static value.

Translate Table Attributes

The system maintains the structure of the Translate table and you supply the data. This table contains the following fields:

Field Name

Description

FIELDNAME

Field name, such as ABSENCE_TYPE.

LANGUAGE_CD

Language code.

FIELDVALUE

Value for the field.

EFFDT

Effective date.

VERSION

Internal version number (system-maintained).

EFF_STATUS

Status—active or inactive.

XLATLONGNAME

Thirty-character description; used as a label on pages and reports.

XLATSHORTNAME

Ten-character description; used as a label on pages and reports.

LASTUPDDTTM

DateTime field showing the last time that a field was updated.

LASTUPDOPRID

User ID of the user who most recently updated the field.

If you must define more information about a field other than the preceding attributes, create a separate prompt table for the field instead of adding it to the Translate table.

When the user presses the prompt button to prompt for valid values in a field on a page, the system displays a list of the translate values for a field.

Each value in the Translate table has an effective date, and the date must be earlier than the effective dates of any rows that reference the value. The translate values delivered by PeopleSoft all have an effective date of January 1, 1900. Remember this if you add new translate values for a field.

Field Properties dialog box: Translate Values tab

Click to jump to top of pageClick to jump to parent topicAdding Values to the Translate Table

Access the Add Translate Table Value dialog box.

To add translate values to the Translate table:

  1. From a field definition window, select File, Definition Properties.

    You can also right-click and select Field Properties, or press alt+enter.

  2. From a record field definition window, right-click the field, and select View Translates from the pop-up menu.

  3. Select the Translate Values tab.

    The Translate Values dialog box displays existing values for the field and enables you to add, change, or delete values. In the Last Updated box, you see information—date, time, and user ID—about the last update for the selected translate value.

  4. Click Add to define a new value.

  5. Enter the field value, effective date, long name, and short name that your users enter on the page.

    Field Value

    Enter the translate value for the field. The system automatically sorts values in ascending order as you enter them. If you enter three translate values with field values A, T, and C, they appear in the Translate Values dialog box as A, C, T.

    Effective Date

    Enter the date on which you want this value to take effect. The default value is today’s date. If you want the effective date to predate all rows on your database, enter 01011900 (January 1, 1900).

    Note. The Add button only allows you to add a new, distinct translate value. To add a different effective date for the same translate value, select the translate value and use the Change button. As long as the effective date is changed, the system saves the modified entry.

    Inactive

    When a value for a field becomes obsolete, PeopleSoft recommends that you select the Inactive check box rather than deleting the value. Deactivating a value enables fields in the database that still contain the value to use the correct long and short names. If you delete an obsolete code from the Translate table and you still have records in the database that contain that value, you must change all of those values to active values.

    Long Name

    Enter up to 30 characters.

    Short Name

    Enter up to 10 characters. If you leave this field blank, the system automatically copies the first 10 characters of the long name into this field. PeopleSoft recommends that you use mixed case to improve readability.

Click to jump to top of pageClick to jump to parent topicChanging Translate Values

Sometimes the meaning of a translate value changes, but you still need to retain both values in the Translate table. For example, suppose that your PROFICIENCY field has a value of E (extremely high). You’ve been in production for five years, when upper management decides to change the meaning of the value to Exceptional, as of January 1, 1998.

You can accommodate this type of change by creating a second active entry for the same translate value. The first value is valid in the time range between the effective dates of January 1, 1990 and December 31, 1997. The second entry is valid from January 1, 1998 onward. You don’t want to delete the old entry because the database might contain rows that predate January 1, 1997 and contain this value. You might also maintain the old definition for historical reporting.

To change an existing translate value, select the value and click Change, or double-click the value. The system displays the Change Translate Table dialog box, in which you can make the necessary changes.

Click to jump to top of pageClick to jump to parent topicDeleting Translate Values

Use caution when deleting a translate value, unless you are removing an invalid value that was entered by mistake. To deactivate a value because it is no longer used or its meaning has changed, either change the long and short names, or change the status to Inactive. To delete the value, select it and click the Delete button. The row disappears.

Click to jump to top of pageClick to jump to parent topicSaving the Translate Table

After you have made all of your changes to the Translate table, you must save the entire field definition. There is no save option specifically for translate values. Click OK from the Field Properties dialog box, and then save the field definition by clicking the Save button or selecting File, Save.

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.