Calculations

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

Basic Math: SUM, AVERAGE, COUNT

Text Operations: CONCAT, UPPER, LOWER

Date Operations: NOW, DATEDIF, DATEVALUE

Conditional Logic: IF, AND, OR

// 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

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

AND - All conditions must be true

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.

OR - At least one condition must be true

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.

IF - Conditional logic

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

SUM - Add numbers from related records

Calculates the total sum of values in a related field.Syntax: SUM(related_field)Business Example:

// Total revenue from all orders
SUM(ORDERS."Amount")

// Total hours worked by employee
SUM(TIMESHEETS."Hours")

// Total inventory value
SUM(PRODUCTS."StockLevel" * PRODUCTS."UnitCost")

// Customer lifetime value
SUM(CUSTOMER_ORDERS."Amount")

Arguments:

related_field: Field from related records to sum

This function only works with related fields, not individual values. For adding individual values, use the ’+’ operator.

AVERAGE - Calculate mean value

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.

COUNT - Count related records

Counts the number of non-null values in a related field.Syntax: COUNT(related_field)Business Example:

// Number of orders placed
COUNT(ORDERS."ID")

// Number of completed tasks
COUNT(TASKS."CompletedDate")

// Number of active customers
COUNT(CUSTOMERS."LastLoginDate")

// Number of products in stock
COUNT(PRODUCTS."StockLevel")

Arguments:

related_field: Field from related records to count

Use the ID field to count total records, or use a specific field to count only non-null values.

COUNTIF - Count records meeting criteria

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))

COUNTUNIQUE - Count unique values

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.

MAX - Find maximum value

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.

MAX_AGGREGATE - Find maximum in related field

Finds the maximum value from a related field or calculation.Syntax: MAX_AGGREGATE(related_field)Business Example:

// Highest order amount
MAX_AGGREGATE(ORDERS."Amount")

// Best employee performance score
MAX_AGGREGATE(EMPLOYEES."PerformanceScore")

// Peak sales month
MAX_AGGREGATE(MONTHLY_SALES."Revenue")

// Highest customer satisfaction rating
MAX_AGGREGATE(SURVEYS."SatisfactionScore")

Arguments:

related_field: Field from related records to find maximum

This is the aggregate version of MAX for related data.

MIN - Find minimum value

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.

MIN_AGGREGATE - Find minimum in related field

Finds the minimum value from a related field or calculation.Syntax: MIN_AGGREGATE(related_field)Business Example:

// Lowest order amount
MIN_AGGREGATE(ORDERS."Amount")

// Shortest project duration
MIN_AGGREGATE(PROJECTS."Duration")

// Lowest inventory level
MIN_AGGREGATE(PRODUCTS."StockLevel")

// Minimum customer age
MIN_AGGREGATE(CUSTOMERS."Age")

Arguments:

related_field: Field from related records to find minimum

This is the aggregate version of MIN for related data.

ROUND - Round numbers

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).

STDEV - Calculate standard deviation

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.

SUMIF - Sum values meeting criteria

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

NOW - Current date and time

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.

DATE - Create date from components

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).

DATETIME - Create datetime from components

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.

DATEDIF - Calculate date differences

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.

DATETIME_TRUNC - Truncate datetime

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.

DATEVALUE - Convert text to date

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.

DAY - Extract day from 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.

MONTH - Extract month from date

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).

YEAR - Extract year from date

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.

HOUR - Extract hour from datetime

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).

MINUTE - Extract minute from datetime

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.

SECOND - Extract second from datetime

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.

WEEKDAY - Get day of week

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

CONCAT - Join text together

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.

UPPER - Convert to uppercase

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.

LOWER - Convert to lowercase

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.

LEFT - Extract from start of text

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.

RIGHT - Extract from end of text

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.

MID - Extract from middle of text

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.

FIND - Find text position (case-sensitive)

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.

SEARCH - Find text position (case-insensitive)

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.

SUBSTITUTE - Replace text

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.

TRIM - Remove extra spaces

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.

LEN - Get text length

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.

STRING_AGG - Concatenate related values

Concatenates multiple related strings with a delimiter.Syntax: STRING_AGG(related_field, delimiter, [ORDER BY field])Business Example:

// List all order items
STRING_AGG(ORDER_ITEMS."ProductName", ', ')

// Create skill list for employee
STRING_AGG(EMPLOYEE_SKILLS."SkillName", ', ')

// List customer tags
STRING_AGG(CUSTOMER_TAGS."TagName", ', ' ORDER BY CUSTOMER_TAGS."TagName")

Arguments:

related_field: Field from related records to concatenate
delimiter: Text to put between each value
ORDER BY field: [OPTIONAL] Field to sort by before concatenating

Great for creating comma-separated lists from related data.

STRING_AGG_UNIQUE - Concatenate unique values

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.

SPLIT - Split text into array

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.

TEXT - Convert to text

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.

VALUE - Convert text to number

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.

REP - Repeat text

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.

REGEXEXTRACT - Extract with regex

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.

REGEXMATCH - Test regex pattern

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.

REGEXREPLACE - Replace with regex

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.

JSON_ESCAPE - Escape JSON string

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.

JSON_UNESCAPE - Unescape JSON string

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

POWER - Raise to power

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.

SQRT - Square root

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

TRUE - Boolean TRUE

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.

FALSE - Boolean FALSE

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.

BLANK - Return blank value

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.

ISBLANK - Test if blank

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.

UUID - Generate unique ID

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

Common Error: Blank Results

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

Common Error: Function Not Working with Current Record

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.

Common Error: Date Calculation Issues

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'))

Common Error: Text Function Confusion

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")

Common Error: Field Reference Issues

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"

Performance Issues

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

Overview

Workflow Orchestration

Agents & AI

Data

Security

Quick Start

Function Reference

Business Examples

Troubleshooting

Quick Start

Most Used Functions

Business Examples

Calculate Monthly Sales Performance

Function Reference

Logical Functions

Numeric Functions

Date and Time Functions

Text Functions

Send API Request

JSON File Reader

Send API Request

JSON File Reader

Mathematical Functions

Special Functions

Troubleshooting

Best Practices

Naming and Structure

Data Validation

Performance Optimization

Error Prevention

Need More Help?

Community Forum

Support Tickets

Best Practices Guide

Video Tutorials

Overview

Workflow Orchestration

Agents & AI

Data

Security

Quick Start

Function Reference

Business Examples

Troubleshooting

​Quick Start

​Most Used Functions

​Business Examples

​Calculate Monthly Sales Performance

​Function Reference

​Logical Functions

​Numeric Functions

​Date and Time Functions

​Text Functions

​Mathematical Functions

​Special Functions

​Troubleshooting

​Best Practices

​Naming and Structure

​Data Validation

​Performance Optimization

​Error Prevention

​Need More Help?

Community Forum

Support Tickets

Best Practices Guide

Video Tutorials

Quick Start

Most Used Functions

Business Examples

Calculate Monthly Sales Performance

Function Reference

Logical Functions

Numeric Functions

Date and Time Functions

Text Functions

Mathematical Functions

Special Functions

Troubleshooting

Best Practices

Naming and Structure

Data Validation

Performance Optimization

Error Prevention

Need More Help?