Use the ↓ down arrow again to select an option, and then <tab> or <enter>.
Practice auto-completion by starting from a different character, such as letter G:
======= General Structure =======
Each statement MUST be separated using separator lines, which should start and finish with "--".
This formula uses properly separated statements.
Separator lines can include comments.
This formula includes 2 separator lines (one of which has a comment)
======= Variables Declaration =======
To declare or assign variables, use the "var" statement, and prefix your variable name with $.
To declare strings, use single quotes
- If your string contains single quotes, double them
To declare dates, use # at the start and end
- Always use the YYYY-MM-DD format
This formula declares variables of different types (numeric, string, date).
======= Variable Text Embedding =======
To embed variables into strings, use "{$yourVariable}".
Dates / numeric values are automatically converted to a text representation.
This avoids manually concatenating text values.
This formula embeds variables $category and $type into a message.
This is equivalent to var $msg = 'The category is ' + $category + ' and the type is ' + $type
======= Flow Control =======
Various constructs let you control the flow of execution.
Last Value = Return Value
If a formula ends with a statement which isn't a variable assignment, it becomes the return value.
This formula returns 101 because the last statement is not a variable assignment.
IIF(Condition, Value, Otherwise)
IIF() returns <Value> if <Condition> is true, and <Otherwise> if false.
This formula assigns a value of 1 to $a if the transaction's revenue is positive, and -1 otherwise.
IFEX(Condition, Value)
IFEX() returns <Value> if <Condition> is true, and exits the script immediately.
This formula returns a value of 100 if the transaction's revenue is positive (subsequent statements are not evaluated since the condition is met).
WHEN(Condition) ... END WHEN
WHEN blocks are entered when <Condition> is true.
This formula increments $a's value by 1, but only if the transaction's revenue is positive.
RETURN(Value)
RETURN() returns <Value>, and exits the script immediately.
This formula returns a value of 101, but only if the transaction's revenue is positive.
!!Throw Message
Throws an error and exits the script immediately. An error will appear in calculation alerts.
This formula throws an error (with the fee value) - processing is aborted and 1 is not returned.
!!Warn Message
Logs an error but continues running the script. An error will appear in calculation alerts.
This formula logs an error (with the fee value) - processing continues and 1 is returned.
======= Utility Functions =======
Here are some general data manipulation functions.
Logical Functions
Numerical Functions
- Abs(x) - returns x if greater than 0, and 0 otherwise
- Min(x, y) - returns x if lower than y, and y otherwise
- Max(x, y) - returns x if greater than y, and y otherwise
- Round(x, [Digits]) = returns the rounded of value x
- RoundUp(x, [Digits]) - returns the ceiling of value x
- RoundDown(x, [Digits]) - returns the floor of value x
- ConvertAmount(Value, Currency, Date) - converts a value from a specified currency, to your workspace's default currency, using an effective date
Text Functions
- LEN(Text) - gets the length of a string
- REPLACE(Text, Before, After) - replaces a string by another
- TRIM(Text) - removes leading and trailing spaces, tabs, etc.
- SUBSTRING(Text, Start, Length) - extracts a string as a specified position with a given length
- Regex(Text, Pattern) - extracts a pattern using a regular expression
- RegexReplace(Text, Pattern, Replacement) - replaces matching a pattern using a regular expression
- All text functions are case-insensitive
Date Functions
- FromUtc(Date) - returns a date in UTC using your workspace's timezone
- ToUtc(Date) - returns a date relative to your workspace's timezone
- DateAdd(Date, Unit, Count) - returns a date after adding units (Days, Months, Quarters, Years, WorkDays)
- DateDiff(Date1, Date2, Unit) - returns units between two dates (Days, Months, Quarters, Years, WorkDays)
- GetDate(Date, Unit) - returns the component of a date (Days, Months, Quarters, Years, WorkDays)
- GetDateStart(Date, Unit) - returns the start of a date (Days, Months, Quarters, Years, WorkDays)
- GetWeekDay(Date) - returns the day of the week (Sunday = 0, Monday = 1, Tuesday = 2, etc.)
- GetWeekNumber(Date) - returns the week number relative to your fiscal year (ISO week)
- UtcNow() - returns the current date
- InPeriod(Date) - checks if a date is in the calculation period
Type Conversion Functions
- ToString(Value) - converts a value to a string value with a default of '' (empty string)
- ToDecimal(Value) - converts a value to a numeric value with a default of 0
- ToDate(Value) - converts a value to a date with a default of 2000-01-01
Alias-Based Lookup Functions
Other Lookup Functions
Lookup(Value, $lookupTable, [DefaultValue], [BlendRates]) - if a lookup table has been loaded into variable $lookupTable, this returns the result of performing a lookup into this table using the specified value (learn how you can define lookup tables as custom variables here)
- If you set [DefaultValue], this value is returned if the lookup table does not match the value
- If you set [BlendedRates] as true, a blended rate is calculated based on tiers and their rate
- QueryTransactionSingle(SearchField, SearchValue, ResultField) - searches for one transaction whose SearchField is set to SearchValue, and returns its ResultField (this is useful if you need to query another set of data)
- QueryTransactionSum(SearchField, SearchValue, ResultField) - searches for all transactions whose SearchField is set to SearchValue, and returns the sum of ResultField (this is useful if you need to query another set of data)
- QueryTransactionMin(SearchField, SearchValue, ResultField) - searches for all transactions whose SearchField is set to SearchValue, and returns the minimum of ResultField (this is useful if you need to query another set of data)
- QueryTransactionMax(SearchField, SearchValue, ResultField) - searches for all transactions whose SearchField is set to SearchValue, and returns the maximum of ResultField (this is useful if you need to query another set of data)
Grouping Functions
- SetRewardGroup(Value) - allows grouping of transactions for advanced reward calculation scenarios. To learn more about transaction grouping, click here.
Debug Functions
- Log(x) - log a variable and its evaluated result

This formula combines utility functions and variables.
======= Custom Variables =======
You can define custom variables on plans, users, or teams.
To refer to custom variables, use expressions such as:
- [Plan].[@@CustomVariable]
- [Credited].[@@CustomVariable]
- [Beneficiary].[@@CustomVariable]
- Etc.
It is also possible to retrieve custom variables using explicit Lookup() statements (see "Alias-Based Lookup Functions" above for more details). To learn more about custom variables, click here.
======= Retrieving Commission Amounts =======
You can retrieve how much commission has been paid so far on a deal (or even who previously got paid on a deal).
GetCommission(BeneficiaryId, OnlyReleased, Estimated, [TransactionId], [PlanId], [Explanation], [ExcludeSelf])
This function returns the total amount of commissions paid (so far) on a single transaction, to a single beneficiary. You can further control whether you want to retrieved all such commissions, or filter them.
BeneficiaryId is the ID of the payee to lookup a commission for
- This parameter is required and should be non-empty
- This is typically read from [Beneficiary].[id]
OnlyReleased controls whether unreleased calculations should be considered
- This parameter is required and should be true or false
- If true, only commissions paid by fully released calculations will be considered
- If false, commissions paid by unreleased calculations will also be considered
Estimated controls whether to retrieve actual or estimated commissions
- This parameter is required and should be true or false
- If true, only estimated commissions will be considered
- If false, only actual commissions will be considered
TransactionId is the transaction ID to lookup a commission for
This parameter is either
- If not specified, the current transaction will be used
- If specified, commissions for the specified transaction ID will be considered
- This is typically read from [Transaction].[id]
- This can be a transaction system ID or a transaction Order ID
PlanId is a specific plan to consider
- This parameter is optional
- If not specified, commissions paid by all plans will be considered
- If specified and non-empty, only commissions for the specified plan ID will be considered
- If specified but empty, commissions paid by all plans will be considered
- This is typically read from [Plan].[id]
- This must be a plan system ID
Explanation is a specific explanation to consider
- This parameter is optional
- If not specified, commissions with any explanation (could be empty) will be considered
- If specified and non-empty, only commissions with the specified explanation will be considered
- If specified but empty, commissions with any explanation (could be empty) will be considered
- You can also request a partial "contains" match (ex: using either '%something%' or '*something*')
- Please note that only "contains" match are supported (ex: using 'somet%ing' is not supported)
ExcludeSelf controls whether to exclude commissions paid by the current calculation
- This parameter is optional
- If not specified, commissions paid by the current calculation will be excluded
- If specified and true, commissions paid by the current calculation will be excluded
- If specified and false, commissions paid by the current calculation will be included
GetCommissionRate(BeneficiaryId, OnlyReleased, Estimated, [TransactionId], [PlanId], [Explanation], [ExcludeSelf])
Same as the above, but returns the overall commission rate (ex: a 1% commission rate will be returned as 0.01).
GetCommissionBeneficiaries(OnlyReleased, Estimated, [TransactionId], [PlanId], [Explanation], [ExcludeSelf]
Same as the above, but returns the list of payee IDs with a commission (ex: '9f2ff62e-1202-469b-bac4-e693f65c0733||6a485608-d3e0-41af-84bc-be39925f38cc'). This is often used in dynamic crediting formulas. This formula pays the difference between a/ what has been paid so far and b/ a re-calculated owed commission amount.
======= Storing Calculated Values =======
StoreCalculation(Name, Key, Value) - stores a value within a calculation
- Name is the name of the property to store
- Key is the slot to use (ex: [Credited].[id] to store a fact for a credited user or team)
- Value is the value to store
LoadCalculation(Name, Key) - loads a value from a calculation
- Name is the name of the previously stored property to load
- Key is the slot to use (ex: [Credited].[id] to store a fact for a credited user or team)
StoreTransaction(Name, Value) - stores a value within a credited transaction
- Name is the name of the property to store
- Value is the value to store
[Transaction].[Name] - loads a stored value from a transaction
- Name is the name of the previously stored property to load