Transforming Data using Calculated Columns

Tutorials on using Calculated Columns to transform data during integration

A key feature of any integration solution is the ability to create new data by transforming existing information.

DS3 has the most advanced data transformation features of any integration product. Core to any integration system is usability and DS3 has modelled its key transformation technology 'Calculated Columns' on the formula field of a spreadsheet. The new columns can be used in a mapping like any other column.

To create a new Calculated Column

Connect to a data source and then select the Calculated Column node in the columns list, then click the 'fx' or calculated column button:

Calculated Columns

This will show you the Calculated Column Dialog Box:

Dialog box

Static / Constant Values

In many cases you might want to create a fixed or static value such as the state of an entity. In this case just give the column a name, a type of int32 and then assign it a value of 0. This will create a new column called statecode.

In other cases might just be setting the statecode and statuscode to active:

Calculated Column

See later on in the tutorial how to use a case statement to decide on the state and status codes.

Calculated Column

Dynamics CRM Data Types

It is always a good idea for the source columns and target columns to match Data Types. DS3 will actually transform data types from one type to another but the comparison might not make sense especially with date and number types such as decimal, integer etc.

String Formatting

DS3 has extensive string formatting but the following are popular functions.

Explicitly Formatting using the FORMAT function:

For example if you wanted to format the firstname columns and lastname columns into lastname, firstname

FORMAT("{0}, {1}",lastname,firstname)

In this case the first parameter is a string indicating that it should replace the first {0} with the first string that is passed and {1} with the next.

Applying Logic using a CASE statement

Often you need to choose a value based on a source column. In this case we can convert the salutation for Doctor to a smaller set of choices.

//Convert different Doctor to Dr.

CASE(Salutation,Salutation, //This is the default if none of the values are found

    WHEN("Doctor","Dr."), 

    WHEN("Dr","Dr."), 

    WHEN("MD","Dr."))

Calculated Columns support comments

It is always a good idea to comment your calculated columns as you or someone else might need to extend their logic later.

Applying Logic using an IF statement

The IF statement is modelled on the Excel functional equivalent IF

//If the firstname is empty replace with (unknown)
IF(ISNULLOREMPTY(firstname),"(unknown)",firstname)

A complete and advanced string manipulation

To show the extent it is possible to manipulate and manage a complex transformation we are going to use the following example requirement: A telephone number formatting function will not only handle the leading zeros, but if there are exactly 10 numeric characters in the source, it will also format it as (xxx) xxx-xxxx. If there are not exactly 10 characters in the source, the function will simply populate the raw value from your source.

Calculated Column

The functions are fully documented in the online documentation and autocomplete feature of DS3:

Using Lookups to read other entities and Option Sets

Using DS3 we can lookup the value of an accounts accountid by passing its details to the LOOKUP function:

LOOKUPB("accountid","account", WHEN("name",company_name))

LOOKUPB as opposed to LOOKUPA function is used to indicate that the B connection (Destination Connection) should be used rather than the Source (A).

When we want to lookup OptionSets we use a vertical '|' to separate the entity from its optionset. Lookup the id in the accountrolecode OptionSet in the contact entity where the name was Influencer.

LOOKUPA("id","contact|accountrolecode",WHEN("name","Influencer"))


Can't find the answers you're looking for? Contact our support team and we'll personally get back to you as quickly as we can.