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:
{
"Name" : "Acme",
"Type" : "Financial",
"Website" : "acme.com"
}
{
"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:
A Link whose source looks like the Acme record above and whose target is the Account object in Salesforce
A Link with no source and whose target is the Contact table
A Link Split connecting Link #1 to Link #2 and specifying the
Contactsfield 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.
{
"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
{
"favoriteFood" : "ice cream"
}
Mapping: preferences.foodItem to favorites.favoriteFood
{
"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)
{
"addresses" : [
{
"city" : "Boston",
"state" : "MA"
},
{
"city" : "New York",
"state" : "NY"
}
]
}
Mapping: previousAddresses to housingDetails.addresses
{
"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
{
"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)
{
"personalDetails" : {
"foods" : ["ice cream"]
}
}
Mapping: currentAddress to personalDetails.addresses
{
"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)
{
"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
{
"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)
{
"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)
{
"favorites" : [
{
"name" : "ice cream"
}
]
}
Mapping: currentAddress to personalDetails.location (where personalDetails is an array)
{
"personalDetails" : [
{
"location" : {
"city" : "Honolulu",
"state" : "HI"
}
}
]
}