Multi-language record content translation

Semarchy xDM provides Data UI label translation out of the box (official documentation here), but translating record content requires model design efforts.

Depending on the number of languages that you'll need to manage, if these languages are set once and for all or need to be added dynamically, you will need to use one of those model types :

1. Entity level attributes dedicated to translation (i.e DefaultLabel, EnglishLabel, FrenchLabel, etc.). 

   This is the easiest and most efficient way to deal with translations, always prefer this solution when you know the languages in advance and there is no business requirement to dynamically add new languages or new attributes.

2. Dedicated translation entities

   This is the solution that we will explain in details in this article. Choose this solution when you need more flexibility for the end user to be able to add new translation languages and attributes on the fly.

Step-by-step guide

The following instructions are based on our current Product Retail Demo model.

1. Defining 2 new entities is enough to cover multi-language translation of any attributes (a language entity and a many-to-many entity)
2. 
3. The Language entity contains a list of language with the Code in iso format (en-US, fr-FR, etc.), same as your language settings in xDM user profile
4. 
5. The ProductLanguage entity contains all translated attributes, in our case only Name and Description are translated
6. It is populated with one entry per product/language (here is an example with ProductTranslation records displayed in a product tab)
7. 
8. Then, to be able to automatically display the correct language in form views we will require the following PL/SQL function
9. PL/SQL function to retrieve translated product fields value

   create or replace 
   function GET_PRODUCT_TRANSLATION (
     p_product_id number,
     p_lang varchar2,
     p_attr_semql_name varchar2
   )
   return varchar2
   as
    v_translated_label varchar2 (4000);
   Begin
     select case p_attr_semql_name
       when 'Name' then name 
       when 'Description' then description
       end
     into v_translated_label
     from gd_product_translation 
     where f_product = p_product_id
       and f_lang = p_lang;
     
     return v_translated_label;
     
     exception when no_data_found then 
       return null;
   end;

10. The function needs to map SemQL attribute names to physical names (Name => name), which will allow you to refer to SemQL names when calling the function from the SemQL editor
11. To call it within form view attributes, first declare the PL/SQL function in the workbench and then use the following syntax (which defaults to the stored value in case there is no translation for the desired language)
12. SemQL form attribute value

    COALESCE(GET_PRODUCT_TRANSLATION(ID, :V_USER_LANGUAGE, 'Name'), Name)

13. 
14. Use the same syntax in collections and display cards to make sure these values are translated everywhere
15. Deploy your application, open it and edit your language settings to check the different translations (for testing purposes the current language is displayed in the form view)
16. 
17. Note that only values are translated here, since labels (Product, Brand, etc.) can easily be configured with the translation bundles feature (see link at the very beginning of the article)