##################### 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. .. _scalar: https://softwareengineering.stackexchange.com/questions/238033/what-does-it-mean-when-data-is-scalar This is best explained with an example: .. code-block:: json :caption: One-dimensional { "Name" : "Acme", "Type" : "Financial", "Website" : "acme.com" } .. code-block:: json :caption: Multidimensional :emphasize-lines: 5-14 { "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. .. _REST Tree API: https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/resources_composite_sobject_tree.htm 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`_: .. _Quickbooks Online API: https://developer.intuit.com/app/developer/qbo/docs/api/accounting/all-entities/invoice .. code-block:: json { "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": "Familiystore@intuit.com" }, "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 :ref:`lazy loading ` section of the :doc:`/advanced-concepts/schema` page. .. _SOQL relationship queries: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships.htm 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: .. code-block:: json [ { "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``: .. code-block:: json [ { "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`_. .. _multi-link-split-example: 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 :doc:`/advanced-concepts/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: .. code-block:: json [ { "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 ``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: .. code-block:: json [ { "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. .. _mapping-behavior: 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. .. code-block:: json :caption: 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** .. code-block:: json :caption: Target Result { "favoriteFood" : "ice cream" } Mapping: **preferences.foodItem** to **favorites.favoriteFood** .. code-block:: json :caption: 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) .. code-block:: json :caption: Target Result { "addresses" : [ { "city" : "Boston", "state" : "MA" }, { "city" : "New York", "state" : "NY" } ] } Mapping: **previousAddresses** to **housingDetails.addresses** .. code-block:: json :caption: 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** .. code-block:: json :caption: 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) .. code-block:: json :caption: Target Result { "personalDetails" : { "foods" : ["ice cream"] } } Mapping: **currentAddress** to **personalDetails.addresses** .. code-block:: json :caption: 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) .. code-block:: json :caption: 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** .. code-block:: json :caption: 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) .. code-block:: json :caption: 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) .. code-block:: json :caption: Target Result { "favorites" : [ { "name" : "ice cream" } ] } Mapping: **currentAddress** to **personalDetails.location** (where personalDetails is an array) .. code-block:: json :caption: Target Result { "personalDetails" : [ { "location" : { "city" : "Honolulu", "state" : "HI" } } ] }