Use case - Transformations
Overview
This article contains Tips & Tricks on the use of transformations in DataBridge.
Use cases
Clean field
Cleans a text field using a specific cleaning option. All cleaning options include white space, commas and periods.
Clean number removes all non-digit characters.
Clean text removes all special characters like dashes and dots.
In this example we want to clean VAT numbers.
In the transformation Clean field we choose the correct column and the clean option clean number
Below you can see the result. On the left the VAT nr in the import file. On the right the VAT number after the transformation is applied.
Replace
In an import file, you have a column called paying customer. It contains the values Yes and No. You want to import this information into Superoffice and make it visible by using the Stop sign in SuperOffice. The problem is that Yes will activate the Stop sign.
The solution is to create two Replace Transforms. With the first, you replace Yes with 0 and you use the second transform to replace No with 1
In the second step of the Profile configuration Configure Source, you can see the data from the import file. After the transformations, the result can be seen in step 4 Configure Fields.
Replace
Another Replace transform can be used in case you have a file with empty fields in the import file.
For instance, you have a column Our contact which you want to import. But not all fields in this column are filled in.
You can use the Replace transform where you choose to leave the first field empty and fill the second field with an associate from SuperOffice to be used as our contact. See the example below.
In the second step of the Profile configuration Configure Source, you can see the data from the import file. After the transformation, the result can be seen in step 4 Configure Fields as you can see below.
Merge fields
The import file has the address and home number in separate columns. You can use the Merge fields transform to merge the two columns with a delimiter of your choice.
In this example, the fields Address and Number will be merged into a new output field Street address. As a delimiter, a space is used.
.png?version=1&modificationDate=1567605614160&cacheVersion=1&api=v2&width=600&height=329){kind=spacer}
Copy field
We want to use the data from the address column in the import file for the postal address. This can be achieved by choosing the Copy Field transformation.
.png?version=1&modificationDate=1567605640552&cacheVersion=1&api=v2&width=600&height=308){kind=spacer}
The result can be viewed on the Summary page. The Address column from the import file can be used twice.
.png?version=1&modificationDate=1567605665227&cacheVersion=1&api=v2&width=800&height=137){kind=spacer}
Format text value
Besides formatting text values to upper or lower case, among others, we have now also introduced a new format option called Trim. This will remove all leading and trailing white spaces from the value in the fields.
.png?version=1&modificationDate=1567605698560&cacheVersion=1&api=v2&width=600&height=355){kind=spacer}
Add field
When importing data you can add a new field with a fixed value. Say you want to import a file containing projects and you want to link these to companies in SuperOffice. In the file itself, you have no field for Project Responsible (mandatory).
You can now add a field called Responsible and add an associate from SuperOffice
To link the projects to a company you need to add another field, 'Helper' field, to make the link between company and Project, more info
In the next step 'Configure fields' you can link these new fields to the correct SO fields.
