Skip to content


Tech Tuesday : How to translate your data 5 different ways

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.

Leave a Comment


2023 Advanced SmartConnect Bootcamp: REST Web Services Integration
Accessing Your Historical Dynamics NAV Data in D365 Business Central - Part 1: Getting Started
2023 Migration from Dynamics GP to D365 Business Central Training
eOne Welcomes Incremental Group as a New eOne iPaaS Integration Partner
Employee Spotlight: Floyd Jones, Front End Developer


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder



Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SmartConnect SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.