Creating Field Definitions

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

• Create new field definitions.

• Use multiple labels.

• Open a field definition.

• View field properties.

• Change field definitions.

• Set custom field formats.

• Use the translate table.

Understanding 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 definition) might include a department ID field, department name field, location field, and so on.

For each field definition that you add to a record definition, 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.

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

Prerequisite

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

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

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

Specifying Attributes for a 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.

Specifying Character Field Attributes

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.

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

The maximum Large Object (LOB) size allowed by PeopleTools is 50Mb. The following database platforms support a smaller maximum (in bytes):

• DB2/LUW (32,700)

• DB2/zOS (31,744)

• Sybase (32,000)

Specifying Number Field Attributes

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.

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:

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

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.

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

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

Specifying DateTime Field Attributes

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.

See Also

%ClientDate

%Date

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

Specifying ImageReference Field Attributes

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.

Working With Additional Oracle Data Type Mappings

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:

Working With Additional Microsoft SQL Server 2005 Data Type Mappings

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:

Using Multiple Labels

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

• Add a new label.

• Delete a label.

• Select record field labels.

Understanding Multiple Labels

You can define multiple label pairs for any field. By adding multiple labels to a field definition, you can expand its usability and create a more intuitive user experience at the same time.

For example, here you see the Date field with five labels.

You could use this field in a student registration application and select the First Day of Classes label. You could also use this field in an application that tracks employee personal information, but select the Date of Birth label. In each case, you use the same field definition but you vary the label to reflect what the user sees and should understand about its context.

The underlying data structure for the field does not change when you change its label. At the database level, the column name in the table is DATE. You are merely manipulating the label to accommodate the context in which you use the field.

The system displays multiple labels 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.

Adding 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 defining the multiple labels, you can select appropriate labels when designing a record or page. You also can 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 multiple labels, the first label ID is the default label . You can change the default label by selecting the Def check box for a different row. Only one label can be the default.

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

Deleting 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. You cannot delete labels that are currently in use. 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.

Selecting 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. The default selection is *** Use Default Label ***. The record field label changes automatically whenever you change the default label in the field definition.

Opening an Existing Field Definition

You can open a field definition in several ways:

• Select File, Open from the menu.

  From this dialog box, you can view all available fields in the database. You can open all fields of a certain type or from a specific project, or you can open a specific field or set of fields.

• Clone an existing definition.

  To create a field definition with attributes similar to an existing one:

  1. Open an existing field definition.

  2. Select File, Save As.

  3. Enter a new name and click the OK button.

• Open a field from the project workspace.

  Double-click a field from the project workspace to open a field definition window in the definition workspace.

• Open a field from the record.

  Open an existing record definition. When the record definition window opens, you can:

  1. Select a field.

  2. Right-click the field.

  3. Select View Definition from the pop-up menu.

Viewing Field Properties

You can view field properties in several ways:

• While in the field definition workspace, click the Properties button.

• Select File, Definition Properties.

• While in a record definition:

  1. Select the field.

  2. Right-click the field name.

  3. Select View Field Properties from the pop-up menu.

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

Changing Field Definitions

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

• Change field types.

• Rename field definitions.

• Delete field definitions.

• Print field definitions.

Understanding 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 is not appropriate for every occurrence of the field, consider defining a new field instead.

If a change is still needed, you should 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 are 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 projects, 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 of a field definition requires that you modify 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 of a field definition and 30 record definitions contain the same field name, then you must create or alter 30 records.

Changing 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 are created in the database. While you can convert some field types, the results vary depending on your database platform.

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

See Altering Tables.

Renaming 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 are not 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 enter a 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 in 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 already used SQL Create to create the underlying tables for the record definitions that contain the field that you renamed, you must recreate 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.

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

Printing Field Definitions

If you plan to make changes to your field definitions, you should view 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 method 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.

Setting Custom Field Formats

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

• Change format families.

• Edit formats.

• Use family options.

• Test formats.

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

Several formatting options are available 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.

Understanding Format Notation

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

• Lexical characters delimit character expressions and designate interpretation rules.

• Literal characters represent only themselves.

• Meta characters represent a class of characters.

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

Changing 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 information 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 information 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 (maximum 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 setting 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 five characters of the reformatted data. By entering 5 in the Max Display Len (maximum display length) field, the page uses five characters for the edit field without requiring that the field length in the database be five characters long.

Editing 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:

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

Click OK to save changes.

This section discusses how to:

• Add a new stored format.

• Add a new display format to a stored format.

• Update a display format.

• Delete stored formats.

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

Using Family Options

Family options include:

• Smart punctuation

• Make uppercase

Smart Punctuation

Smart punctuation automatically adds or removes punctuation characters to the data that users enter. For instance, if you enter a phone number, 8005551212 and press the tab key to exit the entry field, the system reformats the value 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:

• The input has all characters in the punctuation set stripped out—similar to ignoring white spaces.

• The pattern has all characters in the punctuation set expanded if they are not found in the input.

  To do this, enclose each punctuation character in the punctuation set with "[ puncchar]!".

• The formatter runs on the modified data to apply the modified pattern.

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

For example:

• Input: (800)555-1212.

• Display pattern: 999-999-9999.

• Punctuation set: ()-.

• Stored pattern: 999999999.

• The "(" , ")", and "-"are stripped out of the input because they appear in the punctuation set.

  Now the input looks as follows: 8005551212.

The display pattern is modified to 999[-]!999[-]!9999 because "-" 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 the tab key to exit the field.

Testing 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, and 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, and 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 the tab key to exit the field, the system calls the DB->Display function to reformat the user input for the display.

Using the Translate Table

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

• Add values to the Translate table.

• Change translate values.

• Delete translate values.

• Save the Translate table.

Understanding the Translate Table

The Translate table is a prompt table, similar to an all-purpose data dictionary, that stores values for fields that do not need individual prompt tables. As a general rule, store field values in the Translate table if the field meets the following criteria:

• Field type is Character.

• Field length is 1 to 4 characters.

• Field values consist of a relatively small, static set of values that are not maintained by the user.

• No other fields relate to this field.

Note. If the only values for a field are Y (yes) and N (no), you do not need to enter them in the Translate table. The Translate table comes with a field for Y and N named PSYESNO. When you select 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:

Translate Table Attributes

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

If you must define more information about a field 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. Choosing a different value for translate values that you add enables you to quickly differentiate delivered values from your values.

Adding 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, you should 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, then 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. Use mixed case to improve readability. Note. The recommended character length of translate short name values is six. You can configure PeopleSoft Application Designer to alert you if the recommended length is exceeded. See Setting PeopleTools Options.

Changing 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 have 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 do not want to delete the existing entry because the database might contain rows that predate January 1, 1997, and contain this value. You might also maintain the existing 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.

Deleting 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 system removes the row.

Saving the Translate Table

After you have made all of your changes to the Translate table, you must save the entire field definition. The system has 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. A special Translates Only security access level is available for fields; it allows authorized users to update translate values but not other field attributes.