To get the data in the desired format and add this to the output node, Formulas are defined.
Supported DataRaptors: Extract, Transform, and Load.
When you define the formulas you will map its output node to the output JSON (If you are using it in Extract or Transform) or Salesforce object fields (If you are using it in Load).
1. ‘Add Formula’ will let you create new formulas.
2. You can add more than one formula and can use the output node added in ‘Formula Result Path’ as an input in the next formula step.
Creating Formulas in DataRaptor:
Example Custom JSON object to give as an input to DataRaptor Transform:
{
"CustomerName": "Bob Smith",
"Products": [
{
"Name": "iPhone",
"Price": 600
},
{
"Name": "iPhone Case",
"Price": 30
},
{
"Name": "Ear Buds",
"Price": 200
}
]
}
Example: Get the Average of the Product’s Price.
1. In the Dataraptor Transform interface page, go to the Formulas tab. Add a new formula.
2. Create a Formula to fetch the total price of the items purchased by the customer, using the SUM function and Price field.
Get the Price node by traversing to its parent node i.e., Products:Price
3. SUM(Products:Price) value will be stored in the TotalPrice node, this node will be referenced in the output path to map with the output node or salesforce field.
4.When you preview the DataRaptor, the TotalPrice will be added to the Response JSON.
Here are a few examples of formulas:
1. YEAR(TODAY()) : Result = 2022(To get the Year of the running date.)
2. YEAR(TODAY()) - 1: Result = 2021 (Gives the previous year of the running date.)
3. ADDDAY(TODAY(), -180) : Result = 11/27/2021 (Gives the current year with -180 days)
4. Products|1 (To get only the first element from the Products array.)
Complex Formulas:
1. Using the ‘Nested If’ Condition in the formula.
If the first statement is true then execute “Inactive” else execute the next If statement.
{
"IsChecked": true
}
IF(IsChecked = false, "Inactive", IF(IsChecked = true, "Active", ""))
Output: Active
2. Use Merge fields “%” to fetch the value from SObject fields.
IF(%claims:claimNumber% != $Vlocity.NULL , “ ”,$Vlocity.NULL)
3. To fetch the value after the ‘-’ symbol in the example below:
Input text = ‘abcdef-1234’
SUBSTRING(%Variable%, STRINGINDEXOF(%Variable%, '-')+1
Output: 1234.
4. For concatenating address fields by ignoring null values.
{
"Account":
{
"PersonMailingStreet" : "47 W 13th St",
"PersonMailingCity": "NewYork",
"PersonMailingState": "US",
"PersonMailingCountry": "USA",
"PersonMailingPostalCode": "11344"
}
}
CONCAT(IF(ISNOTBLANK(%Account:PersonMailingStreet%),IF(ISNOTBLANK(%Account:PersonMailingCity%) OR ISNOTBLANK(%Account:PersonMailingState%) OR ISNOTBLANK(%Account:PersonMailingCountry%) OR ISNOTBLANK(%Account:PersonMailingPostalCode%),CONCAT(%Account:PersonMailingStreet%,','),%Account:PersonMailingStreet%),''),IF(ISNOTBLANK(%Account:PersonMailingCity%),IF(ISNOTBLANK(%Account:PersonMailingState%) OR ISNOTBLANK(%Account:PersonMailingCountry%) OR ISNOTBLANK(%Account:PersonMailingPostalCode%),CONCAT(%Account:PersonMailingCity%,','),%Account:PersonMailingCity%),''),IF(ISNOTBLANK(%Account:PersonMailingState%),IF(ISNOTBLANK(%Account:PersonMailingCountry%) OR ISNOTBLANK(%Account:PersonMailingPostalCode%),CONCAT(%Account:PersonMailingState%,','),%Account:PersonMailingState%),''),IF(ISNOTBLANK(%Account:PersonMailingCountry%),IF(ISNOTBLANK(%Account:PersonMailingPostalCode%),CONCAT(%Account:PersonMailingCountry%,','),%Account:PersonMailingCountry%),''),IF(ISNOTBLANK(%Account:PersonMailingPostalCode%), %Account:PersonMailingPostalCode%,''))
Output: 47 W 13th St,NewYork,US,USA,11344
References: