Skip to main content
Calculations allow you to manipulate and transform data in your workflows. Use them in automations, layouts, and data processing to create dynamic, intelligent applications.

Quick Start

Get started with basic calculations

Function Reference

Complete list of all available functions

Business Examples

Real-world calculation scenarios

Troubleshooting

Common issues and solutions

​
Quick Start

New to calculations? Start with these common patterns that solve 80% of business needs.

​
Most Used Functions

// Sum all invoice amounts
SUM(INVOICES."Amount")

// Average customer rating
AVERAGE(REVIEWS."Rating")

// Count completed tasks
COUNT(TASKS."ID")

// Create full name
CONCAT(CUSTOMERS."FirstName", ' ', CUSTOMERS."LastName")

// Standardize email format
LOWER(CUSTOMERS."Email")

// Format product codes
UPPER(PRODUCTS."SKU")

// Days since order placed
DATEDIF(ORDERS."OrderDate", NOW(), 'D')

// Convert text to date
DATEVALUE(CUSTOMERS."SignupDate")

// Current timestamp
NOW()

// Customer status based on spending
IF(CUSTOMERS."TotalSpent" > 1000, 'VIP', 'Standard')

// Eligible for discount
AND(CUSTOMERS."TotalSpent" > 500, CUSTOMERS."MembershipLevel" = 'Gold')

// Needs attention
OR(CUSTOMERS."DaysSinceLastOrder" > 90, CUSTOMERS."SupportTickets" > 3)

​
Business Examples

  • Sales & Revenue
  • Customer Management
  • Inventory & Operations

​
Calculate Monthly Sales Performance

// Total monthly revenue
SUM(ORDERS."Amount")

// Average order value
AVERAGE(ORDERS."Amount")

// Top performing month
MAX_AGGREGATE(MONTHLY_SALES."Revenue")

// Sales growth percentage
ROUND((THIS_MONTH."Revenue" - LAST_MONTH."Revenue") / LAST_MONTH."Revenue" * 100, 2)

// High-value customer identification
IF(CUSTOMERS."TotalSpent" > 5000, 'Enterprise', 
   IF(CUSTOMERS."TotalSpent" > 1000, 'Premium', 'Standard'))

​
Function Reference

Functions are organized by category. Use the search function (Ctrl/Cmd + K) to quickly find specific functions.

​
Logical Functions

Tests multiple conditions and returns TRUE only if all are TRUE.Syntax: AND(condition1, condition2, ...)Business Example:
// Check if customer is eligible for discount
AND(CUSTOMERS."TotalSpent" > 500, CUSTOMERS."MembershipLevel" = 'Gold')

// Validate complete order
AND(ORDERS."PaymentStatus" = 'Paid', ORDERS."ShippingAddress" != '')

// Employee bonus eligibility
AND(EMPLOYEES."SalesTarget" <= EMPLOYEES."ActualSales", EMPLOYEES."Tenure" > 1)
Arguments:
  • condition1, condition2, ...: Logical expressions that evaluate to TRUE/FALSE
If any condition is blank, the result will be blank.
Tests multiple conditions and returns TRUE if any one is TRUE.Syntax: OR(condition1, condition2, ...)Business Example:
// Check if customer needs attention
OR(CUSTOMERS."DaysSinceLastOrder" > 90, CUSTOMERS."SupportTickets" > 3)

// Urgent order flag
OR(ORDERS."Priority" = 'High', ORDERS."CustomerType" = 'VIP')

// Product needs review
OR(PRODUCTS."StockLevel" < 10, PRODUCTS."Rating" < 3)
Arguments:
  • condition1, condition2, ...: Logical expressions that evaluate to TRUE/FALSE
If all conditions are blank, the result will be blank.
Returns different values based on a condition.Syntax: IF(condition, value_if_true, value_if_false)Business Example:
// Customer status based on spending
IF(CUSTOMERS."TotalSpent" > 1000, 'VIP', 'Standard')

// Shipping cost calculation
IF(ORDERS."Amount" > 100, 0, 9.99)

// Performance rating
IF(EMPLOYEES."SalesTarget" <= EMPLOYEES."ActualSales", 'Exceeded', 'Below Target')

// Nested conditions for customer tiers
IF(CUSTOMERS."TotalSpent" > 5000, 'Enterprise', 
   IF(CUSTOMERS."TotalSpent" > 1000, 'Premium', 'Standard'))
Arguments:
  • condition: Logical expression
  • value_if_true: Value returned when condition is TRUE
  • value_if_false: Value returned when condition is FALSE

​
Numeric Functions

Returns the numerical average of values in a related field.Syntax: AVERAGE(related_field)Business Example:
// Average customer rating
AVERAGE(REVIEWS."Rating")

// Average order value
AVERAGE(ORDERS."Amount")

// Average employee salary by department
AVERAGE(EMPLOYEES."Salary")

// Average project completion time
AVERAGE(PROJECTS."CompletionDays")
Arguments:
  • related_field: Field from related records to average
Blank values are automatically excluded from the calculation.
Counts non-null values in a related field that meet a specified condition.Syntax: COUNTIF(related_field, criterion)Business Example:
// Count high-value orders
COUNTIF(ORDERS."Amount", '>1000')

// Count 5-star reviews
COUNTIF(REVIEWS."Rating", '=5')

// Count overdue tasks
COUNTIF(TASKS."DueDate", '<' + TEXT(NOW()))

// Count products with low stock
COUNTIF(PRODUCTS."StockLevel", '<10')
Arguments:
  • related_field: Field from related records to count
  • criterion: Condition to meet (supports comparison operators)
Alternative syntax: SUM(IF(RELATED."Field" = 'Paid', 1, 0))
Counts the number of unique values in a related field.Syntax: COUNTUNIQUE(related_field)Business Example:
// Number of unique customers
COUNTUNIQUE(ORDERS."CustomerID")

// Number of different product categories
COUNTUNIQUE(PRODUCTS."Category")

// Number of unique sales reps
COUNTUNIQUE(DEALS."SalesRep")

// Number of unique support ticket types
COUNTUNIQUE(TICKETS."Type")
Arguments:
  • related_field: Field from related records to count unique values
Null values are excluded from the count.
Returns the maximum value from a given set of values.Syntax: MAX(value1, value2, ...)Business Example:
// Highest of three scores
MAX(PERFORMANCE."Q1Score", PERFORMANCE."Q2Score", PERFORMANCE."Q3Score")

// Maximum shipping cost between options
MAX(SHIPPING."Standard", SHIPPING."Express", SHIPPING."Overnight")

// Latest date from multiple fields
MAX(CUSTOMER."LastPurchase", CUSTOMER."LastContact", CUSTOMER."LastLogin")
Arguments:
  • value1, value2, ...: Values to compare
Blank values are ignored. For aggregate calculations, use MAX_AGGREGATE.
Returns the minimum value from a given set of values.Syntax: MIN(value1, value2, ...)Business Example:
// Lowest of three prices
MIN(PRICING."Standard", PRICING."Discount", PRICING."Wholesale")

// Earliest date from multiple fields
MIN(PROJECT."StartDate", PROJECT."PlannedStart", PROJECT."ActualStart")

// Minimum required inventory
MIN(PRODUCT."SafetyStock", PRODUCT."ReorderPoint", 10)
Arguments:
  • value1, value2, ...: Values to compare
Blank values are ignored. For aggregate calculations, use MIN_AGGREGATE.
Rounds a number to a specified number of decimal places.Syntax: ROUND(number, [decimal_places])Business Example:
// Round currency to 2 decimal places
ROUND(ORDERS."Amount", 2)

// Round percentage to whole number
ROUND(SALES."GrowthRate" * 100, 0)

// Round to nearest thousand
ROUND(REVENUE."Annual", -3)

// Round average rating
ROUND(AVERAGE(REVIEWS."Rating"), 1)
Arguments:
  • number: Number to round
  • decimal_places: [OPTIONAL] Number of decimal places (default: 0)
Negative decimal_places rounds to left of decimal point (e.g., -1 rounds to tens).
Calculates the standard deviation of a related field.Syntax: STDEV(related_field)Business Example:
// Variability in order amounts
STDEV(ORDERS."Amount")

// Consistency of employee performance
STDEV(EMPLOYEES."PerformanceScore")

// Product rating consistency
STDEV(REVIEWS."Rating")

// Sales performance variability
STDEV(SALES_REPS."MonthlySales")
Arguments:
  • related_field: Field from related records to calculate standard deviation
Standard deviation measures how spread out values are from the average.
Returns the sum of values in a field that meet a specified condition.Syntax: SUMIF(related_field, criterion)Business Example:
// Revenue from high-value orders
SUMIF(ORDERS."Amount", '>1000')

// Total hours for completed tasks
SUMIF(TASKS."Hours", TASKS."Status" = 'Completed')

// Revenue from premium customers
SUMIF(ORDERS."Amount", CUSTOMERS."Tier" = 'Premium')

// Sales from specific region
SUMIF(SALES."Amount", SALES."Region" = 'North')
Arguments:
  • related_field: Field from related records to sum
  • criterion: Condition values must meet
Use operators like greater than, less than, greater than or equal to, less than or equal to, and equal to in your criteria.

​
Date and Time Functions

Returns the current date and time.Syntax: NOW()Business Example:
// Timestamp for new records
NOW()

// Days since order placed
DATEDIF(ORDERS."OrderDate", NOW(), 'D')

// Current year for reporting
YEAR(NOW())

// Age calculation
DATEDIF(CUSTOMERS."BirthDate", NOW(), 'Y')
This function takes no arguments and always returns the current moment.
Returns a date value based on provided year, month, and day.Syntax: DATE(year, month, day)Business Example:
// Create fiscal year start date
DATE(YEAR(NOW()), 4, 1)

// Build date from separate fields
DATE(ORDERS."Year", ORDERS."Month", ORDERS."Day")

// Create quarter end date
DATE(2024, 3, 31)

// Generate report date
DATE(REPORTS."ReportYear", REPORTS."ReportMonth", 1)
Arguments:
  • year: Four-digit year
  • month: Month (1-12)
  • day: Day of month (1-31)
Values exceeding normal ranges automatically adjust (e.g., month 13 becomes January of next year).
Returns a datetime value in the company’s timezone.Syntax: DATETIME(year, month, day, hour, minute, second)Business Example:
// Create meeting start time
DATETIME(2024, 3, 15, 9, 30, 0)

// Build timestamp from fields
DATETIME(EVENTS."Year", EVENTS."Month", EVENTS."Day", EVENTS."Hour", 0, 0)

// Create deadline
DATETIME(TASKS."DueYear", TASKS."DueMonth", TASKS."DueDay", 23, 59, 59)
Arguments:
  • year: Four-digit year
  • month: Month (1-12)
  • day: Day of month (1-31)
  • hour: Hour (0-23)
  • minute: Minute (0-59)
  • second: Second (0-59)
Time is set in your company’s timezone.
Calculates the difference between two dates in specified units.Syntax: DATEDIF(start_date, end_date, unit)Business Example:
// Customer age
DATEDIF(CUSTOMERS."BirthDate", NOW(), 'Y')

// Days since last purchase
DATEDIF(CUSTOMERS."LastPurchaseDate", NOW(), 'D')

// Project duration in months
DATEDIF(PROJECTS."StartDate", PROJECTS."EndDate", 'M')

// Employee tenure
DATEDIF(EMPLOYEES."HireDate", NOW(), 'Y')
Arguments:
  • start_date: Beginning date
  • end_date: End date
  • unit: ‘Y’ for years, ‘M’ for months, ‘D’ for days
Returns negative values if start_date is after end_date.
Truncates a datetime to a specified unit.Syntax: DATETIME_TRUNC(datetime, unit)Business Example:
// Start of month for reporting
DATETIME_TRUNC(ORDERS."OrderDate", 'MONTH')

// Start of day for daily summaries
DATETIME_TRUNC(EVENTS."EventTime", 'DAY')

// Start of quarter
DATETIME_TRUNC(SALES."SaleDate", 'QUARTER')

// Start of year
DATETIME_TRUNC(EMPLOYEES."HireDate", 'YEAR')
Arguments:
  • datetime: Datetime to truncate
  • unit: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND
Useful for grouping data by time periods.
Converts text date value into a DATE object.Syntax: DATEVALUE(text_date)Business Example:
// Convert imported date text
DATEVALUE(IMPORTS."DateString")

// Parse date from external system
DATEVALUE(EXTERNAL."FormattedDate")

// Convert user-entered date
DATEVALUE(FORMS."SubmissionDate")
Arguments:
  • text_date: Text representation of a date
Returns null if the text cannot be parsed as a date.
Returns the day of the month (1-31) from a date.Syntax: DAY(date)Business Example:
// Extract day for daily reports
DAY(ORDERS."OrderDate")

// Get payment day
DAY(INVOICES."DueDate")

// Extract birth day
DAY(CUSTOMERS."BirthDate")
Arguments:
  • date: Date to extract day from
Returns a number between 1 and 31.
Returns the month (1-12) from a date.Syntax: MONTH(date)Business Example:
// Extract month for monthly reports
MONTH(ORDERS."OrderDate")

// Get birth month
MONTH(CUSTOMERS."BirthDate")

// Extract fiscal month
MONTH(TRANSACTIONS."TransactionDate")
Arguments:
  • date: Date to extract month from
Returns a number between 1 (January) and 12 (December).
Returns the year from a date.Syntax: YEAR(date)Business Example:
// Extract year for annual reports
YEAR(ORDERS."OrderDate")

// Get hire year
YEAR(EMPLOYEES."HireDate")

// Extract birth year
YEAR(CUSTOMERS."BirthDate")
Arguments:
  • date: Date to extract year from
Returns a four-digit year number.
Returns the hour (0-23) from a datetime.Syntax: HOUR(datetime)Business Example:
// Extract hour for time-based analysis
HOUR(ORDERS."OrderTime")

// Get meeting hour
HOUR(MEETINGS."StartTime")

// Extract login hour
HOUR(USERS."LastLogin")
Arguments:
  • datetime: Datetime to extract hour from
Returns a number between 0 (midnight) and 23 (11 PM).
Returns the minute (0-59) from a datetime.Syntax: MINUTE(datetime)Business Example:
// Extract minute for precise timing
MINUTE(MEETINGS."StartTime")

// Get appointment minute
MINUTE(APPOINTMENTS."ScheduledTime")

// Extract timestamp minute
MINUTE(EVENTS."EventTime")
Arguments:
  • datetime: Datetime to extract minute from
Returns a number between 0 and 59.
Returns the second (0-59) from a datetime.Syntax: SECOND(datetime)Business Example:
// Extract second for precise timing
SECOND(TRANSACTIONS."Timestamp")

// Get event second
SECOND(EVENTS."EventTime")

// Extract log second
SECOND(LOGS."LogTime")
Arguments:
  • datetime: Datetime to extract second from
Returns a number between 0 and 59.
Returns the day of the week (1-7) for a date.Syntax: WEEKDAY(date, [type])Business Example:
// Check if order was placed on weekend
WEEKDAY(ORDERS."OrderDate") = 1 OR WEEKDAY(ORDERS."OrderDate") = 7

// Get weekday for scheduling
WEEKDAY(MEETINGS."MeetingDate", 2)

// Analyze sales by day of week
WEEKDAY(SALES."SaleDate")
Arguments:
  • date: Date to get weekday from
  • type: [OPTIONAL] 1=Sun-Sat (1-7), 2=Mon-Sun (1-7), 3=Mon-Sun (0-6)
Type 1 (default): Sunday=1, Monday=2, …, Saturday=7

​
Text Functions

Joins multiple text values into a single string.Syntax: CONCAT(text1, text2, ...)Business Example:
// Customer full name
CONCAT(CUSTOMERS."FirstName", ' ', CUSTOMERS."LastName")

// Product description
CONCAT(PRODUCTS."Brand", ' - ', PRODUCTS."Model", ' (', PRODUCTS."Color", ')')

// Order summary
CONCAT('Order #', ORDERS."OrderNumber", ' - ', ORDERS."Status")

// Address formatting
CONCAT(CUSTOMERS."Street", ', ', CUSTOMERS."City", ', ', CUSTOMERS."State")
Arguments:
  • text1, text2, ...: Text values to join together
Various field types are automatically converted to text for concatenation.
Converts text to uppercase letters.Syntax: UPPER(text)Business Example:
// Standardize product codes
UPPER(PRODUCTS."SKU")

// Format state abbreviations
UPPER(CUSTOMERS."State")

// Consistent department names
UPPER(EMPLOYEES."Department")

// Normalize country codes
UPPER(ADDRESSES."CountryCode")
Arguments:
  • text: Text to convert to uppercase
Useful for standardizing data entry and comparisons.
Converts text to lowercase letters.Syntax: LOWER(text)Business Example:
// Standardize email addresses
LOWER(CUSTOMERS."Email")

// Consistent username format
LOWER(USERS."Username")

// Normalize search terms
LOWER(SEARCH."Query")

// Standardize domain names
LOWER(WEBSITES."Domain")
Arguments:
  • text: Text to convert to lowercase
Essential for consistent data processing and matching.
Extracts characters from the beginning of a string.Syntax: LEFT(text, number_of_characters)Business Example:
// Extract first 3 characters of product code
LEFT(PRODUCTS."SKU", 3)

// Get first initial
LEFT(CUSTOMERS."FirstName", 1)

// Extract area code from phone
LEFT(CUSTOMERS."Phone", 3)

// Get first part of order number
LEFT(ORDERS."OrderNumber", 4)
Arguments:
  • text: String to extract from
  • number_of_characters: Number of characters to extract
Returns the entire string if requested length exceeds string length.
Extracts characters from the end of a string.Syntax: RIGHT(text, number_of_characters)Business Example:
// Extract last 4 digits of credit card
RIGHT(PAYMENTS."CardNumber", 4)

// Get file extension
RIGHT(ATTACHMENTS."FileName", 4)

// Extract year from date string
RIGHT(RECORDS."DateString", 4)

// Get last part of account number
RIGHT(ACCOUNTS."AccountNumber", 6)
Arguments:
  • text: String to extract from
  • number_of_characters: Number of characters to extract
Returns the entire string if requested length exceeds string length.
Extracts substring from specified position.Syntax: MID(text, start_position, number_of_characters)Business Example:
// Extract middle digits from account number
MID(ACCOUNTS."AccountNumber", 5, 4)

// Get month from date string (MM/DD/YYYY)
MID(RECORDS."DateString", 4, 2)

// Extract product category from code
MID(PRODUCTS."SKU", 3, 2)
Arguments:
  • text: String to extract from
  • start_position: Starting position (1-based)
  • number_of_characters: Number of characters to extract
Position counting starts at 1, not 0.
Returns position of first case-sensitive substring match.Syntax: FIND(search_text, text_to_search, [start_position])Business Example:
// Find @ symbol in email
FIND('@', CUSTOMERS."Email")

// Find dash in product code
FIND('-', PRODUCTS."SKU")

// Find space in full name
FIND(' ', CUSTOMERS."FullName")
Arguments:
  • search_text: Text to find
  • text_to_search: Text to search within
  • start_position: [OPTIONAL] Starting position for search
Returns 0 if text not found. Case-sensitive search.
Returns position of first case-insensitive substring match.Syntax: SEARCH(search_text, text_to_search, [start_position])Business Example:
// Find 'premium' in product name (any case)
SEARCH('premium', PRODUCTS."Name")

// Find 'manager' in job title
SEARCH('manager', EMPLOYEES."JobTitle")

// Find 'urgent' in support ticket
SEARCH('urgent', TICKETS."Subject")
Arguments:
  • search_text: Text to find
  • text_to_search: Text to search within
  • start_position: [OPTIONAL] Starting position for search
Returns 0 if text not found. Case-insensitive search.
Replaces text occurrences in a string.Syntax: SUBSTITUTE(text, old_text, new_text)Business Example:
// Replace dashes with spaces in product codes
SUBSTITUTE(PRODUCTS."SKU", '-', ' ')

// Replace old company name in addresses
SUBSTITUTE(CUSTOMERS."Address", 'Old Corp', 'New Corp')

// Clean phone number formatting
SUBSTITUTE(CUSTOMERS."Phone", '(', '')
Arguments:
  • text: Original text
  • old_text: Text to replace
  • new_text: Replacement text
Replaces ALL occurrences of old_text with new_text.
Removes leading and trailing spaces from text.Syntax: TRIM(text)Business Example:
// Clean up imported customer names
TRIM(CUSTOMERS."Name")

// Remove spaces from product codes
TRIM(PRODUCTS."SKU")

// Clean email addresses
TRIM(CUSTOMERS."Email")

// Clean up form input
TRIM(FORMS."UserInput")
Arguments:
  • text: Text to trim
Essential for cleaning up data entry and imports.
Returns the number of characters in a string.Syntax: LEN(text)Business Example:
// Check if password meets minimum length
LEN(USERS."Password") >= 8

// Validate phone number length
LEN(CUSTOMERS."Phone") = 10

// Check product code format
LEN(PRODUCTS."SKU") = 8

// Validate input length
LEN(FORMS."Description") <= 500
Arguments:
  • text: Text to measure
Useful for data validation and formatting checks.
Concatenates unique values from a related field.Syntax: STRING_AGG_UNIQUE(related_field, delimiter)Business Example:
// List unique product categories
STRING_AGG_UNIQUE(ORDER_ITEMS."Category", ', ')

// List unique customer locations
STRING_AGG_UNIQUE(CUSTOMERS."City", ', ')

// List unique skills
STRING_AGG_UNIQUE(EMPLOYEE_SKILLS."SkillName", ', ')
Arguments:
  • related_field: Field from related records to concatenate
  • delimiter: Text to put between each value
Automatically removes duplicates before concatenating.
Splits a string into an array using a delimiter.Syntax: SPLIT(text, delimiter)Business Example:
// Split full name into parts
SPLIT(CUSTOMERS."FullName", ' ')

// Split tags by comma
SPLIT(PRODUCTS."Tags", ',')

// Split address by comma
SPLIT(CUSTOMERS."Address", ',')
Arguments:
  • text: String to split
  • delimiter: Character or string to split on
Returns an array of substrings. Empty delimiter splits into individual characters.
Converts numbers or dates to text format.Syntax: TEXT(value)Business Example:
// Convert order amount to text
TEXT(ORDERS."Amount")

// Convert date to text
TEXT(ORDERS."OrderDate")

// Convert ID to text for concatenation
TEXT(CUSTOMERS."ID")
Arguments:
  • value: Number or date to convert
Useful when you need to treat numbers as text for concatenation.
Converts text to a number.Syntax: VALUE(text)Business Example:
// Convert text amount to number
VALUE(IMPORTS."AmountText")

// Convert text quantity to number
VALUE(FORMS."QuantityInput")

// Convert text ID to number
VALUE(EXTERNAL."IDString")
Arguments:
  • text: Text to convert to number
Returns blank if text cannot be converted to a number.
Repeats a string a specified number of times.Syntax: REP(text, number_of_times)Business Example:
// Create visual indicators
REP('★', PRODUCTS."Rating")

// Create padding
REP(' ', 10)

// Create separators
REP('-', 20)
Arguments:
  • text: String to repeat
  • number_of_times: Number of repetitions
Useful for creating visual elements and formatting.
Extracts text using a regular expression pattern.Syntax: REGEXEXTRACT(text, pattern)Business Example:
// Extract phone area code
REGEXEXTRACT(CUSTOMERS."Phone", '\\((\\d{3})\\)')

// Extract email domain
REGEXEXTRACT(CUSTOMERS."Email", '@(.+)')

// Extract order number
REGEXEXTRACT(ORDERS."Reference", 'ORD-(\\d+)')
Arguments:
  • text: Text to extract from
  • pattern: Regular expression pattern
Requires knowledge of regular expressions. Use with caution.
Tests if text matches a regular expression pattern.Syntax: REGEXMATCH(text, pattern)Business Example:
// Validate email format
REGEXMATCH(CUSTOMERS."Email", '^[\\w\\.-]+@[\\w\\.-]+\\.[a-zA-Z]{2,}$')

// Check phone format
REGEXMATCH(CUSTOMERS."Phone", '^\\(\\d{3}\\) \\d{3}-\\d{4}$')

// Validate product code
REGEXMATCH(PRODUCTS."SKU", '^[A-Z]{3}-\\d{4}$')
Arguments:
  • text: Text to test
  • pattern: Regular expression pattern
Returns TRUE if pattern matches, FALSE otherwise.
Replaces text using regular expression patterns.Syntax: REGEXREPLACE(text, pattern, replacement, [case_insensitive])Business Example:
// Format phone numbers
REGEXREPLACE(CUSTOMERS."Phone", '(\\d{3})(\\d{3})(\\d{4})', '($1) $2-$3')

// Clean product codes
REGEXREPLACE(PRODUCTS."SKU", '[^A-Z0-9-]', '')

// Standardize names
REGEXREPLACE(CUSTOMERS."Name", '\\s+', ' ')
Arguments:
  • text: Text to modify
  • pattern: Regular expression pattern
  • replacement: Replacement text
  • case_insensitive: [OPTIONAL] TRUE for case-insensitive matching
Advanced feature requiring regex knowledge.
Escapes special characters in a string for use in JSON.Syntax: JSON_ESCAPE(text)Business Example:
// Escape a string with quotes and newlines
JSON_ESCAPE('Hello "world" with\nnewlines')
// Result: "Hello \\"world\\" with\\\\nnewlines"
Arguments:
  • text: Text to escape
This function is useful for safely embedding text into JSON payloads.

Send API Request

Learn how to send JSON data to external systems.

JSON File Reader

Learn how to parse and import data from JSON files.
Unescapes special characters in a JSON string.Syntax: JSON_UNESCAPE(text)Business Example:
// Unescape a JSON-escaped string
JSON_UNESCAPE('Hello \\"world\\" with\\\\nnewlines')
// Result: "Hello \"world\" with\nnewlines"
Arguments:
  • text: Text to unescape
This is the inverse of JSON_ESCAPE, useful for parsing data from JSON payloads.

Send API Request

Learn how to receive JSON data from external systems.

JSON File Reader

Learn how to read and process data from JSON files.

​
Mathematical Functions

Raises a number to a specified power.Syntax: POWER(base, exponent)Business Example:
// Calculate compound interest
POWER(1.05, YEARS."Investment")

// Calculate area of square
POWER(DIMENSIONS."Side", 2)

// Calculate exponential growth
POWER(GROWTH."Rate", PERIODS."Number")
Arguments:
  • base: Base number
  • exponent: Power to raise to
Any number raised to the power of 0 equals 1.
Calculates the square root of a number.Syntax: SQRT(number)Business Example:
// Calculate standard deviation component
SQRT(VARIANCE."Value")

// Calculate distance formula component
SQRT(COORDINATES."X" * COORDINATES."X" + COORDINATES."Y" * COORDINATES."Y")

// Calculate geometric mean component
SQRT(METRICS."Value1" * METRICS."Value2")
Arguments:
  • number: Number to find square root of
Returns null if the number is negative.

​
Special Functions

Returns the Boolean value TRUE.Syntax: TRUE()Business Example:
// Set default active status
TRUE()

// Use in conditional logic
IF(CUSTOMERS."Status" = 'Active', TRUE(), FALSE())

// Initialize flags
TRUE()
Useful for setting boolean field values and conditional logic.
Returns the Boolean value FALSE.Syntax: FALSE()Business Example:
// Set default inactive status
FALSE()

// Use in conditional logic
IF(ORDERS."Amount" > 0, TRUE(), FALSE())

// Initialize flags
FALSE()
Useful for setting boolean field values and conditional logic.
Returns a blank/null value.Syntax: BLANK()Business Example:
// Clear a field conditionally
IF(ORDERS."Status" = 'Cancelled', BLANK(), ORDERS."ShipDate")

// Set default empty value
BLANK()

// Use in conditional assignments
IF(CUSTOMERS."Type" = 'Guest', BLANK(), CUSTOMERS."LoyaltyPoints")
Represents the absence of data, different from empty string.
Tests if a value is blank/null.Syntax: ISBLANK(value)Business Example:
// Check if customer has phone number
ISBLANK(CUSTOMERS."Phone")

// Validate required fields
ISBLANK(ORDERS."ShippingAddress")

// Check for missing data
ISBLANK(PRODUCTS."Description")

// Conditional logic based on blank values
IF(ISBLANK(CUSTOMERS."Email"), 'No Email', 'Has Email')
Arguments:
  • value: Value to test for blankness
Returns TRUE if value is blank, FALSE if it contains data. Empty string (”) is NOT blank.
Generates a random UUID (Universally Unique Identifier).Syntax: UUID()Business Example:
// Generate unique transaction ID
UUID()

// Create unique reference number
UUID()

// Generate API key
UUID()
Returns a string in format: ‘f81d4fae-7dec-11d0-a765-00a0c91e6bf6’

​
Troubleshooting

Problem: Your calculation returns blank instead of expected values.Causes & Solutions:
  • Blank input data: Check that referenced fields contain data
  • Invalid field references: Ensure field names are correct and properly quoted
  • Type mismatches: Verify you’re using the right function for your data type
Example Fix:
// Instead of this (might return blank):
AVERAGE(ORDERS."Amount")

// Try this (handles blanks better):
IF(COUNT(ORDERS."Amount") > 0, AVERAGE(ORDERS."Amount"), 0)
Prevention:
  • Always test with sample data
  • Use ISBLANK() to check for missing data
  • Validate field names match exactly
Problem: Functions like SUM, COUNT, AVERAGE don’t work with fields from the current record.Solution: These functions only work with related fields. For current record calculations, use operators:
// Wrong - won't work:
SUM(CURRENT."Field1", CURRENT."Field2")

// Right - use operators:
CURRENT."Field1" + CURRENT."Field2"

// Right - for related data:
SUM(RELATED_RECORDS."Field")
Key Point: Aggregate functions (SUM, COUNT, AVERAGE, etc.) are designed for related data, not individual field operations.
Problem: Date calculations returning unexpected results.Common Fixes:
  • Text dates: Use DATEVALUE() to convert text to proper dates
  • Timezone issues: Ensure consistent timezone handling
  • Format problems: Check date format consistency
Example Fix:
// If date is stored as text:
DATEDIF(DATEVALUE(CUSTOMERS."SignupDate"), NOW(), 'D')

// For consistent date creation:
DATE(YEAR(NOW()), MONTH(NOW()), 1)

// Handle blank dates:
IF(ISBLANK(ORDERS."ShipDate"), 'Not Shipped', DATEDIF(ORDERS."OrderDate", ORDERS."ShipDate", 'D'))
Problem: FIND vs SEARCH, UPPER vs LOWER, concatenation issues.Solutions:
  • FIND: Case-sensitive search
  • SEARCH: Case-insensitive search
  • CONCAT: Joins multiple values
  • Always use single quotes for text literals
Example Fixes:
// Case-sensitive search:
FIND('Manager', EMPLOYEES."Title")

// Case-insensitive search:
SEARCH('manager', EMPLOYEES."Title")

// Proper concatenation:
CONCAT(CUSTOMERS."FirstName", ' ', CUSTOMERS."LastName")

// Wrong - don't use double quotes:
CONCAT(CUSTOMERS."FirstName", " ", CUSTOMERS."LastName")
Problem: Field references not working, getting ‘field not found’ errors.Solutions:
  • Check field names: Must match exactly (case-sensitive)
  • Use proper syntax: HANDLE.”FieldName” format
  • Verify relationships: Ensure fields are properly related
Example Fixes:
// Correct field reference:
CUSTOMERS."FirstName"

// Wrong - missing quotes:
CUSTOMERS.FirstName

// Wrong - incorrect case:
CUSTOMERS."firstname"

// For related fields:
RELATED_CUSTOMERS."FirstName"
Problem: Calculations running slowly or timing out.Solutions:
  • Simplify complex calculations: Break into smaller parts
  • Avoid nested functions: Use intermediate calculations
  • Check data volumes: Large datasets may need optimization
Example Optimization:
// Instead of nested complexity:
IF(AND(CUSTOMERS."Status" = 'Active', DATEDIF(CUSTOMERS."LastPurchase", NOW(), 'D') < 30), 'Recent', 'Old')

// Break it down:
// Step 1: Days since purchase
DATEDIF(CUSTOMERS."LastPurchase", NOW(), 'D')

// Step 2: Use result in simpler IF
IF(PREVIOUS_CALC < 30, 'Recent', 'Old')

​
Best Practices

Follow these guidelines for reliable, maintainable calculations.

​
Naming and Structure

  • Use descriptive field names that clearly indicate purpose
  • Keep calculations simple and readable
  • Break complex logic into multiple steps
  • Document complex calculations with comments

​
Data Validation

  • Always check for blank values using ISBLANK()
  • Validate data types before performing operations
  • Use IF statements to handle edge cases
  • Test calculations with various data scenarios

​
Performance Optimization

  • Avoid deeply nested functions
  • Use intermediate calculations for complex logic
  • Consider data volume when designing calculations
  • Test performance with realistic data sets

​
Error Prevention

  • Use proper field reference syntax: HANDLE.”FieldName”
  • Always use single quotes for text literals
  • Verify field relationships before using aggregate functions
  • Test calculations thoroughly before deployment
Remember: String literals should always use single quotes (”), never double quotes ("").

​
Need More Help?

Community Forum

Ask questions and share solutions with other users

Support Tickets

Get direct help from our support team

Best Practices Guide

Learn advanced calculation techniques

Video Tutorials

Watch step-by-step calculation examples
⌘I