Everything you ever wanted to know about the 5 translation tables

I've recently been seeing quite a few questions posted asking how the 5 UI translation tables work (in-fact some questions suggesting it's 3 or 4 tables rather than 5). I can assure you, it is 5. So let's go through each of them, how they work, why they are the way they are and how you can use them. This will be quite a long post, so buckle up, have your popcorn and favourite beverage at the ready and let's begin.

Everything we're going to cover is also covered in our training course on NowLearning available here.

The tables

With the exception of the [sys_language] table, there are actually 5 tables that hold UI level translations:

• [sys_documentation] - this holds field labels for all languages (including the English)

• [sys_choice] - this holds drop-down choices for all languages (including the English)

• [sys_ui_message] - this holds the messages called in scripts being sent to the Message API 
  (ideally including the English)

• [sys_translated] - this holds "translated_field" values (not including the English)

• [sys_translated_text] - this holds "translated_text" values (also not including the English)

Each table has it's own specific structure and concept, so it's important to understand what they are in order to be able to fully leverage them correctly.

sys_documentation - i18n debugger ID - "GMLD"

The [sys_documentation] table is an accompanying table to the [sys_dictionary] table. So, [sys_dictionary] holds the field definitions such as the field type, attributes etc, where-as [sys_documentation] holds all of the field labels, including the very first one (such as the English) one.

One of the things to remember here is that, just because you have a field on your form for your extended table, doesn't necessarily mean it's defined on your table as it may be an extended field (e.g. from the [task] table). So, always be sure you check which table the field is defined on before adding a new label (or for that matter any "table overrides").

Looking at the [sys_documentation] table for the English field label of the "urgency" field on the [incident] table, we can see the following:

The [sys_documentation] table has a structure as follows:

• Table - this is the target table the label is for

• Element - this is the field on the target table the label is for

• Language - this can only be an active language available on the instance

• Display Name - similar to the label (for some legacy functionality)

• Label - what is shown in the UI where the field is displayed

• Plural - where a plural is required

• Help - for displaying next to a field in some UI's (e.g. when accessibility is enabled)

• Hint - for mouse-over tooltips to display a message where required

Identification

You can therefore imagine, that if we needed to find any fields we've made (that are not out of the box) that we need to translate, we could just build a filtered query to the [sys_documentation] table where the "element" starts with "u_" for any tables we know we've worked on.

This would therefore mean, that if we structured any spreadsheets (if that's what we need to work with) as per the table above, and we co-elesce on every field to ensure we don't cause any duplicates, then the import has the potential to be quite straight forward.

We should be able to have a result like this:

 ^ Note how each translation is a new record in the [sys_documentation] table, following the same principles as our English record.

Update-set storage

When a field label is created on an instance, it will be held in an update-set (where specific) as follows:

Note the "type" is a "field label" and note the name is structured as follows:

• [sys_documentation]_[target table]_[target field]

sys_choice - i18n debugger ID - "CHC"

The [sys_choice] table works in a very similar way to the [sys_documentation] table, in that where you define the English choices is also where you add the translations and you have to define the table and field for your choices. And just like [sys_documentation], we need to be mindful of where our choices are defined. For example, the OOtB choices for the "urgency" field are actually defined on the [task] table rather than the [incident] table:

There's a few more fields on [sys_choice] that we need to factor in, however the thought process is such that if we want the behaviour in another language to be exactly the same as that of English then we need to be mindful of the "value" (because of Business Rules checking for it), and the "sequence" in order to show the choice in the right order in the UI, as well as any "dependent_value".

The [sys_choice] table structure is as follows:

• Table - the target table of the choice field

• Element - the target field on the target table of the choice

• Language - the language of the record (restricted to only the active languages on the 
  instance)

• Value - the server-side value of the choice

• Label - what is to be displayed

• Hint - any hint text of the choice

• Dependent Value - for any dependent show/hide logic of that choice

• Inactive - where the choice is to be shown or not (this has to be consistent across all 
  languages)

• Sequence - the order in which to display the choice.

For those who aren't aware, choices are actually a children record to "Choice Sets" which are held in the [sys_choice_set] table. This is where all choice permutations for a choice field are defined, with the subsequent choices being held in the [sys_choice] table accordingly.

Identification

The sharp eyed amongst you, will also see the opportunity of how we can find our own choices for custom fields, because the "element" will start with "u_" and the same is true for any custom table names.

Once extracted if we co-elesce on all of the field columns in sys_choice we should be able to achieve something like this:
 

^ The above is an incomplete list, otherwise the screenshot would be too large for this post. And just like the [sys_documentation] table, we can see that the translations are essentially additional records per language following the same principles as our English source records.

Update-set storage

This is where things start to get a little different. Because of the relationship with the [sys_choice_set] records, which holds all of the choice permutations for all languages and their choices, we are actually updating this "Choice List" and as such it is this that gets recorded in the update-set like so:

Note how the "type" is called a "Choice list" and how the "name" is structured like:

• [sys_choice]_[target table]_[target field]

sys_ui_message - i18n debugger ID - "MSG"

The [sys_ui_message] holds entries (including English if created) based on a "key". The concept is fairly simple, e.g. if in a scripted message you need to display some text back in the UI, you would therefore call the Message API parsing the Key, then the API identifies that key for the session's language to show the corresponding message accordingly.

I'm not going to go through coding best-practices in this post, as I've done that in previous blog entries, but I will give you some typical examples of how to call the Message API:

Client-side:

g_form.addInfoMessage(getMessage('test message'));
g_form.addErrorMessage(getMessage('test message'));
g_form.showFieldMsg('field', getMessage('test message'));
alert(getMessage('test message'));
confirm(getMessage('test message'));

There are of course others, these are just examples to show how it works.

Server-side:

gs.addInfoMessage(gs.getMessage('test message'));
gs.addErrorMessage(gs.getMessage('test message'));

There are of course others, these are just examples to show how it works.

Within HTML (such as Portal widgets / UI pages):

// Client-side
${"test message"}

// Server-side
${gs.getMessage("test message")}

// other
${test message}

The exact style of the call can vary on the type of HTML, however it will be very similar to this.

The [sys_ui_message] table structure is as follows:

• Key - this is the identifier called via the API

• Language - this is the language of the presenting text

• Message - this is what is to be displayed in the UI (aka the translation)

It's important to note that the "key" is case-insensitive, and as such you need to be mindful to not create a duplicate of that key in a given language.

Identification

So, providing we've been following established coding standards and best-practices, and have been making a suitable English [sys_ui_message] record as we've been calling the API in our scripts, we should therefore be able to identify all of the English records that we've made for a simpler extraction.

If we co-elesce on all 3 columns in [sys_ui_message], we should be able to achieve something like this:

Update-set storage

When a message record is created on an instance, it will be captured in the associated update-set like this:

Note how the type is "message" and the name is structured like:

• [sys_ui_message]_[target record sys_id]

sys_translated - i18n debugger ID - "TRF"

This is where things are a bit different. Records for this table are determined by a field type, in this case a "translated_field" such as what we find on the [question] table for our variable's titles:

Imagine we want to translate our variable within our Catalog Item. In this example, lets say it's the "Operational Software" question in the "Developer Laptop (Mac)" catalog item. So, let's check where the source record is in the [question] table:

In order to show the translations in the UI, we need to follow the "question" field.

The thing about the [sys_translated] table, is that it doesn't hold the English values, as it essentially instantiates the translations as a way of replacing what is seen for multiple records of a given table. If you think about it, in the previous tables were were replacing one thing on a given a table (e.g. a field label, or a choice on a super specific field, or a specific message), where-as now we need to be able to work with many things on the same table.

The [sys_translated] table is structured as follows:

• Table - the target table for where the field is defined

• Display Name - translation of what you want to translate (used for some UI's)

• Element - the target field on the target table for the translation

• Language - the target language

• Value - the English source value, so the system knows what to replace when it comes across 
  it

• Label (translate) - the translation

Identification

So, if you identify the right types of entries you've been making and capturing the necessary information to potentially make a spreadsheet (if that's what you need to use), then you should be able to achieve the following:

Update-set storage

When an entry for [sys_translated] is created on an instance, within a defined update-set it will be captured as follows:

^ Note the type is "Translated Name / Field", and name is structured like:

• [sys_translated]_[target record sys_id]

Top tip - since Quebec, you can actually leverage the "Localization Framework" to massively simplify how to translate Catalog Items (which will also be relevent for our [sys_translated_text] example below.
Check the documentation here for more info.

sys_translated_text - i18n debugger ID - "TRT"

This is where things are a bit different again, because we need to be even more specific as to where our translations need to go.

As before, entries in this table are also governed by a specific field type "translated_text" or "translated_html", such as the "name" field on the [sc_cat_item] table:
 

^ Very important - the serializer attribute must be present otherwise the desired behaviour will not work. When making a new field or converting a string field to a 'translated_text" check that the Serializer is present prior to carrying out next steps - it should be auto added.

Like before, we need to check our English source, so if we navigate to the [sc_cat_item] to find our example Catalog item, we can see the following:

In this example, we're just looking to translate the "name" of our Catalog item.

The [sys_translated_text] table is structured as follows:

• Document - the sys_id of the English source record, in this example the sys_id of the 
  [sc_cat_item] record

• Field Name - the target field name

• Language - target language

• Table Name - target table

• Value - the translation

Identification

So, if we've been keeping a track of all of the fields we've been working on (such as any we've been making, because they would start with a "u_"), we should be able to create the following:

There's a few really neat little nuggets here. Because of the relationship in [sys_translated_text] via the "document" field (which is a "documentkey" type field), if we check the XML of our English [sc_cat_item] record, we can actually see the translations for this there:

^ The reason we have many more above the "name" Czech translation in my example, is because the "description" field is also a "translated_text" field.

So, this nugget about the XML is actually quite important because it tells us how the translations are captured in an update-set.

Update-set storage

With the XML info above in mind, the translations are actually stored in the payload of the definition of the English source record. In this example the Cat Item itself:

If we inspect the payload we will see the translations:

This is of course not to say you can't import via other means, you absolutely can. It's just to say that if you are ever trying to find where TRT records are in an update-set, it is like this.

As this is also part of a Catalog Item, this is where you can leverage the "Localization Framework" to simplify both the activity of identifying what Catalog Items to translate, how you translate them, but also how to migrate the translations between instances.

Common questions

We often get asked a lot of questions around translations, so here's a list of the most common with their respective answers:

• Q - "My customisations to ootb translations got over written after an upgrade, how do we stop this?"
  • Answer 1 - This happens under a few scenarios. Essentially the changes to ootb (out of the box) translations are tracked as entries in the [sys_update_xml] table, which are generated when you either manually change the records on PROD, or if you make the changes on a sub-Prod instance and bring those changes upstream via an update-set. Essentially, it's not advisable to do a spreadsheet import in DEV, then another spreadsheet import in TEST/UAT and then another spreadsheet import in PROD as this would not be tracked in the appropriate way. 
    • If you have made a lot of changes to ootb translations (for what-ever reason), it's best to change them on a sub-prod and ensure they are tracked in an update-set (using the information above) and migrate them that way.
  • Answer 2 - This can happen if you have a transform map set to run on these 5 translation tables at the same order as the old ones we used to use for the language packs of old (order 100) which is problematic because we have very specific conditional checks in the ootb transform maps we used to use. So, when the upgrade runs it would also run your custom transform maps on the ootb language packs. Therefore, it's best to put your custom transform maps at a later order to ensure there isn't a clash and to the previous point, it's also best to not have custom transform maps on any upstream instances.
• Q - "We need to change a very specific term"
  • Answer - As the UI translations are essentially data in the 5 tables mentioned in this blog post, you can search in the respective translation field, filtering on language to narrow down which specific records you would want to change.

Summary and Conclusions

Now that you know how the underpinnings of the 5 tables work, and all of the respective nuances, you should now be able to use them to their fullest should you need to. When you've used them a couple of times and got into good habits, it will become nearly second nature.

Feel free to comment below if you have any questions you'd like me to explore or if you've developed any tips worth sharing with the community.

And as usual, if you found this post helpful, please like, share and subscribe for more as it always helps