Understanding Related Language Tables

PeopleTools can store multiple translations of application data and PeopleTools objects in a single database. Each PeopleSoft database has a single base language. The base language of a new PeopleSoft database upon installation is always English, but can be changed by an administrator. The base language should be the language most commonly used by application users, and is the language in which data is stored in the core PeopleSoft tables known as base language tables.

All PeopleTools objects, such as pages, fields and queries, can be maintained in multiple languages. Descriptions of application data elements, such as departments, locations and job codes, can also be maintained in multiple languages.

The key to maintaining this data in multiple languages is the use of special tables known as related language tables. A related language table stores descriptions and other language-sensitive elements in all languages other than the base language of the database. In this way, while any table in the database can store data in the base language of that database, only tables that have related language tables can maintain the same data in multiple languages simultaneously. For example, it is unlikely that you will want to maintain the descriptions of your general ledger journal lines in multiple languages − the sheer volume of the journal lines in most systems would preclude any effort to maintain translations of their descriptions. The cost of hiring a translator to translate each journal line would be prohibitive, and in most cases only the person entering the journal line, and possibly their supervisor, would be likely to want to view it again. However for frequently used values, such as a chart of accounts, many users across your entire organization would often need to refer to this data, and so you would most likely want to maintain the descriptions of each ChartField entry in each language spoken by your users.

In this case, you would not need a related language table for your Journal Lines table, as you would be maintaining Journal Line descriptions in a single language only, and this would be in the base table, but you would need a related language table for each of your ChartField tables.

When the system displays a language-sensitive field value, it retrieves the text from either the base table or the related language table, depending on the following:

• The current language preference.

• Whether any translated rows for the field are found in the related language table.

The language preference applies to both the session language and the data language and can be different for each.

If the current language preference is the system's base language, the text is retrieved from the base table. If the language preference is a non-base language, then the system looks for a translation of the text in the related language table. If it finds a translation, it displays the translated text; if no translation exists, the system uses the text in the base table. This enables developers to selectively translate portions of the system, while keeping the system fully functional at all times, even if not all rows have been translated.

Image: System display of base and non-base language text

This diagram illustrates the flow of execution of translated language text

Synchronization of related language tables with their base tables is automatically maintained by PeopleTools. When you translate a language-sensitive field, the system adds a new row to the related language table. When you delete a row from a base table, any child rows in the related language table are deleted. The primary responsibility of the application developer in the language architecture is to define and maintain the related language tables.

Related language tables store multiple translations of language-sensitive fields from an associated base table. For example, the description (DESCR), short description (DESCRSHORT), and long description (DESCRLONG) fields are commonly language sensitive.

Each table in your database that is not a related language table is, by definition, a base language table. The Message Catalog table, PSMSGCATDEFN, is one example of a base table. Note that it has a DESCRLONG field—which is typically language-sensitive—as well as MESSAGE_TEXT, which is also language-sensitive.

Image: PSMSGCATDEFN table

This diagram illustrates related language table structure

PeopleSoft has therefore created a new record definition, PSMSGCATLANG, to act as the related language table for PSMSGCATDEFN. Related language records are defined in Application Designer just like any other record definitions. However, as they often contain many of the same fields as their base language table, it is often quicker to create the related language table by cloning the base table. To clone a base table, open the base language table and the use the Save As option on the File menu and enter a name for the new record. Once cloned, any fields not required in the related language record can be deleted from the new record.

A related language table must:

• Share all key record fields with its base table.

• Have an additional key record field, which must be LANGUAGE_CD.

• Have language-sensitive fields matching those in the base table. These are typically DESCR, DESCRSHORT or DESCRLONG; or LONGNAME or SHORTNAME.

• Not contain any fields that are not present on the base table, except the LANGUAGE_CD key field.

• Not contain any non-key, non-language-sensitive fields from the base table.

The related language table, PSMSGCATLANG, meets all of these requirements. Note that both language-sensitive fields, DESCRLONG and MESSAGE_TEXT, appear in PSMSGCATLANG.

Image: PSMSGCATLANG table

This diagram illustrates related language table structure displaying DESCRLONG and MESSAGE_TEXT

The base table must be explicitly associated with its related language table.

To associate a base table with its related language table:

1. Open the Record Properties dialog box for the base table record.

2. Click the Use tab.

3. Under Record Relationships, in the Related Language Record field enter the name of the related language table.

   Note: There is a one-to-one relationship between the base record and the related language record. Also, the record type of the base record and the related language record should be the same.

4. Click OK.

Base tables store language-sensitive descriptions in the database's base language. Related language tables store translations of these descriptions in non-base languages. For each row in the base table, there can be zero rows or one row in the related language table for each non-base language.

For example, the following table shows a row of data from the PSMSGCATDEFN table.

The following table shows child rows of data from the PSMSGCATLANG table. For each LANGUAGE_CD, there is a row with appropriate translations of the language-sensitive fields, DESCRLONG and MESSAGE_TEXT, from the parent row.

Related language tables are the primary means of storing language translations in the PeopleSoft system. They store translations of descriptive text for data, such as a standard list of expense categories, and system objects, such as descriptions of fields and records.

Related language tables that store descriptive text for data are generally associated with edit tables that store lists of values that are used for validation and prompting. When a page field prompts against a table that has an associated related language table, and, if the user’s language preference is a non-base language, the description fields that have been translated into the current language appear in place of the base language descriptive text.

For example, the following Message Catalog page appears when the user’s language preference is Spanish (ESP). The Texto Mensaje (MESSAGE_TEXT) field and the Explicación (DESCRLONG) field present translations from the PSMSGCATLANG related language table.

Image: Page with translated descriptive text (Message Catalog page)

This is an example of the Message Catalog page in Spanish

Many PeopleTools object definitions, including fields, records, and components, also have associated related language tables. This makes maintenance of multiple language PeopleTools objects, and therefore the system user interface, relatively straightforward. The system uses a single mechanism for maintaining translated data and for maintaining the system objects themselves. Field translation is particularly powerful because translated field descriptions appear on search pages and on pages where the descriptive text for the field is used as the field label.

Image: Translation of fields in ADDRESS2_SBP

This is an example of the fields on the ADDRESS2_SBP subpage in Spanish

Because the fields are translated, the translated versions of field labels appear when a user whose language preference is Spanish accesses a page that shows the long name or short name of any of these fields. No additional development work on the part of the developer is required.

Of course, whenever the base labels change, you must also update the translations of the object names.