There are many times when you are integrating data from multiple systems that you need to translate the values to what the new system expects. Today I will discuss five ways to handle this inside of SmartConnect.
We will start with a set of Leads that exist in Excel and we need to bring them into Dynamics 365/CRM. The key area we will focus on in the following data is the Source which indicates where this lead originated. In the table below we have text like Trade Show, Web and Other to indicate the source but our CRM system actually expects the numerical value. We will use this data for the remainder of the article:
The first and simplest solution is to use the built-in translation table. This allows you to setup the values one time and then reuse the information across all integrations as desired. Another nice feature is that you have the ability to import into the table using an Excel Sheet. Below you can see two images, one for the setup and another on how you use this inside of a map.
Now we will move from the simplest to the solution with the potential for the most complexity, the Calculated Field. I say this because I have seen very simple If/Then translations and I have also seen incredibly complex calculations to handle the translations. Below you can see an example of a VB.NET script to translate the values.
Another way to translate data is to use the built-in Lookup functionality. We have several types (ie SQL Lookups, CRM Lookups, Page Lookups, Entity Lookups, etc) that allow you to pass in the data that is known to get a value that the other system is expecting. The SQL Lookup in the diagram below could point to any system to retrieve the needed information.
The next two options involve affecting the data source at the beginning of the process instead of in the transformation stage.
The first approach in this method is changing your data source from being specific (ie CSV, SQL, Web Service) to using that same source inside of a Multi-Data source and adding a link to another system. This allows us to pull the data from another source and allows the translation of this information in the data source before the mapping process occurs. The example below
Finally we can add a CASE or IIF statement to our data source. Much like the Calculated Field, adding a CASE or IIF statement in your data source can do the translation up front. If you use a Multi-Data source like explained above you can run a CASE statement on any type of data source as it basically becomes a Virtual SQL table. Below I have a simple example of converting the text values to their numerical equivalents in the destination system.
In summary, there are many ways Translation of data can be accomplished and SmartConnect gives you the flexibility to approach this task using the toolset you may be most comfortable using.