Formulas

Formula fields let you create computed columns that automatically calculate values based on other fields in the same record. Formulas update in real time when referenced fields change.

Syntax Basics

Referencing Fields

Wrap field names in curly braces to reference them in a formula:

{FieldName}

If the field name contains spaces:

{First Name}

Literals

Numbers: 42, 3.14, -10
Strings: "hello", 'world'
Booleans: TRUE(), FALSE() (use the functions, not bare keywords)

Operators

Operator	Description	Example
`+`	Addition	`{Price} + {Tax}`
`-`	Subtraction	`{Revenue} - {Cost}`
`*`	Multiplication	`{Price} * {Quantity}`
`/`	Division	`{Total} / {Count}`
`%`	Modulo	`{Number} % 2`
`>`	Greater than	`{Age} > 18`
`<`	Less than	`{Score} < 50`
`>=`	Greater than or equal	`{Stock} >= 0`
`<=`	Less than or equal	`{Amount} <= 1000`
`=`	Equal	`{Status} = "Active"`
`!=`	Not equal	`{Type} != "Internal"`
`&`	String concatenation	`{First} & " " & {Last}`

Parentheses

Use parentheses to control evaluation order:

({Price} + {Tax}) * {Quantity}

Numeric Functions

Function	Syntax	Description
`SUM`	`SUM(value1, value2, ...)`	Sum of all values. Accepts linked/lookup fields with multiple values.
`AVERAGE`	`AVERAGE(value1, value2, ...)`	Arithmetic mean.
`MAX`	`MAX(value1, value2, ...)`	Largest value. Works with numbers and dates.
`MIN`	`MIN(value1, value2, ...)`	Smallest value. Works with numbers and dates.
`ROUND`	`ROUND(value, precision)`	Round to N decimal places. `ROUND(3.456, 2)` returns `3.46`.
`ROUNDUP`	`ROUNDUP(value, precision)`	Round away from zero. `ROUNDUP(3.2, 0)` returns `4`.
`ROUNDDOWN`	`ROUNDDOWN(value, precision)`	Round toward zero. `ROUNDDOWN(3.9, 0)` returns `3`.
`CEILING`	`CEILING(value, places)`	Round up to the given decimal places.
`FLOOR`	`FLOOR(value, places)`	Round down to the given decimal places.
`EVEN`	`EVEN(value)`	Round up to the nearest even integer.
`ODD`	`ODD(value)`	Round up to the nearest odd integer.
`INT`	`INT(value)`	Truncate to integer (floor).
`ABS`	`ABS(value)`	Absolute value.
`SQRT`	`SQRT(value)`	Square root.
`POWER`	`POWER(base, exponent)`	Raise base to exponent. `POWER(2, 3)` returns `8`.
`EXP`	`EXP(value)`	e raised to the power of value.
`LOG`	`LOG(value, base)`	Logarithm. Base defaults to 10 if omitted.
`MOD`	`MOD(value, divisor)`	Remainder after division.
`VALUE`	`VALUE(string)`	Parse a string into a number. Strips non-numeric characters.

Text Functions

Function	Syntax	Description
`CONCATENATE`	`CONCATENATE(str1, str2, ...)`	Join strings together.
`LEN`	`LEN(string)`	Length of the string in characters.
`LOWER`	`LOWER(string)`	Convert to lowercase.
`UPPER`	`UPPER(string)`	Convert to uppercase.
`TRIM`	`TRIM(string)`	Remove leading and trailing whitespace.
`LEFT`	`LEFT(string, count)`	First N characters. `LEFT("Hello", 2)` returns `"He"`.
`RIGHT`	`RIGHT(string, count)`	Last N characters. `RIGHT("Hello", 2)` returns `"lo"`.
`MID`	`MID(string, start, count)`	Extract substring. `MID("Hello", 1, 3)` returns `"ell"`.
`FIND`	`FIND(search, text, start)`	Position of first occurrence (1-based). Returns 0 if not found.
`SEARCH`	`SEARCH(search, text, start)`	Like FIND but returns null if not found.
`REPLACE`	`REPLACE(text, start, count, new)`	Replace characters by position.
`REGEXP_REPLACE`	`REGEXP_REPLACE(text, pattern, replacement)`	Replace using a regular expression pattern.
`SUBSTITUTE`	`SUBSTITUTE(text, old, new, index)`	Replace occurrences of a substring. Optional index to replace only the Nth occurrence.
`REPT`	`REPT(string, count)`	Repeat a string N times.
`T`	`T(value)`	Convert to text. Returns null for non-string values.
`ENCODE_URL_COMPONENT`	`ENCODE_URL_COMPONENT(string)`	URL-encode a string.

Logical Functions

Function	Syntax	Description
`IF`	`IF(condition, if_true, if_false)`	Returns one of two values based on a condition.
`SWITCH`	`SWITCH(expr, case1, val1, case2, val2, ..., default)`	Match an expression against cases and return the corresponding value.
`AND`	`AND(expr1, expr2, ...)`	Returns `true` if all arguments are truthy.
`OR`	`OR(expr1, expr2, ...)`	Returns `true` if any argument is truthy.
`XOR`	`XOR(expr1, expr2, ...)`	Returns `true` if an odd number of arguments are truthy.
`NOT`	`NOT(expr)`	Inverts a boolean value.
`BLANK`	`BLANK()`	Returns a null/empty value. Useful as a placeholder.
`ERROR`	`ERROR(message)`	Throws a formula error with a custom message.
`IS_ERROR`	`IS_ERROR(expr)`	Returns `true` if the expression results in an error.

Date and Time Functions

Function	Syntax	Description
`TODAY`	`TODAY()`	Current date (start of day).
`NOW`	`NOW()`	Current date and time.
`YEAR`	`YEAR(date)`	Extract the year.
`MONTH`	`MONTH(date)`	Extract the month (1-12).
`DAY`	`DAY(date)`	Extract the day of the month.
`HOUR`	`HOUR(date)`	Extract the hour (0-23).
`MINUTE`	`MINUTE(date)`	Extract the minute.
`SECOND`	`SECOND(date)`	Extract the second.
`WEEKNUM`	`WEEKNUM(date)`	ISO week number of the year.
`WEEKDAY`	`WEEKDAY(date, startDay)`	Day of the week (0=Sunday by default). Pass `"monday"` as second arg to start weeks on Monday.
`FROMNOW`	`FROMNOW(date, unit, isFloat)`	Absolute difference between the date and now. Units: `"day"`, `"hour"`, `"minute"`, `"month"`, `"year"`.
`TONOW`	`TONOW(date, unit, isFloat)`	Same as FROMNOW (alias).
`DATETIME_DIFF`	`DATETIME_DIFF(date1, date2, unit, isFloat)`	Difference between two dates in the specified unit.
`DATE_ADD`	`DATE_ADD(date, count, unit)`	Add time to a date. `DATE_ADD({DueDate}, 7, "day")`.
`DATESTR`	`DATESTR(date)`	Format date as `YYYY-MM-DD` string.
`TIMESTR`	`TIMESTR(date)`	Format time as `HH:mm:ss` string.
`DATETIME_FORMAT`	`DATETIME_FORMAT(date, format)`	Format a date using a custom format string (e.g., `"YYYY-MM-DD HH:mm"`).
`DATETIME_PARSE`	`DATETIME_PARSE(string, format)`	Parse a date string using a custom format.
`WORKDAY`	`WORKDAY(startDate, numDays, holidays)`	Add business days to a date, skipping weekends and optional holidays.
`WORKDAY_DIFF`	`WORKDAY_DIFF(startDate, endDate, holidays)`	Count business days between two dates.
`IS_SAME`	`IS_SAME(date1, date2, unit)`	Check if two dates are the same at the given granularity.
`IS_AFTER`	`IS_AFTER(date1, date2, unit)`	Check if date1 is after date2.
`IS_BEFORE`	`IS_BEFORE(date1, date2, unit)`	Check if date1 is before date2.
`CREATED_TIME`	`CREATED_TIME()`	The record's creation timestamp.
`LAST_MODIFIED_TIME`	`LAST_MODIFIED_TIME(field1, ...)`	The record's last modification timestamp. Optionally pass field references to watch specific fields.

Array Functions

These functions work with multi-value fields (Lookup, Link, Multiple Select):

Function	Syntax	Description
`COUNTALL`	`COUNTALL(field)`	Count total elements including empty ones.
`COUNTA`	`COUNTA(values...)`	Count non-empty values.
`COUNT`	`COUNT(values...)`	Count numeric values.
`ARRAY_JOIN`	`ARRAY_JOIN(field, separator)`	Join multi-value field into a string. Default separator: `", "`.
`ARRAY_UNIQUE`	`ARRAY_UNIQUE(values...)`	Return only unique values.
`ARRAY_FLATTEN`	`ARRAY_FLATTEN(values...)`	Flatten nested arrays into a single list.
`ARRAY_COMPACT`	`ARRAY_COMPACT(values...)`	Remove empty/blank values.

System Functions

Function	Syntax	Description
`RECORD_ID`	`RECORD_ID()`	The unique ID of the current record.
`AUTO_NUMBER`	`AUTO_NUMBER()`	The auto-incrementing number of the current record.

Examples

Calculate a total with tax

{Price} * {Quantity} * (1 + {TaxRate} / 100)

Full name from parts

CONCATENATE({FirstName}, " ", {LastName})

Or using the & operator:

{FirstName} & " " & {LastName}

Status label based on value

IF({Amount} > 10000, "Enterprise",
  IF({Amount} > 1000, "Business", "Starter"))

Days until deadline

DATETIME_DIFF({DueDate}, NOW(), "day")

Days overdue (only if past due)

IF(IS_AFTER(NOW(), {DueDate}, "day"),
  DATETIME_DIFF(NOW(), {DueDate}, "day"),
  0)

Business days remaining

WORKDAY_DIFF(TODAY(), {DueDate})

Conditional text with SWITCH

SWITCH({Priority},
  "High", "Urgent - handle immediately",
  "Medium", "Normal priority",
  "Low", "Can wait",
  "Unknown priority")

Combine multi-value lookup into text

ARRAY_JOIN(ARRAY_UNIQUE({Tags}), ", ")

Extract domain from email

RIGHT({Email}, LEN({Email}) - FIND("@", {Email}))

Tips

Circular references: A formula cannot reference itself or create a circular dependency. The system detects and prevents this.
Null handling: Most functions return null if any required input is null. Use IF({Field}, {Field}, 0) or BLANK() to handle missing values.
Type coercion: The return type of a formula is automatically inferred. If you mix types (e.g., number and string in IF branches), the result becomes a string.
Performance: Formulas are computed at the database level when possible, so they are efficient even on large tables.
Formatting: After creating a formula, configure the formatting in field settings (decimal places for numbers, date format for dates, etc.).