The Ultimate DataRaptor Formula Reference for OmniStudio Developers

VlocityGru
4 days ago
6 min read

Introduction

This guide explains DataRaptor formulas the way developers actually think:

Input → Formula → Output → Why this output?

Every example uses the same JSON so you don’t waste time re-mapping data mentally. Each result is followed by a plain-English explanation of how OmniStudio calculates it.

DataRaptor Formula Operators – Complete Reference Table

Category	Operator	What It Does (Brief)	Example	Result
Logical	&&	True if all conditions are true	(600 > 100) && (2 > 0)	true
Logical	\|\|	True if any of the conditions are true	(600 > 100) \|\| (2 < 0)	true
Comparison	>	Left value greater than right	600 > 200	true
Comparison	>=	Greater than or equal	200 >= 200	true
Comparison	<	Left value less than right	30 < 100	true
Comparison	<=	Less than or equal	100 <= 100	true
Comparison	!= , <>	Values are not equal	"iPhone" != "Case"	true
Comparison	==	Values are equal	2 == 2	true
Assignment	=	Assigns value	var:Total = 600	Total = 600
String	LIKE	Contains text (case-sensitive)	"iPhone" LIKE "Phone"	true
String	NOTLIKE	Does not contain text	"Case" NOTLIKE "Phone"	true
String	~=	Case-insensitive match	"ABC" ~= "abc"	true
Math	+	Adds numbers	600 + 30	630
Math	-	Subtracts numbers	600 - 30	570
Math	*	Multiplies numbers	30 * 2	60
Math	/	Divides numbers	600 / 2	300
Math	%	Percentage of first by second	50 % 20	10
Math	^	Power	2 ^ 3	8

Variable Names with Spaces

Input JSON Example

{
  "Primary School": "Green Valley High",
  "Employee Name": "John Doe"
}

Accessing Variables with Spaces:

Formula Syntax	Output
var:"Primary School"	Green Valley High
var:"Employee Name"	John Doe

Why Quotes("") Are Needed ?

Spaces or special characters in variable names cannot be interpreted directly.
Enclosing the variable name in quotes tells DataRaptor to treat it as a single identifier.
The var: prefix signals that this is a variable reference, not a literal string.

Attribute Reference

You can reference object attributes directly in formulas to evaluate conditions or extract values.

Example JSON/Record Context

Assume an Account object with the attribute:

{
  "Account": {
    "PhoneColor": "Red",
    "Industry": "Telecom"
  }
}

Output:

Formula Syntax	Output
Account:@PhoneColor == "Red"	true

Why This Works

Account → the object you are referencing.
@PhoneColor → accesses the PhoneColor attribute of the Account object.
== "Red" → checks if the attribute value is equal to "Red".
The formula returns true because the value matches.

Best Practices for Attribute References

Use the @ prefix to reference attributes of objects.
Combine with comparison operators (==, !=, <, >) for conditional logic.
Keep names consistent with Salesforce API names for objects and fields.
Quotes are required for string values in comparisons.

✅ Tip: You can use attribute references in IF statements or complex formulas:

IF(Account:@PhoneColor == "Red", "High Priority", "Normal")

Mathematical Functions

DataRaptor formulas allow you to perform calculations on numeric fields, such as summing values, finding averages, or rounding numbers.

Example Input JSON

{
  "Products": [
    { "Name": "iPhone", "Price": 600 },
    { "Name": "iPhone Case", "Price": 30 },
    { "Name": "Ear Buds", "Price": 200 }
  ]
}

Function	Formula	Output	Explanation
SUM	SUM(Products:Price)	830	Adds all product prices: 600 + 30 + 200 = 830
AVG	AVG(Products:Price)	276.67	Average = Total ÷ Count = 830 ÷ 3 = 276.67
MIN	MIN(Products:Price)	30	Returns the lowest numeric value → 30
MAX	MAX(Products:Price)	600	Returns the highest numeric value → 600
ROUND	ROUND(AVG(Products:Price), 0, CEILING)	277	CEILING rounds the average 276.67 up to 277

String Functions

DataRaptor allows you to manipulate text using formulas like CONCAT and SUBSTRING.

Example Input JSON

{
  "Customer": { "Name": "Bob Smith" },
  "Products": [
    { "Name": "iPhone", "Price": 600 },
    { "Name": "iPhone Case", "Price": 30 },
    { "Name": "Ear Buds", "Price": 200 }
  ]
}

Function	Formula	Output	Explanation
CONCAT	CONCAT(Customer:Name, " bought ", LISTSIZE(Products), " products")	Bob Smith bought 3 products	Concatenates text with variables and list size to create a readable sentence. ListSize gives you the size of the array. The ListSize function is explained below.
SUBSTRING	SUBSTRING(Customer:Name, 0, 3)	Bob	Extracts the first 3 characters from Customer:Name (index 0–2)

Date & Time Functions

Date formulas help you calculate age, date differences, and time-based values dynamically.

AGE() returns an approximate value based on the current date.
DATEDIFF() returns the difference in days by default.
TODAY() always evaluates to the current system date at runtime.

Example Input JSON

{
  "Customer": {
    "Name": "Bob Smith",
    "BirthDate": "1992-06-15"
  },
  "Order": {
    "OrderDate": "2024-01-01"
  }
}

Function	Formula	Output	Explanation
AGE	AGE(Customer:BirthDate)	32	Calculates the difference between today’s date and the birth date in years
DATEDIFF	DATEDIFF(Order:OrderDate, TODAY())	360 (days)	Returns the number of days between the order date (2024-01-01) and today

List & JSON Functions

List functions help you count, filter, sort, and merge arrays in DataRaptor formulas.

Example Input JSON

{
  "Products": [
    { "Name": "iPhone", "Price": 600 },
    { "Name": "iPhone Case", "Price": 30 },
    { "Name": "Ear Buds", "Price": 200 }
  ],
  "Accessories": [
    { "Name": "Screen Guard", "Price": 20 },
    { "Name": "EAR", "Price": 150 }
  ]
}

Function	Formula	Output	Explanation
LISTSIZE	LISTSIZE(Products)	3	Counts the number of objects in the Products array
FILTER	FILTER(LIST(Products), 'Price > 100')	iPhone, Ear Buds	Includes only products where Price is greater than 100
SORTBY	SORTBY(LIST(Products), 'Price', '[:DSC]')	iPhone → Ear Buds → iPhone Case	Sorts products by Price in descending order
LISTMERGE	LISTMERGE(Products, Accessories)	Products + Accessories (EAR included) Example - [{ "Name": "iPhone", "Price": 600 }, { "Name": "iPhone Case", "Price": 30 }, { "Name": "Ear Buds", "Price": 200 }, { "Name": "Screen Guard", "Price": 20 }, { "Name": "EAR", "Price": 150 }]	Combines all lists; items from later lists can overwrite earlier ones. All Products + Accessories are present
LISTMERGEPRIMARY	LISTMERGEPRIMARY(Products, Accessories)	Products only (EAR excluded) Example - [{ "Name": "iPhone", "Price": 600 }, { "Name": "iPhone Case", "Price": 30 }, { "Name": "Ear Buds", "Price": 200 }]	Keeps only items from the first list and ignores unmatched items from others. Keeps only the first (primary) list. Ignores all items from Accessories.

✅ Notes & Best Practices

Use LIST() when a function explicitly expects a list (array) input.
FILTER is ideal for eligibility, pricing rules, and conditional display logic.
SORTBY supports ascending (ASC) and descending (DSC) order.
Prefer LISTMERGEPRIMARY when the primary list must remain authoritative.

🧠 One-Line Memory Trick (Great for Blog)

LISTMERGE = Add everything
LISTMERGEPRIMARY = Trust the first list only

Conditional Logic

Example Input JSON

{
  "Products": [
    { "Name": "iPhone", "Price": 600 },
    { "Name": "iPhone Case", "Price": 30 },
    { "Name": "Ear Buds", "Price": 200 }
  ]
}

Function	Formula	Output	Explanation
IF	IF(SUM(Products:Price) > 500, "Premium Customer", "Standard Customer")	Premium Customer	Since the total price of all products (830) is greater than 500, the condition evaluates to true and the first value is returned.

How It Works (Simple Breakdown)

SUM(Products:Price) calculates the total purchase amount
> 500 evaluates the condition
If true, the first value is returned
If false, the second value is returned

Other Useful Formulas

{"Products": [

{

"Name": "iPhone",

"Price": 600

{

"Name": "iPhone Case",

"Price": 30

{

"Name": "Ear Buds",

"Price": 200

}

"DynamicIndex": 1}

Function	Formula	Example Input / Context	Output	Explanation
VALUELOOKUP	VALUELOOKUP(Products, DynamicIndex, "Price")	DynamicIndex = 1 → Products[1] = iPhone Case	30	Check the above mentioned JSON. ProductsRefers to the list (array) in the input JSON. DynamicIndex = 1Points to the second element in the list:Products[1] → iPhone Case "Price"Specifies the field to fetch from the selected product. Returned ValueThe Price of iPhone Case, which is 30.
ABS	ABS(-1)	-1	1	Returns the absolute value
ADDDAY	ADDDAY("1999-01-01", 100)	Base date + 100 days	1999-04-11	Adds days to a date
ADDMONTH	ADDMONTH("1999-01-01", 100)	Base date + 100 months	2007-05-01	Adds months to a date
ADDYEAR	ADDYEAR("1999-01-01", 100)	Base date + 100 years	2099-01-01	Adds years to a date
BASE64ENCODE	BASE64ENCODE("Encode this string")	Plain text	RW5jb2RlIHRoaXMgdGV4dA==	Encodes string in Base64
BASEURL	BASEURL()	Salesforce org	Org URL	Returns Salesforce base URL
GENERATEGLOBALKEY	GENERATEGLOBALKEY("ORD")	Prefix = ORD	ORD-xxxxx	Generates a unique key
ISBLANK	ISBLANK(var:Name)	Name = null	true	Checks if value is blank
ISNOTBLANK	ISNOTBLANK(var:Name)	Name = "Bob"	true	Checks if value is not blank
COUNTQUERY	COUNTQUERY("SELECT COUNT() FROM Case WHERE AccountId = '{0}'", Id)	Account has 5 cases	5	Executes SOQL count query
DATETIMETOUNIX	DATETIMETOUNIX('11/30/2016 07:15:34')	DateTime value	1480490134000	Converts DateTime to epoch milliseconds
DESERIALIZE	DESERIALIZE("{\"key\":\"value\"}")	JSON string	{ "key": "value" }	Converts JSON string to object
ROUND (DOWN)	ROUND(2.572, 2, DOWN)	2.572	2.57	Rounds toward zero