Returns the date after adding a number of days to the given date.
ADDDATE(date, days)Arguments. date; days.
Example
Estimate the delivery date using calendar days.
ADDDATE([Order Date], [Shipping Days])
Calculate totals, work with dates and connect records across tables. Find the syntax and examples you need for your next formula.
A formula field calculates a value for each record. Select fields from the editor so Cynexio can resolve their names and types. The examples use common order, invoice and project fields. Field and table names are illustrative; select the matching fields from your own app.
[Unit Price] * [Quantity]If Unit Price is 12.50 and Quantity is 4, the result is 50.
IF([Stock Available] >= [Order Quantity], "Ready to ship", "Backorder")Returns “Ready to ship” when available stock covers the order quantity.
Use Test on first row to preview a result without changing records. Save the formula in the editor, then save the field settings to persist the change.
Field names refer to values in the current row. Use double quotes for text, commas between function arguments and parentheses to control calculation order. Optional arguments appear in square brackets in function signatures; those brackets are documentation notation.
| Operator | Meaning | Example |
|---|---|---|
| + - * / | Arithmetic | ([Unit Price] * [Quantity]) / 2 |
| = != > >= < <= | Compare values | [Quantity] >= 3 |
| & | Join text | [First Name] & " " & [Last Name] |
| AND / OR / NOT | Combine conditions | AND([Quantity] > 0, [Unit Price] > 0) |
[Order Date] + [Shipping Days][Delivery Date] - 3Use the first formula to estimate delivery from the order date. Use the second to schedule a reminder three days before delivery.
Date arithmetic includes weekends and holidays. If Order Date is September 10, 2026 and Shipping Days is 3, [Order Date] + [Shipping Days] returns September 13. The offset must be a whole number; a blank date or offset returns a blank result.
WORKDAY([Order Date], 3)Moves forward three working days, skipping weekends and configured holidays. An order placed on Thursday, September 10, 2026 finishes three working days of processing on Tuesday, September 15. A negative offset moves backward; zero keeps the starting date.
Use a table as the receiver of COUNTIF to count matching records. For example, your current table and Tasks both have a Project relation field pointing to the same project records.
Tasks.COUNTIF(@CurrentValue.Project = Project)@CurrentValue.Project is the project on the task record being checked. The unqualified Project is the project on your current row. If five task records match, the result is 5.
Tasks.COUNTIF(AND(@CurrentValue.Project = Project, @CurrentValue.Status = "Completed"))If two of those five matching tasks have Status set to Completed, the result is 2. A single-select comparison accepts an unambiguous option label such as “Completed”, as well as the stored option key.
Browse the functions registered in Cynexio’s engine. Each entry includes its syntax, description and reference example. Use the syntax and argument types shown for each function.
112 functions
Returns the date after adding a number of days to the given date.
ADDDATE(date, days)Arguments. date; days.
Estimate the delivery date using calendar days.
ADDDATE([Order Date], [Shipping Days])
Converts numbers representing year, month, and day into a date.
DATE(year, month, day)Arguments. year, month, day (numbers or field refs).
Build the first date of a fiscal year that starts in January.
DATE([Fiscal Year], 1, 1)
Returns the difference between two dates in days, months, or years.
DATEDIF(start_date, end_date, unit)Arguments. start_date, end_date (date values); unit: "Y" years, "M" months, "D" days.
Calculate completed years of service for an employee.
DATEDIF([Hire Date], TODAY(), "Y")
Returns the day of the month as a number.
DAY(date)Arguments. date.
Extract the billing day of the month.
DAY([Invoice Date])
Returns the number of days between two dates.
DAYS(end_date, start_date)Arguments. end_date, start_date.
Measure the calendar days between an order and its delivery.
DAYS([Delivery Date], [Order Date])
Builds an engine duration value from days, hours, minutes and seconds.
DURATION(days, [hours], [minutes], [seconds])Arguments. days, hours, minutes, seconds.
Build a duration from a job's planned days and hours.
DURATION([Planned Days], [Planned Hours], 0, 0)
Returns the date that is a specified number of months before or after the start date.
EDATE(start_date, months)Arguments. start_date; months (positive for future, negative for past).
Calculate a contract renewal date after the agreed number of months.
EDATE([Contract Start Date], [Contract Months])
Returns the last day of the month that is a specified number of months before or after start_date.
EOMONTH(start_date, months)Arguments. start_date; months (positive future, negative past).
Find the last day of the invoice month.
EOMONTH([Invoice Date], 0)
Returns the hour component of a time value.
HOUR(time)Arguments. time.
Extract the hour of an employee's check-in.
HOUR([Check-in Time])
Returns the minute component of a time value.
MINUTE(time)Arguments. time.
Extract the minute component of an employee's check-in.
MINUTE([Check-in Time])
Returns the month number of a date.
MONTH(date)Arguments. date.
Extract the month number for invoice reporting.
MONTH([Invoice Date])
Counts working days in the inclusive interval, excluding weekends and configured holidays.
NETWORKDAYS(start_date, end_date, [holidays_or_country], [country])Arguments. start_date and end_date: Valid dates. Optional holiday values or a supported country code; the default calendar is US.
Count working days in a project schedule, including eligible start and end dates.
NETWORKDAYS([Project Start Date], [Project End Date])
Returns the current date and time.
NOW()Show the current date and time when an order formula is evaluated.
NOW()
Returns the seconds component of a time value.
SECOND(time)Arguments. time.
Extract the seconds component of a machine event timestamp.
SECOND([Machine Event Time])
Returns today's date.
TODAY()Show today's date when a delivery planning formula is evaluated.
TODAY()
Returns the day of week as a number.
WEEKDAY(date, [type])Arguments. date; type (1=Sunday start, 2=Monday start; default 1).
Identify the weekday of a delivery, with Monday numbered 1.
WEEKDAY([Delivery Date], 2)
Returns the week number of the year for a date.
WEEKNUM(date, [type])Arguments. date; type (1=Sunday start, 2=Monday start; default 1).
Assign an order to a reporting week that starts on Monday.
WEEKNUM([Order Date], 2)
Moves a date forward or backward by working days, skipping Saturday, Sunday and configured holidays.
WORKDAY(start_date, days, [holidays_or_country])Arguments. start_date: A date field or valid date. days: An integer from -10000 to 10000. holidays_or_country: Optional custom holiday values or a supported country code.
Calculate when order processing will finish, skipping weekends and configured holidays.
WORKDAY([Order Date], [Processing Days])
Returns the year of a given date.
YEAR(date)Arguments. date.
Extract the invoice year for annual reporting.
YEAR([Invoice Date])
Returns TRUE only if all arguments evaluate to TRUE; returns FALSE if any argument is FALSE.
AND(logical_expression1, [logical_expression2, ...])Arguments. logical_expression1: Boolean expression or field reference. logical_expression2: Additional boolean expressions.
Check that an order line has a positive quantity and price.
AND([Quantity] > 0, [Unit Price] > 0)
Determines whether the range contains the specified values. Note: Text substring match is not supported; use CONTAINTEXT for that.
CONTAIN(range, [value_to_find, ...])Arguments. range: A number, array, or list to search. value_to_find: One or more values to search for.
Check whether either of the two assigned markets is Japan.
CONTAIN(LIST([Primary Market], [Secondary Market]), "Japan")
Returns TRUE if the range contains all specified values.
CONTAINSALL(range, [values...])Arguments. range: Number or list range. values: Values or list to check.
Check that a shipment includes both an invoice and a packing list.
CONTAINSALL(LIST([Document 1 Type], [Document 2 Type], [Document 3 Type]), "Invoice", "Packing list")
Returns TRUE only if the range contains exactly and only the specified values.
CONTAINSONLY(range, [values...])Arguments. range: Number or list range. values: Values or list to check.
Check whether the review decisions contain only the Approved value.
CONTAINSONLY(LIST([Reviewer 1 Decision], [Reviewer 2 Decision]), "Approved")
Returns the logical value FALSE.
FALSE()Return false when stock cannot cover an order.
IF([Order Quantity] > [Stock Available], FALSE(), TRUE())
Returns one value if a condition is TRUE, and another value if it is FALSE.
IF(condition, value_if_true, [value_if_false])Arguments. condition: A boolean expression. value_if_true: Value when TRUE. value_if_false: Value when FALSE.
Show whether an order can be fulfilled from current stock.
IF([Stock Available] >= [Order Quantity], "Ready to ship", "Backorder")
Returns the original value when it is not blank; otherwise returns the fallback.
IFBLANK(value, value_if_blank)Arguments. value: Value to return when not blank. value_if_blank: Fallback when value is blank.
Use a clear label when no company name is supplied.
IFBLANK([Company Name], "Individual customer")
Returns the value when it is not an error; otherwise returns the fallback.
IFERROR(value, value_if_error)Arguments. value: The value to return when no error. value_if_error: Return value when an error occurs.
Show a review message when average selling price cannot be calculated.
IFERROR([Revenue] / [Units Sold], "Check units sold")
Evaluates multiple conditions and returns the value corresponding to the first condition that evaluates to TRUE.
IFS(condition1, value1, [condition2, ...], [value2, ...])Arguments. condition1/value1: First test and result; then additional condition/value pairs.
Assign a collection action based on how overdue an invoice is.
IFS([Days Overdue] > 30, "Escalate", [Days Overdue] > 0, "Follow up", TRUE, "On time")
Checks whether a value is blank; returns TRUE if blank, otherwise FALSE.
ISBLANK(value)Arguments. value: Field or literal to test.
Identify orders that still need a delivery address.
ISBLANK([Delivery Address])
Checks whether the value is an error.
ISERROR(value)Arguments. value: Value to test.
Check whether an average selling price calculation fails.
ISERROR([Revenue] / [Units Sold])
Checks whether a value is NULL; returns TRUE if NULL, otherwise FALSE.
ISNULL(value)Arguments. value: Field or literal to test.
Check whether an approval date has no value.
ISNULL([Approval Date])
Checks whether the value is a number.
ISNUMBER(value)Arguments. value: Any type.
Check whether an imported amount is a number rather than text.
ISNUMBER([Imported Amount])
Logical negation of its boolean argument.
NOT(boolean)Arguments. boolean: Any expression that evaluates to TRUE or FALSE.
Check whether a shipment has a tracking number.
NOT(ISBLANK([Tracking Number]))
Returns TRUE if any argument is TRUE; returns FALSE only when all arguments are FALSE.
OR(logical_expression1, [logical_expression2, ...])Arguments. logical_expression1: Boolean expression or field reference. logical_expression2: More boolean expressions.
Check whether a shipment goes to either supported destination.
OR([Shipping Country] = "Canada", [Shipping Country] = "United States")
Compares an expression to specified cases and returns the matching value; can include a default. Result cannot be an array.
SWITCH(expression, case1, value1, [case2 or default, ...], [value2, ...])Arguments. expression: The value to compare. case1/value1: First case and result. Additional case/value pairs or a default.
Assign an estimated transit time of 1, 5 or 10 calendar days by shipping method.
SWITCH([Shipping Method], "Express", 1, "Standard", 5, 10)
Returns the logical value TRUE.
TRUE()Mark an invoice as fully paid when its outstanding amount is zero.
IF([Outstanding Amount] = 0, TRUE(), FALSE())
Returns the Unicode character for the given code point.
CHAR(number)Arguments. number.
Put the customer name and street address on separate lines of a shipping label.
[Customer Name] & CHAR(10) & [Street Address]
Joins text strings together. Similar to CONCATENATE.
CONCAT(text1, [text2, ...])Build a customer's full name.
CONCAT([First Name], " ", [Last Name])
Concatenates multiple strings.
CONCATENATE(text1, [text2, ...])Arguments. text1: initial string; text2: additional strings.
Create a readable order label.
CONCATENATE([Order Number], " - ", [Customer Name])
Returns TRUE if text contains the search substring.
CONTAINTEXT(text, search_text)Arguments. text: string or field; search_text: substring.
Flag delivery instructions that mention fragile items.
CONTAINTEXT([Delivery Instructions], "fragile")
Returns the URL-encoded string.
ENCODEURL(text)Encode a product name for use in a URL query.
ENCODEURL([Product Name])
Finds first occurrence position starting at start_pos (1-based); returns -1 if not found.
FIND(search_for, within_text, [start_pos])Arguments. search_for (case-sensitive); within_text; start_pos default 1.
Locate the separator in a customer's email address.
FIND("@", [Email Address])Formats a template with placeholders {1},{2},... using provided values.
FORMAT(template, [values...])Create a short order summary.
FORMAT("Order {1} for {2}", [Order Number], [Customer Name])Builds an HTML link string from a URL and optional display text.
HYPERLINK(url, [display_text])Arguments. url: full address; display_text: text to display.
Build a link labeled with the invoice number.
HYPERLINK([Invoice URL], [Invoice Number])
Returns leftmost substring.
LEFT(text, [num_chars])Extract the regional prefix from an order number such as LON-2026-0142.
LEFT([Order Number], 3)
Returns length of a string.
LEN(text)Measure the length of a product description before publishing it.
LEN([Product Description])
Converts to lowercase.
LOWER(text)Normalize an email address to lowercase.
LOWER([Email Address])
Returns substring from 1-based start of given length.
MID(text, start, length)Extract 2026 from an order number such as LON-2026-0142.
MID([Order Number], 5, 4)
Extracts the first substring matching regex.
REGEXEXTRACT(text, pattern)Extract the final four digits from an order number such as LON-2026-0142.
REGEXEXTRACT([Order Number], "[0-9]{4}$")Extracts all substrings matching regex.
REGEXEXTRACTALL(text, pattern)Extract all SKU references mentioned in packing notes.
REGEXEXTRACTALL([Packing Notes], "SKU-[0-9]+")
Returns TRUE if text matches regex.
REGEXMATCH(text, pattern)Check that an order number follows the LON-2026-0142 format.
REGEXMATCH([Order Number], "^[A-Z]{3}-[0-9]{4}-[0-9]{4}$")Replaces all matches with replacement.
REGEXREPLACE(text, pattern, replacement)Remove spaces and punctuation from a phone number.
REGEXREPLACE([Phone Number], "[^0-9]", "")
Replaces part of text with new text.
REPLACE(text, position, length, new_text)Replace the three-letter regional prefix of an order number.
REPLACE([Order Number], 1, 3, "NYC")
Returns rightmost substring.
RIGHT(text, [num_chars])Extract the sequence number from an order number such as LON-2026-0142.
RIGHT([Order Number], 4)
Splits text by delimiter.
SPLIT(text, delimiter)Split a semicolon-separated recipient list into individual addresses.
SPLIT([Recipient Emails], ";")
Replaces occurrences of old_text; when instance_num provided, only that occurrence.
SUBSTITUTE(text, old_text, new_text, [instance_num])Update the edition label in a product name.
SUBSTITUTE([Product Name], "Limited Edition", "Special Edition")
Converts value to text using format such as "YYYY/MM/DD", "MM/DD HH:MM", "DDDD", "DDD", "0.0%".
TEXT(value, format)Format an invoice date for a customer-facing label.
TEXT([Invoice Date], "YYYY/MM/DD")
Converts text to date/time.
TODATE(text)Convert an imported order-date string into a date value.
TODATE([Imported Order Date])
Removes leading, trailing and repeated spaces.
TRIM(text)Remove extra spaces from a customer name.
TRIM([Customer Name])
Converts to uppercase.
UPPER(text)Normalize a product SKU to uppercase.
UPPER([Product SKU])
Returns the absolute value of a number.
ABS(number)Arguments. number: The value to return as a non-negative number.
Show the size of a budget difference, whether spending is above or below budget.
ABS([Budget Variance])
Returns the arccosine of a number, in radians.
ACOS(number)Arguments. number: The value to compute.
Calculate a beam angle in degrees from its measured lengths.
ACOS([Adjacent Length] / [Beam Length]) * 180 / PI()
Returns the inverse hyperbolic cosine of a number.
ACOSH(number)Arguments. number: The value to compute.
Recover the dimensionless position from a catenary cable height ratio of at least 1.
ACOSH([Normalized Cable Height])
Returns the arcsine of a number, in radians.
ASIN(number)Arguments. number: The value to compute.
Calculate a ramp angle in degrees.
ASIN([Rise] / [Ramp Length]) * 180 / PI()
Returns the inverse hyperbolic sine of a number.
ASINH(number)Arguments. number: The value to compute.
Transform positive and negative sales variances for a chart with a compressed scale.
ASINH([Sales Variance])
Returns the arctangent of a number, in radians.
ATAN(number)Arguments. number: The value to compute.
Convert a measured slope to an angle in degrees.
ATAN([Rise] / [Run]) * 180 / PI()
Returns the arctangent for the given X and Y coordinates.
ATAN2(x, y)Arguments. x: The X coordinate value. y: The Y coordinate value.
Calculate the direction of a surveyed point from its X and Y offsets.
ATAN2([East Offset], [North Offset]) * 180 / PI()
Returns the inverse hyperbolic tangent of a number.
ATANH(number)Arguments. number: The value to compute.
Apply a Fisher transform to a correlation strictly between -1 and 1.
ATANH([Correlation])
Returns the arithmetic mean of values.
AVERAGE(value1, [value2, ...])Arguments. value1: First number or field. value2: Additional numbers or fields.
Calculate average monthly revenue for the first quarter.
AVERAGE([January Revenue], [February Revenue], [March Revenue])
Rounds a number up to the nearest multiple of factor.
CEILING(value, [factor])Arguments. value: Number to round up. factor: Optional rounding multiple (default 1, cannot be 0).
Round an order quantity up to a full pack multiple.
CEILING([Order Quantity], [Pack Size])
Returns the cosine of an angle in radians.
COS(angle)Arguments. angle: The angle in radians.
Calculate a beam's horizontal projection from its angle in degrees.
[Beam Length] * COS([Beam Angle] * PI() / 180)
Returns the hyperbolic cosine of a real number.
COSH(number)Arguments. number: The value to compute.
Calculate the support-to-center height difference of a symmetric catenary cable.
[Cable Parameter] * (COSH([Span] / (2 * [Cable Parameter])) - 1)
Counts values from a supplied list or receiver. The engine accepts at most one argument.
COUNT([values])Arguments. values: Optional collection, array or scalar value.
Count all records in the Orders table.
Orders.COUNT()
Counts non-empty values in a dataset.
COUNTA(value1, [value2, ...])Arguments. value1: First value or referenced field. value2: Additional values or referenced fields.
Count how many contact methods have been filled in.
COUNTA([Email Address], [Phone Number])
Counts records in a table for which the condition is true.
table.COUNTIF(condition)Arguments. table: The table to scan. condition: One expression evaluated for each record; use AND or OR for multiple conditions.
Count completed tasks for the project on the current row.
Tasks.COUNTIF(AND(@CurrentValue.Project = [Project], @CurrentValue.Status = "Completed"))
Rounds a number down to the nearest multiple of factor.
FLOOR(value, [factor])Arguments. value: Number to round down. factor: Optional rounding multiple (default 1, cannot be 0).
Find how many units can be supplied in complete packs.
FLOOR([Stock Available], [Pack Size])
Rounds a number down to the nearest integer less than or equal to it.
INT(number)Arguments. number: The value to compute.
Calculate the number of complete packs available for positive stock quantities.
INT([Stock Available] / [Pack Size])
Returns TRUE if a number is odd, otherwise FALSE.
ISODD(number)Arguments. number: The value to test.
Identify odd-numbered weeks for an alternating maintenance schedule.
ISODD([Week Number])
Returns the largest value in a dataset.
MAX(value1, [value2, ...])Arguments. value1: First value or referenced field. value2: Additional values or referenced fields.
Find the highest supplier quote.
MAX([Supplier A Quote], [Supplier B Quote], [Supplier C Quote])
Returns the median of a dataset.
MEDIAN(value1, [value2, ...])Arguments. value1: First value or referenced field. value2: Additional values or referenced fields.
Find the middle daily sales value for the workweek.
MEDIAN([Monday Sales], [Tuesday Sales], [Wednesday Sales], [Thursday Sales], [Friday Sales])
Returns the smallest value in a dataset.
MIN(value1, [value2, ...])Arguments. value1: First value or referenced field. value2: Additional values or referenced fields.
Find the lowest supplier quote.
MIN([Supplier A Quote], [Supplier B Quote], [Supplier C Quote])
Returns the remainder after division.
MOD(dividend, divisor)Arguments. dividend: Number to be divided. divisor: Number to divide by.
Find the units left over after filling complete packs.
MOD([Order Quantity], [Pack Size])
Returns the mathematical constant pi (3.14159265358979).
PI()Arguments. none.
Calculate the circular base area of a storage tank.
PI() * POWER([Tank Radius], 2)
Returns a number raised to a power.
POWER(base, exponent)Arguments. base: The base number. exponent: The power to raise the base to.
Estimate compound growth using an annual rate stored as a decimal, such as 0.05.
[Principal] * POWER(1 + [Annual Rate], [Years])
Returns the integer portion of a division result.
QUOTIENT(dividend, divisor)Arguments. dividend: Number to be divided. divisor: Number to divide by.
Calculate the number of complete packs in a positive order quantity.
QUOTIENT([Order Quantity], [Pack Size])
Generates a random integer within the inclusive range.
RANDOMBETWEEN(min_integer, max_integer, [volatile])Arguments. min_integer: Inclusive lower bound. max_integer: Inclusive upper bound. volatile: Accepted for compatibility; it currently does not change evaluation behavior.
Draw a random ticket number within the issued range.
RANDOMBETWEEN(1, [Ticket Count])
Returns the rank of a value within a dataset.
RANK(value, range, [ascending])Arguments. value: Value to rank. range: Dataset to compare. ascending: TRUE for ascending rank, FALSE for descending (default FALSE).
Rank the current sales record by revenue, highest first.
RANK([Revenue], Sales.[Revenue])
Returns the unique ID of the current record.
RECORD_ID()Arguments. none.
Display the unique identifier of the current order record for reconciliation.
RECORD_ID()
Rounds a number to the specified number of digits.
ROUND(number, digits)Arguments. number: Value to round. digits: Decimal places (1 for one decimal place, 0 for integer, -1 for tens place).
Round an order line total to two decimal places.
ROUND([Unit Price] * [Quantity], 2)
Rounds a number down toward zero to a specified number of digits.
ROUNDDOWN(number, digits)Arguments. number: Value to round. digits: Decimal places (1 for one decimal place, 0 for integer, -1 for tens place).
Discard fractional reward points for a non-negative balance.
ROUNDDOWN([Reward Points], 0)
Rounds a number up away from zero to a specified number of digits.
ROUNDUP(number, digits)Arguments. number: Value to round. digits: Decimal places (1 for one decimal place, 0 for integer, -1 for tens place).
Calculate how many parcels are needed for a shipment.
ROUNDUP([Shipment Weight] / [Weight per Parcel], 0)
Generates a numeric sequence from start to end.
SEQUENCE(start, end, [step])Arguments. start: First number in the sequence. end: Last number in the sequence. step: Optional increment/decrement (default depends on start and end).
Generate installment numbers from 1 through the planned count.
SEQUENCE(1, [Installment Count])
Returns the sine of an angle in radians.
SIN(angle)Arguments. angle: The angle in radians.
Calculate a beam's vertical projection from its angle in degrees.
[Beam Length] * SIN([Beam Angle] * PI() / 180)
Returns the hyperbolic sine of a real number.
SINH(number)Arguments. number: The value to compute.
Calculate the length of a symmetric catenary cable over a specified span.
2 * [Cable Parameter] * SINH([Span] / (2 * [Cable Parameter]))
Returns the total of numeric values.
SUM(value1, [value2, ...])Arguments. value1: First value or referenced column. value2: Additional values or referenced columns.
Calculate the combined cost of fulfilling an order.
SUM([Materials Cost], [Labor Cost], [Shipping Cost])
Returns the sum of values in the target range that satisfy a condition.
range.SUMIF(condition)Arguments. range: Target field or list to sum. condition: Condition used to test each item (use @CurrentValue for row-by-row checks).
Sum positive outstanding invoice balances.
Invoices.[Outstanding Amount].SUMIF(@CurrentValue > 0)
Returns the tangent of an angle in radians.
TAN(angle)Arguments. angle: The angle in radians.
Calculate the rise of a ramp from its run and angle in degrees.
[Run] * TAN([Ramp Angle] * PI() / 180)
Returns the hyperbolic tangent of a real number.
TANH(number)Arguments. number: The value to compute.
Compress a demand score into the interval between -1 and 1 for a planning model.
TANH([Normalized Demand Score])
Converts a numeric text string (such as currency, date, or time text) to a number.
VALUE(text)Arguments. text: The text value to convert.
Convert an imported price such as $49.90 into a number.
VALUE([Imported Unit Price])
Joins elements of an array/list into a string using the delimiter. Typically used with LIST.
ARRAYJOIN(array, [delimiter])Arguments. array; delimiter (default ",").
Build a mailing address from three address fields.
ARRAYJOIN(LIST([Street Address], [City], [Postal Code]), ", ")
Filters records that satisfy a predicate. The result can be counted, sorted or projected to a field.
range.FILTER(predicate)Arguments. range: A table or record list. predicate: A condition using @CurrentValue for the record being checked.
Get invoice totals for the customer on the current row.
Orders.FILTER(@CurrentValue.Customer = [Customer]).[Invoice Total]
Returns the first value of a list or field.
FIRST(list)Get the lowest of three numeric supplier quotes.
FIRST(SORT(LIST([Supplier A Quote], [Supplier B Quote], [Supplier C Quote])))
Returns the last value of a list or field.
LAST(list)Get the highest of three numeric supplier quotes.
LAST(SORT(LIST([Supplier A Quote], [Supplier B Quote], [Supplier C Quote])))
Returns a list that can contain values or nested lists.
LIST([values...])Collect first-quarter revenue figures into a list.
LIST([January Revenue], [February Revenue], [March Revenue])
Concatenates multiple lists/fields and returns the combined sequence.
LISTCOMBINE(field1, [field2, ...])Combine contact addresses into one list.
LISTCOMBINE(LIST([Primary Email], [Billing Email]), LIST([Shipping Email]))
Looks up data based on a matching field and returns values from another field.
LOOKUP(search_value, match_field, return_field, [mode])Arguments. search_value (field/value in current table); match_field [Table].[Field]; return_field [Table].[Field]; mode (1 split multi-selects, 0 treat as whole array; default 1).
Look up a product's unit price using its SKU.
LOOKUP([Product SKU], Products.[Product SKU], Products.[Unit Price])
Transforms each item in a receiver range and returns a list of results.
range.MAP(mapping_formula)Arguments. range: The receiver list. mapping_formula: One expression using @CurrentValue for the current item.
Calculate invoice totals including a 10% surcharge.
Orders.[Invoice Total].MAP(@CurrentValue * 1.1)
Returns the N-th (1-based) value from a list/field.
list.NTH(n)Get the second-lowest supplier quote.
SORT(LIST([Supplier A Quote], [Supplier B Quote], [Supplier C Quote])).NTH(2)
Returns a random element from a list.
RANDOMITEM(list, [volatile])Arguments. list: A collection of candidate values. volatile: Accepted for compatibility; it does not persist a one-time result.
Choose a contact channel for a randomized outreach experiment.
RANDOMITEM(LIST("Email", "Phone", "Video call"))Sorts values in a list (numeric).
SORT(list, [ascending])Arguments. list; ascending: TRUE ascending, FALSE descending; default TRUE.
Sort numeric supplier quotes from lowest to highest.
SORT(LIST([Supplier A Quote], [Supplier B Quote], [Supplier C Quote]))
Sorts records by a column in the range, and returns the specified return column.
range.SORTBY(sort_by_column, [ascending]).return_columnArguments. range: A table or filtered record list. sort_by_column: A field on the same table, such as @CurrentValue.[Order Date]. ascending defaults to TRUE.
List invoice totals in ascending order of order date.
Orders.SORTBY(@CurrentValue.[Order Date]).[Invoice Total]
Removes duplicates from values and returns unique items.
UNIQUE(value1, [value2, ...])Build a list of distinct customer email addresses from orders.
UNIQUE(Orders.[Customer Email])
Check both sides of the condition: @CurrentValue refers to the scanned table, while an unqualified field refers to your current row. Check relation targets, record IDs and the exact option label.
All listed functions are available for generation. Start a new generation with the exact field names and desired calculation. For calendar days use [Order Date] + 3; for working days use WORKDAY([Order Date], 3).
The displayed expression and its generated structure could not be reconciled. Generate again and verify that the formula references the intended fields before saving.
Check the field type, a whole-number offset and the holiday calendar. Calendar-day arithmetic and WORKDAY intentionally handle weekends differently.
Test on first row uses the table’s default row order, not the selected record or current view order. Check which values the preview used.
Save both the formula editor and field settings. Edits to source fields in the same record recalculate its formulas and notify open views. Cross-table changes do not yet guarantee automatic refresh of every dependent formula. Refresh and retest if needed.