Custom Translations – the Easy Way

In many customer projects and products, I have built solutions for managing translations in Microsoft Dynamics 365 / CDS / CRM.
Sure, the metadata can be translated to show labels etc in the language of the user, but having a custom solution for translations ensures localized content for texts that are shown to the user from code or automation.

In customer projects this approach makes it easier to manage text and messages shown to users, and as an ISV it is a great way to let the customers customize the phrasing of standard messages used in out products.

I have created helper methods in C# and javascript to get the localized translation of a specific text, identified by its TxtId in the model above. I have created Custom Workflow Activities and other types of code gadgets to make the translation model more accessible to classic workflows, Power Automate flows, and a number of other types of automation.

Querying translations

All these helper features in one way or another needs to find out what language for which to get the translations. Most common scenario is to read the Language Code Identifier (LCID) from the user settings, for the current user. This could then be used in the query to retrieve the translation, or even by joining on the “imaginary relationship” of the LCID.

Sample query to return all translations in the language selected by the current user:

<fetch>
  <entity name='jr_translation' >
    <attribute name='jr_languageid' />
    <attribute name='jr_textid' />
    <attribute name='jr_translation' />
    <filter>
      <condition attribute='statecode' operator='eq' value='0' />
    </filter>
    <link-entity name='jr_language' from='jr_languageid' to='jr_languageid' >
      <link-entity name='usersettings' from='uilanguageid' to='jr_lcid' >
        <filter>
          <condition attribute='systemuserid' operator='eq-userid' />
        </filter>
      </link-entity>
    </link-entity>
  </entity>
</fetch>

The query above reads Translations, joins with Language, joins over the LcId field to the User Settings field UI Language Id, and filters this on the current user.

eq-userlanguage

A few weeks ago the @FetchXMLBuilder account on Twitter asked about a condition operator I had never heard about…

This got me thinking…. Have I been doing my translation solutions unnecessarily complext all these years?

I guess I have!

Adding the condition operator eq-userlanguage will filter any whole number field on the LCID of the current user 🙂

This would simplify my query to this:

<fetch>
  <entity name='jr_translation' >
    <attribute name='jr_languageid' />
    <attribute name='jr_textid' />
    <attribute name='jr_translation' />
    <filter>
      <condition attribute='statecode' operator='eq' value='0' />
    </filter>
    <link-entity name='jr_language' from='jr_languageid' to='jr_languageid' >
      <filter>
        <condition attribute='crmk_lcid' operator='eq-userlanguage' />
      </filter>
    </link-entity>
  </entity>
</fetch>

Using in views

You can use this condition operator in views too, if you use FetchXML Builder to alter the view query. Unfortunately it is not currently available in Advanced Find or in the view designer of the Maker Portal.

Sample standard view for translations:

I created a variation of this view, and used FetchXML Builder to add the condition according to the FetchXML demonstrated above:

This view now only shows translations in Swedish.

Simply changing my user language to English will now of course show the same translations in English:

Advanced Find not this advanced

Trying to look at this view in Advanced Find, makes it painfully obvious this condition operator is not supported by the UI.

Note that the query now just thinks the condition operator was “Equals”, and trying to execute the query shows error message “Provide a valid value for LCID.“

Also note that it is only the Advanced Find UI that does not support this operator – using it in queries and views is fully supported as it is documented in the FetchXML schema.

I guess this is not a complete game changer or world savior – but it sure makes it a bit easier to work with custom translations in Microsoft Common Data Service! 😊

Bonus! In the next release of FetchXML Builder (slated for November 2020), this condition operator will be fully supported to make it even easier to create these queries!