Using Lookups to Transform Values

In this simple example we will demonstrate how you can use the Lookup functionality in Data Synchronisation Studio to Transform/Convert a value via an Lookup Data source. Our source data contains the numbers 1-10 and we want to convert these into the names of the Numbers.

We need then to create a Transformation Data source and for this we're going to use Excel. We could use virtually anything we like, SQL, CRM, SharePoint, XML, CSV etc but Excel is ideal for this. This Excel file contains the From and To values for the Transformation, we need to create Column headers in the First row and then the Data below i.e.

Now we add the Lookup to the column (select the ID column, and press the 'Add Lookup' toolbar button) that we want to Transform, in our Example this is the ID column. First we select the Data source which for this example is Excel and browse to the Path of the Excel file.

Now we define the Lookup connection details, so we want to join the Lookup based on the Value in the From Column to the Value in the ID column in our source data. We also specify the Data Type as Int32 since this is a Number 1-10.

We then add the “Lookup1_To” column from the Data source to our Schema Map so that we return the Value of the Lookup Transformation.

There we have it a Transformation from an External Data source via Lookups.

This simple Transformation could be created in code via Dynamic Columns but this shows a no-code solution and has the added benefit that you can easily extend the transformation values by updating the Excel data source.