Calculated fields syntax

Operators

Arithmetic operators

Used to perform mathematical calculations.

Operator	Description
`+`	Addition operator sums or concatenates two values.
`-`	Subscription operator subtracts the right value from the left value.
`*`	Multiplication operator multiplies two numbers.
`/`	Division operator divides the left operand by the right.

Comparison (relational) operator

Evaluates the relationship between two values.

Operator	Description
`<`	Less than
`>`	Greater than
`==`	Equals
`!=`	Not equal to

Assignment and logical operators

Set values or combine multiple conditions.

Operator	Description
`=`	Assignment operator assigns a value.
`&`	AND operator returns `True` only if both conditions are true.
`\|`	OR operator returns `True` if at least one condition is true.

Brackets and delimiters

Define the scope, priority, and data structure in the syntax.

Operator	Description
`( )`	Circular open and close brackets: Circular: Changes order of operations (parentheses first). Logic: Groups conditions in an `IF` statement. Functions: Contains arguments, for example, `print("Hello")`.
`{ }`	Curly open and close braces define a block of code. Everything inside the braces belongs together (like the body of a function or a loop).
`[ ]`	Square open and close brackets: Commonly used for arrays or lists. Indexes to get a specific item (for example, `list[0]` gets the first item).

Operator

Description

( )

Circular open and close brackets:

Circular: Changes order of operations (parentheses first).
Logic: Groups conditions in an IF statement.
Functions: Contains arguments, for example, print("Hello").

{ }

Curly open and close braces define a block of code. Everything inside the braces belongs together (like the body of a function or a loop).

[ ]

Square open and close brackets:

Commonly used for arrays or lists.
Indexes to get a specific item (for example, list[0] gets the first item).

Control flow (conditionals)

Keywords that direct the path the program takes based on the results of the comparison operators.

Operator	Description
`IF`	The starting point of a decision. It evaluates a condition.
`ELSE`	Marks the block of code that runs only if the `IF` condition is `True`.
`THEN`	Runs the block of syntax only if the `IF` condition is `False`.

Calculated field data types

When you create a calculated field, you have to select the data type for that field which determines how data is handled and how it displays in the transactions table.

Data types

The following is a description of the available data types you can select for a calculated fields.

Data type	Description	Use case	Example syntax
Date	A calendar date value without a time component.	Compare or return dates from CRM fields such as close dates or contract start dates.	`IF Accounts@LastModifiedDate > DATE("today") then "Active" else "Overdue"`
Date & Time	A combined date and time value with full timestamp precision.	Capture or compare timestamps where time of day matters, such as record creation or last modification.	`DATE("now", "Y-m-d H:i:s")`
Decimal Number (0.00)	A numeric value with decimal precision, typically two decimal places.	Calculate financial values such as commission amounts, percentages, or rates.	`Opportunities@Amount * 0.15`
Rounded Number	A numeric value rounded to the nearest whole integer.	Return whole-number results such as headcount, quantities, or unit counts.	`Opportunities@Quantity * 2`
Month From Date	The numeric month (1–12) extracted from a date field.	Group or filter transactions by month for period-based reporting.	Input: `2026-02-20` > Output: `2`
Month Name From Date	The full month name (February) extracted from a date field.	Display readable month labels in commission reports, ledger views, or exports.	Input: `2026-02-08` > Output: `February`
Paragraph Text	Multi-line or longer text output from a formula.	Return descriptive labels, concatenated values, or conditional messages that may span multiple lines.	`IF Amount > 10000 then "High Value Deal - Requires Manager Approval" else "Standard Processing"`
Year From Date	The four-digit year extracted from a date field.	Group transactions by fiscal year for annual reporting or year-over-year comparisons.	Input: `2026-02-20` > Output: `2026`

Sample formulas

The following table provides examples of different common formulas and their required syntax that you can use to create calculated fields.

Concatenation

Add two values together or combine two separate strings into a single string.

Formula: Field1 + Field2
Syntax: tablename@Field1 + tablename@Field2

Empty value handling

Check if a field is blank to provide a default value, for example, Default Multiplier.

Formula: If Field1 is empty, then 1, else 2
Syntax: if ( tablename@Field1 == "" ) then 1 else 2 (There is no comma after the 1 in the syntax.)

Discrepancy check

Flag records where two fields do not match for auditing purposes.

Formula: If Field1 does not equal Field2, then YES, else NO
Syntax: if ( tablename@Field1 != tablename@Field2 ) then "YES" else "NO" (This treats YES and NO as strings.)

Date-based rate shift

Apply different multipliers based on a specific cutoff date.

Formula: If Field1 date is greater than 25/12/2020 then Field2 * 0.23, else Field2* 0.21
Syntax: if (tablename@Field1 > "2020-12-25") then tablename@Field2*0.23 else tablename@Field2* 0.21

Nested conditions

Create a multi-tiered result based on the status of two different fields.

Formula: If Field1 = YES, then If( field2 = No, then Red, else Blue), else Green
Syntax: if ( tablename@Field1 == "YES" ) then ( if (tablename@Field2 == "NO" ) then "Red" else "blue" ) else "Green"

Compound AND requirement

Require both conditions to be met to trigger a specific result.

Formula: If Field1 = YES and Field2 = NO, then Red, else Green
Syntax:if ( ( tablename@Field1 == "YES" ) & ( tablename@Field2 == "NO" ) ) then "Red" else "Green"

Complex OR with date math

Require both conditions to be met to trigger a specific result.

Formula: If Field1 = YES or Field3 is less than 24/07/2020, then Field3 + 3 Months + ( Field4 + 3 ) Years, else Field3
Syntax: if ( ( tablename@Field1 == "YES" ) | ( tablename@Field3 < "2020-07-24" ) ) then tablename@Field3 + ( 3 Months ) + ( ( tablename@Field4 + 3 ) Years ) else tablename@Field3

DATE( ) formula

Use the following as a reference when you create DATE ( ) expressions for calculated fields. There are three parameters you can use for the DATE( ) formula, you need to provide at least one parameter:

Expression on date: A dynamic calculation or logical rule to select a specific point in time based on relative variables rather than a fixed, static value.
Date format: Rearranges the date data into the layout you indicate (only works with the paragraph text data type).
Date field: A specific object field used to select a specific point in time.

Examples of parameter input combinations:

DATE(<Expression_On_Date>)
DATE(<Expression_On_Date> , <Date_Format>)
DATE(<Date_Field> , <Date_Format>)
DATE(<Expression_On_Date> , <Date_Field>)
DATE(<Expression_On_Date> , <Date_Field> , <Date_Format>)

Date format syntax references for DATE( ) formula

The following syntax is used with the DATE( ) formula to format the date output for calculated fields.

Note
Date format only works with the paragraph text data type.

Syntax	Description	Output example
`Y`	Indicates 4-digit year	`2026`
`y`	Indicates a 2-digit year	`26`
`F`	Full text for month	`February`
`M`	Short 3-letter text indicating month	`Feb`
`m`	Two digits indicating month with leading zero	`02`
`n`	Single digit indicating month with no leading zeros	`2`
`d`	Two digits indicating day with leading zeros	`05`
`j`	Single digit indicating day without leading zeros	`5`
`l`	Full text indicating weekday	`Tuesday`
`D`	Short 3-letter text indicating weekday	`Tue`
`H`	24-hour format indicating time	`14` (for 2 pm)
`h`	12-hour format indicating time	`2` (usually proceeded with am/pm)
`i`	Two digits indicating minutes	`27`
`s`	Two digits indicating seconds	`31`
`a`	Lowercase am or pm indicating time of date	2:27 `pm`
`L`	Boolean that indicates whether the year is a leap year or not.	`0` (False) or `1` (True)
`t`	Number indicating the total number of days in the month	`28` (for Feb 2026)

Expression on date syntax references for DATE( ) formula

The expression on date calculates a specific point in time calculated from the current date (if no date field parameter is included) or the date in the field from the date field parameter.

Expression on date	Description	Examples	Output example
Basic expressions	Uses the current system time as the starting point.	`DATE ( “now” )`	02/23/2026 if the current date is 23 February 2026.
Boundary expressions	Calculates the edge of the time period (month or year).	`DATE ( “first day of this month”)`	02/01/2026 if the current month is February.
Offset expressions	Calculates the time by moving forward (positive) or backward (negative) from the indicated date.	Forward: `DATE ( "first day of 2 months" )` Backward: `DATE ( "first day of -2 months" )`	If the month is February: Forward: 04/01/2026 Backward: 12/01/2025

Expression on date

Description

Examples

Output example

Basic expressions

Uses the current system time as the starting point.

DATE ( “now” )

02/23/2026 if the current date is 23 February 2026.

Boundary expressions

Calculates the edge of the time period (month or year).

DATE ( “first day of this month”)

02/01/2026 if the current month is February.

Offset expressions

Calculates the time by moving forward (positive) or backward (negative) from the indicated date.

Forward: DATE ( "first day of 2 months" )

Backward: DATE ( "first day of -2 months" )

If the month is February:

Forward: 04/01/2026

Backward: 12/01/2025

Syntax scenarios for DATE( ) formula

The following are examples of common syntax used for the DATE( ) formula. When you use the DATE( ) formula, you must select either Date, Date & Time, or Paragraph Text as the data type based on the expected output you need.

Note
Ensure the syntax is typed with the appropriate spacing otherwise it will throw an error.

Syntax example	Required data type	Expected output format
`DATE ( “` `” )`	Date	MM/DD/YYYY Example, 02/19/2026 if today was 19 February 2026.
`DATE ( "now" )`	Date or Date & Time	MM/DD/YYYY or MM/DD/YYYY HH:MM:SS Example, 02/17/2026 if you select DATE data type or 02/17/2026 14:27:31 if you select DATETIME data type and the exact moment is 17 February 2026 at 14:47:31.
`DATE ( "last day of this month" )`	Date	MM/DD/YYYY Example, 02/28/2026 for last day of February.
`DATE ( "first day of this month" )`	Date	MM/DD/YYYY Example, 02/01/2026 if the current month is February.
`DATE ( "last day of next year" )`	Date	MM/DD/YYYY Example, 02/28/2027 if the current month is February 2026.
`DATE ( "last day of December next year" )`	Date	MM/DD/YYYY Example, 12/31/2027 if the current year is 2026.
`DATE ( "first day of next month" )`	Date	MM/DD/YYYY Example, 03/01/2026 if the current month is February 2026.
`DATE ( "last day of next month" )`	Date	MM/DD/YYYY Example, 03/31/2026 if the current month is February 2026.
`DATE ("first day of 2 months" )`	Date	MM/DD/YYYY Example, 04/01/2026 if the current month is February 2026.
`DATE ( "last day of -2 months" )`	Date	MM/DD/YYYY Example, 12/31/2025 if the current month is February 2026.
`DATE ( "now" , "Y-m-d" )`	Date	MM/DD/YYYY Example, 02/17/2026 if the current date is 17 February 2026.
`DATE ( "last day of next month" , Opportunities@Opportunities_CloseDate )`	Date	MM/DD/YYYY Example, 03/31/2026 if the opportunity close date is February 2026.
`DATE ( "last day of -2 months" , Opportunities@Opportunities_CloseDate)`	Date	MM/DD/YYYY Example, 12/31/2025 if the opportunity close date is February 2026.
`DATE ( Opportunities@Opportunities_CloseDate )`	Date	MM/DD/YYYY Example, 12/31/2025 if the opportunity close date is 31 February 2026.
`DATE ( "now" , "l , F j , Y" )`	Paragraph Text	Weekday, Month Day, Year Example, Thursday , February 19 , 2026.
`DATE ( "2025-12-15 08:30:23")`	Date & Time	MM/DD/YYYY HH:MM:SS Example, 12/15/2025 08:30:23 if the system clock is US timezone.
`DATE ( "last day of -2 months")`	Date	MM/DD/YYYY Example, 12/31/2025 if the current month is February 2026.
`DATE ( "2025-07-15" , "F Y" )`	Paragraph Text	Month YYYY Example, July 2026.
`DATE ( "last day of -2 months" , "2025-11-15" , "D, d M Y" )`	Paragraph Text	Weekday (short) , Day Month (short) , YYYY Example, Tues , 30 Sept , 2025 based on specified date of 15 November 2025.
`IF ( Opportunities@Opportunities_CloseDate > DATE ( "today" ) ) then 500 else 200`	Paragraph Text	If the opportunity close date is later than today (future opportunity), the output is 200. If the opportunity close date is today or before (past opportunity), the output is 500.