Open Navigation

Advanced Data Mapping

Using the Script connector to map many fields between services

Our Data Mapper can be used to set up simple mappings from a field in one service to a field in another service.

If you need to map many fields at once, a more efficient way to achieve this would be to use a custom script connector step.

Using the script connector you can write any custom logic you need to use, and it can take inputs from other steps in the workflow.

In our data mapping script we would want to set up an input value which is the entity we would like to map from, and then transform it into the format we would like to use in another service.

Steps:

  • Set up workflow
  • Add an input value to the script step which is the record you want to map from
  • Add your mapping script logic
  • Use the result of the script step as input to the step you want to map to

Setting up the workflow

For our example workflow we will map records from Salesforce into Hubspot. The concepts introduced here will apply to mapping data between any services.

In our workflow we will setup:

  • A Salesforce step which gets a list of records
  • A Loop step which loops over each Salesforce record
  • A Script step which does the data mapping
  • A Hubspot step which ingests mapped data from the script step

The workflow will end up looking like the following:

Workflow

Getting records from Salesforce

Our Salesforce step is setup like this:

Salesforce setup

Using the Find Records operation we choose to find Accounts in our Salesforce instance, and return a list of them with the fields Account Name, Account Phone and City. The result of this step will be a list that we can provide to the Loop Step.

Looking at the debug output of the Find Records operation for the Salesforce connector we can see that 'Name', 'Phone' and 'BillingCity' are what is used by Salesforce:

sf-debug-find-records-1

This is what we then use for the left-hand values in the mapping script.

Writing the Data Mapping script

The Data Mapping Script step will require the record you want to map from as input, in our example we have taken the result from the Loop Step and named it salesforceRecord.

Script step setup

We can then add the following code which will take each salesforceRecord item and map the values onto a new set of fields. In the mapping object we have the Salesforce fields as the keys, and the Hubspot fields as the values. The script will then build a new object using the Hubspot fields and the corresponding values from the Salesforce object.

Using the Salesforce debug output to get the left-hand field names (Name, Phone, BillingCity) was explained above. Likewise, inspection of the debug output of a Hubspot operation can help find what are the raw field names (firstname, phone, city) actually accepted by Hubspot, and entered on the right side of the mapping list in the script:

exports.step = function(input) {
// In the mapping object, we define on the left side the fields we want to map from in Salesforce
// and on the right side the field names we want to map to in Hubspot:
var mapping = {
Name: 'firstname',
Phone: 'phone',
BillingCity: 'city'
};
// The new object we will use to create an entity in Hubspot:
var newEntity = {};
// For each field in the mapping, add a field into `newEntity`
// where the name is the key from the right side of the mapping
// and value from the Salesforce object, using the key from left side of the mapping
for (var field in mapping) {
newEntity[mapping[field]] = input.salesforceRecord[field];
}
return newEntity;
};

This script would then transform this input:

{
Name: 'Ines Connolly',
Phone: '+4408234821832',
BillingCity: 'London'
}

Into this new object:

{
firstname: 'Ines Connolly',
phone: '+4408234821832',
city: 'London'
}

The result of this can then be passed onto the Hubspot step which will be used to create a new customer in our Hubspot instance.

Setting up the Hubspot step

The Hubspot step can then ingest the result of the Script step, and use the new entity created to insert a new customer into our Hubspot instance.

Hubspot step setup

Note that in the case of Hubspot you can easily pass all of the mapped entity in by just converting the top-level Contact information object to a jsonpath and entering $.steps.script-1.result


This will not always be possible though. For example the Trello Create new card operation does not have a top-level object which can be changed to a jsonpath - individual values must be picked up from the mapping script step:

trello-create-card

Was this article helpful?
Yes
No