EONE BLOG

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




RECENT POSTS


Tech Tuesday: Accessing the Real-time Request Log in SmartConnect.com
Tech Thursday: New Field Types in Extender
Integrations from Excel to Microsoft CRM - A Match Made in Heaven
eOne GPUG Summit Sessions
SmartConnect 2018 now compatible with Microsoft Business Central October Release

POPULAR POSTS


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

CATEGORIES

ARCHIVES

open all | close all

TAGS

CRM Demo Dynamics Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight ERP Events Excel Excel Report Builder Extender Extender Enterprise Flexicoder GP GPUG integration integration manager Meet the Team Microsoft Microsoft dynamics crm Microsoft Dynamics GP MSDYNGP Office Relationships partners release SalesForce SalesForce.com scripting SmartConnect SmartList SmartList Builder SmartList Designer SmartView SQL Support Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.