Need to find where a translation comes from? - check this

[Alex Coope - SN]

I was in a call with a Customer the other day and they were struggling to find where a particular translation was coming from. Reason being was that they were in their UAT phase of their implementation and so they wanted to make an edit.

So, let's delve into our options and how we can tackle this in the simplest possible way. Grab a coffee because this one is going to be fun!

The business problem

It's not uncommon for a translation to need a tweak or modification. Maybe a different term is better suited, or may be it's wrong, or maybe it doesn't read quite right due to UI constraints. Either way, it needs to be found and updated.

Ultimately, if we remember our training (if you haven't taken our course, I can highly recommend it, wink wink, nudge nudge) then we know that UI translations are essentially just data in the 5 translation tables (which I've blogged about in the past). If we know that they are just data then we have a bunch of options at our disposal.

We could go and hunt for the translation manually, problem is which table do we spend time on first? Because to be accurate we'd need to turn on the i18n debugger, then navigate through the UI to find what prefix it is to then know said translation table. This is fine for the adhoc one-off scenario, but what if we had a whole bunch of possibilities we needed to factor in?

What if, we needed to update a term everywhere? Or what-if, we needed to change a specific term for a specific reason?

An option

Not to say this is the best option, but it is an option. Because we know the UI translations are merely data in tables, it stands to reason that we could run a very simple query to find what we're after.

For example, here's a background script I've prototyped to get you started:

// this script is for finding instances of where a string is from
stringSearch('du ticket', 'fr');
// param 1 = term
// param 2 = language id

function stringSearch(strVal, lang) {
    try {
        if (lang == '' || lang == ' ' || lang == null) {
            gs.debug('No language ID supplied, please try again');
            return
        }

        // let's first check the sys_choice table
        gs.debug("\n*** sys_choice ***");
        var chcCheck = new GlideRecord('sys_choice');
        chcCheck.addQuery('label', 'CONTAINS', strVal);
        chcCheck.addQuery('language', lang);
        chcCheck.addActiveQuery();
        chcCheck.query();
        while (chcCheck.next()) {
            gs.debug("sys_choice | Translated Label | " + chcCheck.label + " | Table - " + chcCheck.name + " | " + chcCheck.element + " | " + chcCheck.value + " | " + chcCheck.language);
        }

        // let's check for field labels
        gs.debug("\n*** sys_documentation ***");
        var fldCheck = new GlideRecord('sys_documentation');
        fldCheck.addQuery('label', 'CONTAINS', strVal);
        fldCheck.addQuery('language', lang);
        fldCheck.query();
        while (fldCheck.next()) {
            gs.debug("sys_documentation | Translated Label | " + fldCheck.label + " | Table - " + fldCheck.name + " | " + fldCheck.element + " | " + fldCheck.plural + " | " + fldCheck.help + " | " + fldCheck.hint + " | " + fldCheck.language);
        }

        // let's check for scripted messages
        gs.debug("\n*** sys_ui_message ***");
        var msgCheck = new GlideRecord('sys_ui_message');
        msgCheck.addQuery('message', 'CONTAINS', strVal);
        msgCheck.addQuery('language', lang);
        msgCheck.query();
        while (msgCheck.next()) {
            gs.debug("sys_ui_message | Translated Message | " + _htmlStringer(msgCheck.message) + " | Key " + _htmlStringer(msgCheck.key) + " | " + msgCheck.language);
        }

        // let's check for sys_translated records
        gs.debug("\n*** sys_translated ***");
        var trfCheck = new GlideRecord('sys_translated');
        trfCheck.addQuery('label', 'CONTAINS', strVal);
        trfCheck.addQuery('language', lang);
        trfCheck.query();
        while (trfCheck.next()) {
            gs.debug("sys_translated | Translated Label | " + trfCheck.label + " | Table - " + trfCheck.name + " | " + trfCheck.element + " | " + trfCheck.value + " | " + trfCheck.language);
        }

        // let's check for sys_translated_text records
        gs.debug("\n*** sys_translated_text");
        var trtCheck = new GlideRecord('sys_translated_text');
        trtCheck.addQuery('value', 'CONTAINS', strVal);
        trtCheck.addQuery('language', lang);
        trtCheck.query();
        while (trtCheck.next()) {
            gs.debug("sys_translated_text | Translated Value | " + _htmlStringer(trtCheck.value) + " | Table - " + trtCheck.tablename + " | " + trtCheck.fieldname + " | " +trtCheck.documentkey+ " | " + trtCheck.language);
        }

    } catch (err) {
        gs.debug('Error in stringSearch - ' + err.name + ' - ' + err.message);
    }
}

function _htmlStringer(string) {
    try {
        // this is an untidy workaround to aligning script type text into a single line
        var htmlVal = string.toString().split('\r\n');
        if (htmlVal.length == 1) {
            htmlVal = string.toString().split('\r');
            if (htmlVal.length == 1) {
                htmlVal = string.toString().split('\n');
            }
        }
        var outputStr = '';
        for (var i = 0; i < htmlVal.length; i++) {
            outputStr += htmlVal[i];
        }
        return outputStr;
    } catch (err) {
        gs.debug('Error in _htmlStringer - ' + err.name + ' - ' + err.message);
    }
}

Sure it's not super efficient, or super amazing, but does it need to be? The objective is to find a term in any translated record. In my example I'm looking for "du ticket" in french. The output I can get is like this:

*** Script: [DEBUG] sys_choice | Translated Label | Résolution du ticket | Table - var__m_sys_hub_action_input_03345f3773500010a88f7e363cf6a7d9 | entity | incidentresolution | fr
*** Script: [DEBUG] sys_choice | Translated Label | Résolution du ticket | Table - var__m_sys_hub_action_input_91b31ff373500010a88f7e363cf6a7c7 | entity | incidentresolutions | fr

*** Script: [DEBUG] sys_documentation | Translated Label | Numéro du ticket | Table - csm_trip | ticket_number | Numéros des tickets | | | fr
*** Script: [DEBUG] sys_documentation | Translated Label | Numéro du ticket | Table - csm_trip_segment | ticket_number | Numéros des tickets | | | fr

*** Script: [DEBUG] sys_ui_message | Translated Message | {0} (Copié à partir du ticket initial : {1}) | Key {0}(Copied from original case: {1}) | fr
*** Script: [DEBUG] sys_ui_message | Translated Message | Entrez le motif de l'annulation du ticket | Key Enter the reason for canceling the case | fr

*** Script: [DEBUG] sys_translated | Translated Label | Champs du ticket de commande de travaux | Table - sp_widget | name | Work Order Ticket Fields | fr
*** Script: [DEBUG] sys_translated | Translated Label | En-tête du ticket HRM | Table - sp_widget | name | HRM Case Header | fr

*** Script: [DEBUG] sys_translated_text | Translated Value | Mettre à jour les notes de travail de l'incident de télécommunications après la mise à jour du ticket | Table - sys_hub_action_type_base | name | d4c5c3e75b9a10102dff5e92dc81c7c4 | fr
*** Script: [DEBUG] sys_translated_text | Translated Value | Catégorie du ticket RH. | Table - pa_breakdowns | description | 66943bf0d7323100b96d45a3ce6103ac | fr

Which means, in a tight pinch, we could either make a quick spreadsheet to review the outputs or because we have the record sys_id's we know exactly which record to update. Saving us time and energy to focus on what really matters.

Side note - depending on your term being searched, you might find there are many results so be very clear what your objective is.

What have we learned?

When we understand how the 5 tables work (and coupled with why they are the way they are) then it has the power to open up so many possibilities. Therefore, if you find yourself in a similar position of needing to find something, try out something like the above, or even try the script above to find a specific translation. And if you have any ideas to make it better, feel free to share in the comments,


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