Dealing with Multi-Language Lookups

Very often, CRM entities are used as reference data tables, for example to keep a list of countries, states or provinces or other business/industry specific data. For some businesses I have seen entities to keep a list of distributors, list of business roles, regions to only name a few. When used that way, CRM entities provide a lot of great features that cannot easily be met with option sets such as the ability to manage large reference tables, lookup search, lookup filtering, ease of adding/editing/modifying data by power users without a deployment.

One of the issues with using CRM entities for reference data is that they is no concept of multi-language lookup in the Dynamics 365 / CRM platform. Lookups will always display the value of the primary field by default. This can cause an issue in places where you must have a fully multilingual application. In this article, I provide a few possible solutions to solve this issue.

As an example, we’ll use the context of a task for which we need to track its type. The list of the available task activity types is stored as records in an entity called “Task Activity“. The Task entity has a lookup to the Task Activity entity. The information needs to be stored in English and French


1 – Task Form with Lookup to Task Activity


2 – List of Task Activities

Resolution Option 1 – Concatenate multiple languages in one field with a separator

You will be disappointed, this is not a fancy solution. In the Task Activity entity, we have one field for the name in both languages and use concatenate both field values in the primary field using a workflow or plugin.

  • Name English (Single line of text – 100)
  • Name French (Single line of text – 100)
  • Name (Single line of text – 203) – read only for users, populated with “Name English | French Name”


3 – Task Activity Form

This is the most common approach that I have seen when the number of languages is small (2 languages). This has a disadvantage of sometimes creating long name values that are not fully visible in the views and on the forms, but it’s cheap and you keep the ability to search using lookup, and display the columns in French or English the views if you need to.

Resolution Option 2 – Plugins on Retrieve & Retrieve Multiple

This solution is a little more interesting, but risky. In the Task Activity, we still have one field for the name in both languages and we still concatenate both field values in the primary field.

  • Name English (Single line of text – 100)
  • Name French (Single line of text – 100)
  • Name (Single line of text – 203) – read only for users, populated with “Name English | French Name”

The principle is to write plugins on the Retrieve and Retrieve Multiple events of the Task Activity. In both of these plugins, you need to retrieve the connected user’s language (query the user settings table), and then replace the text being returned in the Name field by the value in the user’s language. This value can be obtained by querying the task activity record and retrieving the name in French or English, or simply splitting the Name field (primary field) with the separator and return the part in the desired user’s language. One plugin will handle the lookup column in list views (Retrieve Multiple), and the other will handle the form views (Retrieve).

Generally, it is not recommended to write plugins on the Retrieve and Retrieve Multiple events for performance reason. If the operations executed in those plugins are simple and optimized, it might be a viable solution. This is a solution that can scale well if you are dealing with more than 2 languages because in all cases, users see only the value in their selected language and the multiple values are transparent to them.

Stay tuned, I have an upcoming post where I provide some metrics about the impact of a plugin on Retrieve and Retrieve Multiple on performance.

Resolution Option 3 – Automated mapping of Option Set with Reference Entity Records

This is a bit of a complex solution, by far the fanciest. The idea is to use an option set instead of a lookup to reference the task activities, but the option set values will be “controlled” with records from the Task Activity CRM entity. It goes like this:

  1. Create a global option set named Task Activity
  2. Create your Task Activity entity with a primary “Name” field. Put the name of task activities in the primary language (language of the CRM org)
  3. When a record is created in the Task Activity entity, use a plugin to create an option set value in the global option set
  4. When a record is updated in the Task Activity entity, use a plugin to update the corresponding option set value in the global option set
  5. When a Task Activity record is deleted/deactivated, use a plugin to update the corresponding global option set value by putting brackets around the name for example, and also pushing the value to the bottom of the option set list
  6. You can then get the CRM Translation file and get the Task Activities values translated as part of the global solution.


4 – Records to Option Set Value mapping

The outcome is that for entities that need to capture the task activity information, there will be an option set field as opposed to the lookup field:


5 – Task Form with Option Set

While this has low impact on performance and leverages the out of the box language-aware option sets, it requires a serious time investment to define the development framework for each entity that required this mechanism to be implemented. It also requires a translator to update the CRM translation on a regular basis (every time there is a deployment). This is a fancy solution that requires a lot of coding and maintenance. In addition to that, you lose the ability to search and filter the content easily like you would do with lookups. You should make sure yours lists are not very long if you don’t want to end up with Option Set lists that are very long which will result in poor user experience.

I have rarely seen companies making such large investments to circumvent the lack of multi-language lookup in CRM. This is usually seen when there are laws that force you to have a system running fully in multiple languages.

Resolution Option 4 – Custom Screen for Lookup views & search

This is another fancy one for which I unfortunately don’t have any screenshot. We want to leverage option sets to “overwrite” lookup values and selection process with the following steps:

  1. Create a set of standardized Web Resources for Lookup Display and lookup value selection
  2. Display the web resources on the CRM Forms and hide the lookup controls
  3. The web resources will have built-in logic to display the value in the language of the current user, as well as a mechanism to allow searches (could be auto-complete based)

Writing a standard web resource control for that purpose is relatively simple. However, you might have additional work to do if you want to take advantage of filtering based on other fields, or custom filters. Also, this solves the issue on the form in the sense that you will see the values in the right language on the form, but for list views, reports etc. the problem will still exist so you’ll need to find another solution there.

Closing Thoughts

As you can see there is no perfect solution. Each organization has to decide the level of investment and risk they want to take to make sure they have multi-language option sets. Living in Canada where we have two official languages, this is a challenge that we often see in public sector implementations because having fully bilingual system is mandated by law. There are very few countries where this is the case (which is probably why Microsoft has not made investments in this area). Most private sector companies will usually impose a primary language for the entire organization.

Hope this helps!

Advertisements