Transformations for DataBridge Export

Overview

DataBridge supports transformations for import and export files. Data transformations can be added to import and export jobs.
Click here to view the article on Transformations for DataBridge Import.

Note: Transformations can now be chained together so the field created by a transformation can be further transformed. More info

TIP 1: Notes can be added to transformations to make their purpose clear. 

TIP 2: After you select a transformation and go to the Configure Fields page in your DataBridge Profile you can easily see which columns the Transformation have been applied to. An icon has been added with a tooltip that shows all the transformations applied to the column, in the order in which they are applied.

TIP 3: A preview of the transformed columns is now available directly in the transformation window. More information

How to

Create a new profile or edit an existing profile by clicking Manage profile. Notice that you now have an extra step in the configuration wizard called Transform.

On the Transform page, select the Transformation you want and click Add.

The options to choose from are:

The added transformations can be edited or removed at any time, see below.

THE OUTPUT RESULTS

Add conditional field

Create a new field with the value of the selected field. When the value of that field is empty, the value of the fallback field will be used. 
In the example below a User Defined Field is used (in this case this represents the SuperOffice serial number). As a fallback field, the User Defined Field InfoBridge number is used.
The field name in the output field will be Customer Serial Number. 
When the first field 'SuperOffice Serial number' does not contain any information the 2nd field 'InfoBridge number' will be used. 

Add field

For our export, we have chosen to add a field Code with a fixed value 123

The result can be seen on the Summary page.

Append text value

The “Append text value” transformation allows for prepending and appending text to field values.

Clean field

In the example below, we have chosen the transformation 'Clean field' to convert a value with leading zeros and non-digits to a numeric value. 

In the Configure Source page you can see the original value in the preview

In the Summary page you can see the result of the transformation, the leading zeros and non-digit characters are removed.

Copy field

This transformation can be used for when you want one field from the source to map to multiple fields in the destination. In this example, the AssociateId will be copied to the column AssociateId copy.

Current date time

This transformation can be used to fill a field in an export file with the current date and time. Choose the Transform Current date time and choose a Field name

On the Summary page of the Export wizard you will see the result of this transform

Find Pattern

Find and extract the value from the field using the regular expression search pattern. Example: the ID between parentheses should be extracted from "Doe, John (AB12345)"

A regular expression is a form of advanced searching that looks for specific patterns, as opposed to certain terms and phrases. With RegEx you can use pattern matching to search for particular strings of characters rather than constructing multiple, literal search queries.

• Add the transformation "Find Pattern" and choose the field you want to extract the data from. In this example the field ID
• Choose a name for the Output field
• And fill in the RegEx you need. In this example I need Any data between the parentheses so I will use \((.*)\)

In the Configure Fields page in your DataBridge Profile you can now see the new field with the extracted data. It can now be mapped to the correct field in Superoffice.

Please visit this page for more examples to inspire you.

Note: We support two scenarios: (1) regex without any capturing groups and (2) regex with one capturing group
(1) Anything that match the pattern will be used as the value (my example before the last example)
(2) The value in the capturing group will be uased as the value (all other examples)

Format text value

With Format text value you can format the values in the chosen field. There are a couple of format options.
As an example let's use 'hello YouTube'

We have chosen the format 'Upper Case' for the Name field. And this is the result on the Summary page.

Map list

Converts each possible value of an input field into an item from a specific list in the destination. This transformation is an enhancement for the Replace transformation so you do not have to use multiple Replace transformations anymore.

In this example below we want to convert the values for appointment types

Merge fields

In this example, we will merge the first and last name in the Export file. The fields will be merged into a new field 'Name'. As a delimiter, we use a space.

The result can be seen on the Summary page.

Remove fields

 Transform prevents the selected fields from appearing in the output. In the example below the two selected fields will not be exported.

Rename fields 

This transform enables you to rename the title of a field. In this example, the field 'name' will be replaced by 'Company Name' in the output file.

Replace

In this example, we will replace SuperOffice values. When they match, the values in the Interest column 'persint/Newsletter' will be replaced with the value OK. 

On the Summary page, you can see the result of the replace transformation.

Skip

With this transformation, you can choose to skip a record when a specified value is matched.

Split field

Splits a field with a separator and adds a new field with the selected segment(s)

You can find a detailed description of this transformation here

Switch fields

The Switch fields transformation can be used to configure which field to export the value from depending on the value of another field.

In the example below we have chosen to create a column code. The value depends on the country. 

The result