Open Navigation

Data Mapper

The Data Mapper helper allows you to set up data mappings

Overview

Sometimes when working with data in your workflows, it is necessary to set up 'mappings' for your data. Two general scenarios which would necessitate use of the Tray Data Mapper are:

  1. You are pulling data from a service and need the results to be formatted so as to update a particular field in a database with a value that is more helpful for the users of the database (e.g. 'landline call' and 'cellphone call' could both be mapped to 'phone call').

    The Data Mapper has a Map Data operation for this purpose.

  2. You wish to automatically pass data from one service to another by specifying a mapping 'table' so that e.g. the 'name' field in Service 1 is automatically mapped to the 'item' field in Service 2.

    The Data Mapper has a Map Fields operation for this purpose.

Map Data Example

map-data-workflow-2

The above example shows a manually triggered workflow which implements the following steps:

  1. Use a Script Helper to create some dummy data. In a live situation these messages would be pulled in from another connector step (Salesforce, Intercom etc.).

    data-mapper-script

    In the script box enter the following as an array of 'records':

    exports.step = function() {
    return {
    "records": [
    {"Id": "0345rgrgi6AAE", "Name": "Candidate 1", "Trial_stage": "Scheduled", "URL": "https://testcourse.com/projects/9999"},
    {"Id": "034ef3r934EwE", "Name": "Candidate 2", "Trial_stage": "Activated", "URL": "https://testcourse.com/projects/9999"},
    {"Id": "239fefuHsw9dE", "Name": "Candidate 3", "Trial_stage": "Successful", "URL": "https://testcourse.com/projects/9999"}
    ]
    }
    };
  2. Add a Loop Helper to loop through the above records one by one. Set the List as $.steps.script-1.result.records:

    loop-1

    This will loop through the array of records from the script.

  3. For the Data Mapper step set the operation to Map Data, the Value to $.steps.loop-1.value.Trial_stage and then add the Trial_stage mappings:

    data-mappings-2

    The full mappings to use are:

    From: Scheduled
    to: Trial: Scheduled
    From: Activated
    to: Trial: Activated
    From: Successful
    to: Trial: Successful
    From: Failed
    to: Trial: Failed

    So for each record that is passed to the Data Mapper, it will look for any results in the Trial_stage field and map them as above.

    Looking at the logs for a run of the Data Mapper (note that logs won't be available until you have clicked 'Run Workflow Now' after you have completed the workflow setup) will show you that the value Scheduled was passed through the mapping table and changed to Trial: Scheduled:


    data-mapper-debug

  4. Finally, the result of each loop is fed into a Google Bigquery database (you will need to authenticate the Bigquery connector with your Bigquery account and setup a dataset and table), using the Insert Rows Operation. The Project Id and Dataset Id are specified and then the result of the message is fed in:

    big-query-data-fields

    The key thing to note here is that, while the Name and Id fields take their data from the loop helper, Stage takes it from the Data Mapper using $.steps.data-mapper-1.result

  5. Once the workflow is setup, click 'Run Workflow Now'
    A successful run will then lead to your Bigquery dataset being updated.
    Logging into your Bigquery account and running a query such as SELECT * FROM trainee_trials.trainee_listing will give you a result like this:

    big-query-table-result

    Looking at the Stage field for each entry shows that our mapping has been successful!

    And clicking on the Debug tab will enable you to view the output logs of each stage of your workflow:

    data-mapper-workflow-output

Map Fields Example

Coming soon!

Using the Script connector to map many fields between services

If you need to map many fields at once, an easier 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