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.

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:

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

Note: Null values are not supported as effective dates.

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 or Control

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

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.

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.

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.