You are here:
Use Formulas in Omnistudio Data Mappers
To add data to the output of a Data Mapper, define formulas. Several types of Data Mappers (Extract, Transform, and Load) support formulas. When you define a formula, you map its output to the output JSON (for extracts and transforms) or Salesforce object field (for loads).
For details about the operators and functions that you can use in formulas, see Omnistudio Formulas and Functions.
If a variable name contains spaces or non-alphanumeric characters, enclose the
variable name in double quotes and precede it with var: in formulas. For example, if the JSON node name is Primary Guardian, specify it in formulas as var:"Primary Guardian".
If the name of a custom field includes special characters, sometimes you can't reference the field in a formula.
In the Omnistudio standard runtime, entering the incorrect query formula for Data Mappers returns null but doesn't display error logs. If you get a null response from a query, update the query.
- In the Data Mapper Interface page, go to the Formulas tab.
- Click Add Formula. An empty formula is added to the list.
-
In the Formula field, specify the desired logic. For
example, to determine the total price of the purchase items by a customer, enter a
formula such as:
SUM(Products:Price)You can also use relationship notation in formulas to reference fields in a parent object. See Relationship Notation versus Multiple Extract Steps.
- In the Formula Result Path field, specify a JSON node in which to store the formula result.
-
Map the result to the final output.
- For an Extract Data Mapper, on the Output tab, map the formula result to the output structure. Use a colon (:) to delimit levels in the input and output paths in mappings.
- For a Transform Data Mapper, on the Transforms tab, map the formula result to the output structure. Use a colon (:) to delimit levels in the input and output paths in mappings.
- For a Load Data Mapper, for each sObject that you want to update, on the Fields tab, map the formula result to the field that you want to update.
- Load: For each sObject that you want to update, go to its Fields tab and map the formula result to the specific field that you want to update.
Create an Omnistudio Data Mapper Example with a Formula
The following example accepts a list of prices and uses a formula to compute the total price.
To create this Data Mapper Transform, perform these steps:
- Go the Omnistudio Data Mapper Designer tab and click New. The Create dialog is displayed.
-
Specify a name for the Data Mapper and configure its settings as follows:
- Interface Type: Transform
- Input Type: JSON
- Output Type: JSON
- Click Save. The Data Mapper Interface page is displayed.
- Go to the Formulas tab and click Add Formula.
- In the Formula field, enter SUM(Products:Price).
- In the Formula Result Path field, enter TotalPrice.
-
On the Transforms tab, expand the Input JSON pane and paste this JSON structure into it:
{ "CustomerName": "Bob Smith", "Products": [ { "Name": "iPhone", "Price": 600 }, { "Name": "iPhone Case", "Price": 30 }, { "Name": "Ear Buds", "Price": 200 } ] } -
Expand the Expected JSON Output pane and paste this JSON structure into it:
{ "CustomerName": "Bob Smith", "TotalPrice": 830, "Products": [ { "Name": "iPhone", "Price": 600 }, { "Name": "iPhone Case", "Price": 30 }, { "Name": "Ear Buds", "Price": 200 } ] } - Click Quick Match. In the Quick Match window, click Auto Match, then click Save.
-
On the Preview tab, click Execute.
If you have built the example correctly, the JSON structure in the Response pane matches the Expected JSON Output except for the order of the top-level nodes.
-
In the Input pane, change one or more of the prices, then click Execute again.
Note how the
TotalPricevalue changes.
