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!

4 thoughts on “Dealing with Multi-Language Lookups

  1. Hi Salim,

    Thanks for sharing the options for multi-language lookups. One client I worked with had a requirement to do multi-language. The base language was English and when we onboarded the users in France we were going to add French as the first additional language.

    This client used a lot of “auxilliary” entities for lookups.

    While I moved to a different project before it got implemented, the design we were working on was to make the lookup entities hierarchical via a self-referential 1:N relationship.

    The idea was to make the English record be the parent. Each country we brought on board could create a child in their own language (parented by the English record). We could use Business Unit security to restrict a country’s users to only the records in the native language of the country.

    Let’s say the entity was “color”. We’d have “red” as the parent and “rouge” as a child. The people in France would populate the “rouge” record.

    For the people in the US, we could add an additional lookup to the form to show “red” as well (possibly hiding the “rouge” for US users). Even if we didn’t add the second lookup to the form, the US users could show the parent English record in views.

    This design assumes that each country is operating in its own language with only a small set of users needing to look at records from other countries (i.e. US management reviewing the other countries’ data, but the US users sticking to US data). Sounds like the Canadian public sector’s requirement for a “fully bilingual system” is a bit more challenging.

    Anyway, it’s cool to see the creative ways of dealing with multi-language hurdles.

    • Aron, thanks for sharing you solution. It is very creative. The roadblock that I see in a lot of scenarios is the inability to isolate users records based their country/language. I agree with you, it’s fun and fascinating to see how creative solutions can get to work around these issues!

  2. I did use Option 3 recently and there some particularities to be aware of:

    First, to create an option set value in a plugin, it has to be registered in pre-validation.

    Second, when you create an option set value, the SDK triggers a publish automatically. That was a problem to us because we wanted to load data into the entity, with the plugin on, to fill the option set. The performance was greatly affected. We found no solution other than take a break and get a coffee.

    Third, when you update an option set value there is no automated publish. You have to publish manually to see your changes. Depending on your needs it might not be a bad thing. You can still automate a publish once day.

    Fourth, this one is bug. You’ll probably want to use the option set in a view. Which is fine. But after we configured a sort on that column, and went to page two of that view, we got a big red error message. We did not find any fix for it. This occured with CRM 2016 on-premise.

    In the end, my opinion on each options. Option 1 will work in under an hour. Option 2 will affect performance over time. Option 3 is the best choice even though it requires tinkering. Option 4 is to avoid. Web resources are complex to maintain and will fail you whenever they can.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s