Multidimensional Data

What Is It?

When records have complex (non-scalar) data inside one of their fields, you’re dealing with multidimensional data. You could also think of this scenario as nested or compound data.

This is best explained with an example:

One-dimensional
{
        "Name" : "Acme",
        "Type" : "Financial",
        "Website" : "acme.com"
}
Multidimensional
{
        "Name" : "Acme",
        "Type" : "Financial",
        "Website" : "acme.com",
        "Employees" : [
                {
                        "FirstName" : "Julie",
                        "LastName" : "Smith"
                },
                {
                        "FirstName" : "Thomas",
                        "LastName" : "Lincoln"
                }
        ]
}

Historically, Salesforce org create/update/delete operations only accept one-dimensional data. You work with a list of Accounts, or a list of Contacts, but not an Account with embedded Contacts. There have been small steps more recently towards multidimensional record writes with things like the REST Tree API, but it’s still very uncommon and that’s not likely to change.

That’s all fine, one dimension is typically easier to work with, but not every system out there is as consistent as Salesforce is in this domain.

Here’s a sample Invoice record from the Quickbooks Online API:

{
  "Invoice": {
        "TxnDate": "2014-09-19",
        "domain": "QBO",
        "PrintStatus": "NeedToPrint",
        "SalesTermRef": {
          "value": "3"
        },
        "TotalAmt": 362.07,
        "Line": [
          {
                "Description": "Rock Fountain",
                "DetailType": "SalesItemLineDetail",
                "SalesItemLineDetail": {
                  "TaxCodeRef": {
                        "value": "TAX"
                  },
                  "Qty": 1,
                  "UnitPrice": 275,
                  "ItemRef": {
                        "name": "Rock Fountain",
                        "value": "5"
                  }
                },
                "LineNum": 1,
                "Amount": 275.0,
                "Id": "1"
          },
          {
                "Description": "Fountain Pump",
                "DetailType": "SalesItemLineDetail",
                "SalesItemLineDetail": {
                  "TaxCodeRef": {
                        "value": "TAX"
                  },
                  "Qty": 1,
                  "UnitPrice": 12.75,
                  "ItemRef": {
                        "name": "Pump",
                        "value": "11"
                  }
                },
                "LineNum": 2,
                "Amount": 12.75,
                "Id": "2"
          },
          {
                "Description": "Concrete for fountain installation",
                "DetailType": "SalesItemLineDetail",
                "SalesItemLineDetail": {
                  "TaxCodeRef": {
                        "value": "TAX"
                  },
                  "Qty": 5,
                  "UnitPrice": 9.5,
                  "ItemRef": {
                        "name": "Concrete",
                        "value": "3"
                  }
                },
                "LineNum": 3,
                "Amount": 47.5,
                "Id": "3"
          },
          {
                "DetailType": "SubTotalLineDetail",
                "Amount": 335.25,
                "SubTotalLineDetail": {}
          }
        ],
        "DueDate": "2014-10-19",
        "ApplyTaxAfterDiscount": false,
        "DocNumber": "1037",
        "sparse": false,
        "CustomerMemo": {
          "value": "Thank you for your business and have a great day!"
        },
        "Deposit": 0,
        "Balance": 362.07,
        "CustomerRef": {
          "name": "Sonnenschein Family Store",
          "value": "24"
        },
        "TxnTaxDetail": {
          "TxnTaxCodeRef": {
                "value": "2"
          },
          "TotalTax": 26.82,
          "TaxLine": [
                {
                  "DetailType": "TaxLineDetail",
                  "Amount": 26.82,
                  "TaxLineDetail": {
                        "NetAmountTaxable": 335.25,
                        "TaxPercent": 8,
                        "TaxRateRef": {
                          "value": "3"
                        },
                        "PercentBased": true
                  }
                }
          ]
        },
        "SyncToken": "0",
        "LinkedTxn": [
          {
                "TxnId": "100",
                "TxnType": "Estimate"
          }
        ],
        "BillEmail": {
          "Address": "[email protected]"
        },
        "ShipAddr": {
          "City": "Middlefield",
          "Line1": "5647 Cypress Hill Ave.",
          "PostalCode": "94303",
          "Lat": "37.4238562",
          "Long": "-122.1141681",
          "CountrySubDivisionCode": "CA",
          "Id": "25"
        },
        "EmailStatus": "NotSet",
        "BillAddr": {
          "Line4": "Middlefield, CA  94303",
          "Line3": "5647 Cypress Hill Ave.",
          "Line2": "Sonnenschein Family Store",
          "Line1": "Russ Sonnenschein",
          "Long": "-122.1141681",
          "Lat": "37.4238562",
          "Id": "95"
        },
        "MetaData": {
          "CreateTime": "2014-09-19T13:16:17-07:00",
          "LastUpdatedTime": "2014-09-19T13:16:17-07:00"
        },
        "CustomField": [
          {
                "DefinitionId": "1",
                "StringValue": "102",
                "Type": "StringType",
                "Name": "Crew #"
          }
        ],
        "Id": "130"
  },
  "time": "2015-07-24T10:48:27.082-07:00"
}

Yikes! Not only are there nested lists of records (arrays), there are directly-nested records as well (maps). So how do we get something like this to play nicely with Salesforce?

Working With Multidimensional Data in Valence

The best way to understand how Valence handles multidimensional is to talk about outbound from Salesforce and inbound to Salesforce separately.

Then we’ll get into the nitty-gritty details of how the actual field transformations occur.

Outbound From Salesforce

If we need to retrieve data from multiple Salesforce objects to combine and send to an external system, we are going to rely on SOQL relationship queries, which we already talked about a bit in the lazy loading section of the Schema page.

We might end up pulling some scalar values in from a related record using a child-to-parent query like this:

SELECT FirstName, LastName, Account.Name, Account.Website FROM Contact

Which would traverse the the relationship from a Contact to its parent Account and pull down some Account fields to use in Valence mappings. The record returned by this query would look like this:

[
        {
                "FirstName" : "Jim",
                "LastName" : "Jonston",
                "Account" : {
                        "Name" : "Acme",
                        "Website" : "acme.com"
                }
        }
]

Note

Salesforce limits child-to-parent queries to five levels.

Alternatively (or at the same time), we can do a parent-to-child relationship query:

SELECT Name, Website, (SELECT FirstName, LastName FROM Contacts) FROM Account

This will fetch an Account and also nest all its related Contacts in a field called Contacts:

[
        {
                "Name" : "Acme",
                "Website" : "acme.com",
                "Contacts" : [
                        {
                                "FirstName" : "Jim",
                                "LastName" : "Jonston"
                        },
                        {
                                "FirstName" : "Samantha",
                                "LastName" : "McCay"
                        }
                ]
        }
]

Note

Salesforce limits parent-to-child queries to one level.

Valence will automatically build these queries as-needed depending on which source fields you have selected for mappings. To bring in related records, simply expand those relationships in the schema browser and start using them in mappings.

Since the target system is expecting multidimensional data, all that remains is to map to those fields and let Valence do the heavy lifting. To better understand how that actually happens, head down the page to Transformation Mechanics.

Inbound To Salesforce

Having multidimensional data coming into Salesforce is definitely the trickier of the two directions. Salesforce will not accept records nested inside other records, and each Link writes to only one target object. So how do we solve this dilemma?

With Link Splits!

You can read in much more detail about Link Splits at the link above, but in a nutshell a Link Split allows us to take some of the data coming into a Link and “split” it off to another Link.

So let’s say we had that same record shape from just above:

[
        {
                "Name" : "Acme",
                "Website" : "acme.com",
                "Contacts" : [
                        {
                                "FirstName" : "Jim",
                                "LastName" : "Jonston"
                        },
                        {
                                "FirstName" : "Samantha",
                                "LastName" : "McCay"
                        }
                ]
        }
]

Except now we are bring this record into Salesforce. We could tackle that by setting up the following:

  1. A Link whose source looks like the Acme record above and whose target is the Account object in Salesforce

  2. A Link with no source and whose target is the Contact table

  3. A Link Split connecting Link #1 to Link #2 and specifying the Contacts field as its “inner list” (again, read the Link Splits page to learn more)

Why doesn’t the second Link have a source, you ask? Because it is being fed records directly from Link #1, and doesn’t go out to retrieve any data of its own.

When the Link Split passes the Account record down to Link #2, because we selected an “inner list” it will actually invert the records and they’ll look like this:

[
        {
                "FirstName" : "Jim",
                "LastName" : "Jonston",
                "$ParentSource" : {
                        "Name" : "Acme",
                        "Website" : "acme.com"
                },
                "$ParentTarget" : {
                        "Id" : "0015600000Vf9A9AAJ"
                }
        },
        {
                "FirstName" : "Samantha",
                "LastName" : "McCay",
                "$ParentSource" : {
                        "Name" : "Acme",
                        "Website" : "acme.com"
                },
                "$ParentTarget" : {
                        "Id" : "0015600000Vf9A9AAJ"
                }
        }
]

Each record destined to become a Contact in Salesforce has its field values and also a copy of the values from its parent Account (so for example you could easily write to Contact.AccountId and relate the new Contact from Link #2 to the new Account created by Link #1).

If for some crazy reason you had even more nested layers of records, you can keep setting up Link Splits and new Links to peel more and more layers off this onion. You can even refer back to any parent field from any layer! Get ready to map $ParentSource.$ParentSource.$ParentSource.Id!

Tip

Link Splits aren’t just for solving inbound multidimensional data problems. You can use them in a variety of interesting ways, like routing records to various secondary Links based some field on each record designating a category.

Transformation Mechanics

Ok, so we’ve learned about the broad aspects of tackling multidimensional data outbound from Salesforce and inbound to Salesforce, but how do the actual fields get mapped? Let’s dig into that.

Valence works really hard to make your life easier and tricky multidimensional mappings are no exception! If Valence can figure out what you’re trying to do with a mapping it will help you accomplish it. You can take a nested source field and write it to a top-level target field, no problem. You can take a top-level source field and write it to a nested target field! Valence will automatically create each layer between the target top level and the nested field you are writing to when it builds that record for delivery.

What we’re really talking about here is the Mappings Filter, which is a special Filter that comes with Valence and is responsible for turning source fields into target fields. Below are all the permutations of how this Filter handles Mappings based on the data types of the Mapping’s source and target fields.

You’ll probably fall asleep trying to read through all the possible variations, so suffice it to say that as a rule of thumb just map whatever you want to whatever you want, and Valence will more than likely line things up in a way that is intuitive and makes sense for what you would have wanted to happen, without much effort on your part.

If you really are going to read through all this, here’s a sample source record that will be used in each of the examples below.

Source Record Shape
{
        "preferences" : {
                "foodItem" : "ice cream",
                "flavors" : ["chocolate", "double chocolate", "mega chocolate"]
        },
        "currentAddress" : {
                "city" : "Honolulu",
                "state" : "HI"
        },
        "previousAddresses" : [
                {
                        "city" : "Boston",
                        "state" : "MA"
                },
                {
                        "city" : "New York",
                        "state" : "NY"
                }
        ]
}

Non-Array to Non-Array

Source: A scalar or map value that might or might not be nested, and is not inside any arrays

Target: A scalar or map value that might or might not be nested, and is not inside any arrays

The data value will be extracted from the source field (even if nested), and written to the target field. If the target field is nested, intermediary layers will be created.

Examples

Mapping: preferences.foodItem to favoriteFood

Target Result
{
        "favoriteFood" : "ice cream"
}

Mapping: preferences.foodItem to favorites.favoriteFood

Target Result
{
        "favorites" : {
                "favoriteFood" : "ice cream"
        }
}

Array to Array

Source: An array of scalars, arrays, or maps that might or might not be nested, but is not inside any arrays

Target: An array of scalars, arrays, or maps that might or might not be nested, but is not inside any arrays

Each item in the source array will be written unchanged and in its entirety as an item in the target array. If the target field is nested, intermediary layers will be created.

Examples

Mapping: previousAddresses to addresses (where addresses is an array)

Target Result
{
        "addresses" : [
                {
                        "city" : "Boston",
                        "state" : "MA"
                },
                {
                        "city" : "New York",
                        "state" : "NY"
                }
        ]
}

Mapping: previousAddresses to housingDetails.addresses

Target Result
{
        "housingDetails" : {
                "addresses" : [
                        {
                                "city" : "Boston",
                                "state" : "MA"
                        },
                        {
                                "city" : "New York",
                                "state" : "NY"
                        }
                ]
        }
}

Array to Non-Array

Source: An array of scalars, arrays, or maps that might or might not be nested, but is not inside any arrays

Target: A scalar value that might or might not be nested, but is not inside any arrays

The items in the source array will be individually “stringified”, joined together with commas, and written as a String to the target field. If the target field is nested, intermediary layers will be created.

Examples

Mapping: previousAddresses to housingInfo

Target Result
{
        "housingInfo" : "{city=Boston, state=MA},{city=New York, state=NY}"
}

Non-Array to Array

Source: A scalar or map value that might or might not be nested, but is not inside any arrays

Target: An array of scalars, arrays, or maps that might or might not be nested, but is not inside any arrays

The data value will be extracted from the source field (even if nested), and written unchanged and in its entirety as an item in the target array. If the target field is nested, intermediary layers will be created.

If multiple mappings are like this and write to the same array target field, each value will end up as its own item in that one array.

Examples

Mapping: preferences.foodItem to personalDetails.foods (where foods is an array)

Target Result
{
        "personalDetails" : {
                "foods" : ["ice cream"]
        }
}

Mapping: currentAddress to personalDetails.addresses

Target Result
{
        "personalDetails" : {
                "addresses" : [
                        {
                                "city" : "Honolulu",
                                "state" : "HI"
                        }
                ]
        }
}

Non-Array (inside an Array) to Array

Source: A scalar or map value that might or might not be nested, and has an array somewhere above it in the source schema

Target: An array of scalars, arrays, or maps that might or might not be nested, but is not inside any arrays

The data value will be extracted from the source field (even if nested) inside each item of the ancestor array, creating an array of source data values which will become items in the target array. If the target field is nested, intermediary layers will be created.

Examples

Mapping: previousAddresses.city to personalDetails.citiesVisited (where citiesVisited is an array field)

Target Result
{
        "personalDetails" : {
                "citiesVisited" : ["Boston", "New York"]
        }
}

Non-Array (inside an Array) to Non-Array

Source: A scalar or map value that might or might not be nested, and has an array somewhere above it in the source schema

Target: A scalar or map value that might or might not be nested, but is not inside any arrays

The data value will be extracted from the source field (even if nested) inside the first item of the ancestor array, and that single value will be written to the target field. If the target field is nested, intermediary layers will be created.

Examples

Mapping: previousAddresses.city to personalDetails.aPreviousCity

Target Result
{
        "personalDetails" : {
                "aPreviousCity" : "Boston"
        }
}

Non-Array (inside an Array) to Non-Array (inside an Array)

Source: A scalar or map value that might or might not be nested, and has an array somewhere above it in the source schema

Target: A scalar or map value that might or might not be nested, and has an array somewhere above it in the source schema

The data value will be extracted from the source field (even if nested) inside each item of the ancestor array, and these data values will be written to different items inside the ancestor array of the target field. If the target field is nested, intermediary layers will be created.

Examples

Mapping: previousAddresses.city to addresses.location (where addresses is an array)

Target Result
{
        "addresses" : [
                {
                        "location" : "Boston"
                },
                {
                        "location" : "New York"
                }
        ]
}

Non-Array to Non-Array (inside an Array)

Source: A scalar or map value that might or might not be nested, but is not inside any arrays

Target: A scalar or map value that might or might not be nested, and has an array somewhere above it in the source schema

The data value will be extracted from the source field (even if nested), and will be written to the first item inside the ancestor array of the target field. If the target field is nested, intermediary layers will be created.

Examples

Mapping: preferences.foodItem to favorites.name (where favorites is an array)

Target Result
{
        "favorites" : [
                {
                        "name" : "ice cream"
                }
        ]
}

Mapping: currentAddress to personalDetails.location (where personalDetails is an array)

Target Result
{
        "personalDetails" : [
                {
                        "location" : {
                                "city" : "Honolulu",
                                "state" : "HI"
                        }
                }
        ]
}