Formulas are used throughout the system to perform calculations on selected fields. Formulas for updating fields are used in the following scenarios:

The Formula Wizard helps you create formulas by providing a list of relevant field variables, available formulas, and syntax help. Click Formula Help to open the wizard or, if you see a choice between Standard Text and A Formula, select A Formula and then click Formula Help.

Treat the above as a formula

Formula Help

The Formula Help wizard differs based on the context from which it is launched. It generally contains these tabs:

Formula Syntax Variations

Formula syntax differs slightly between contexts. For example, the dateformat() function requires a $formula() wrapper in a print template, but not in an Update Fields action or a mass edit. This section provides a quick reference for the syntax you need to use in each context.

In any context, whenever you insert a formula or function inside another, you don't need to use the $formula() wrapper. For example, in a context where you generally need a wrapper, you would use it only on the outermost formula: $formula(concat($company_name,"_",$contact_name)

Print Templates

Standard variables don't require a $formula() wrapper, and the system parses all variables without special syntax as long as they correspond to an actual field variable. If a field variable isn't recognized, the variable is stripped from the resulting document entirely.

Functions always require a $formula() wrapper.

If you need to use the $ character in the template text, outside of a formula or variable, you must precede it with another $. Otherwise, it is parsed as an invalid field variable and stripped from the document.

If a field variable needs to be immediately followed by a character that isn't a space, which normally would be parsed as part of the variable, use the concat() function. For example, if you use $start_date: the colon isn't printed after the start date value. Instead, use $formula(concat($start_date,":")) to print a colon after the start date value.

Email Templates

Like print templates, standard variables don't require a $formula() wrapper, and the system parses all variables without special syntax as long as they correspond to an actual field variable. However, if a field variable isn't recognized, it is printed as written instead of being stripped out.

Functions always require a $formula() wrapper.

If you need to use the $ character in the template text outside of a formula or variable, you can enter it as-is and it is printed as shown, as long as it isn't followed by text that is recognized as a variable name.

Update Field Actions, Mass Edits, and Default Values

This refers to running Update Fields actions, performing a mass edit using the formula option, and setting default values with a formula in field data types that allow it.

The $formula() wrapper is not required for standard variables or for functions.

You can use + to combine text strings with field variables, as long as the variables don't contain numeric values: "Role: " + $update_role + ", Team: " + $role_team_ + ", and Group: " + $role_group + " were added." However, if any of the field variables store numeric values, you need to use the concat() function to combine them with text strings. Otherwise, you can use + to print the sum of the values.

Note that default values in Calculated Result fields also follow these rules, but the formula output must be a number, not a string.

Variable Formula fields

This refers to the text you enter in a Variable Formula field in a record, such as the Condition field in an Approval Template.

The $formula() wrapper is not required for standard variables or for functions.

To combine multiple conditions, use && to require both conditions or || to require one condition. For example, this formula in a Variable Formula field would evaluate whether the contract's jurisdiction is the USA and the amount is over $250,000: $latest_contract_id.jurisdiction==”USA” && $latest_contract_id.contract_amount>”250000

Print and Merge Document Actions

This refers to the option to use a formula to generate the file name for Print actions and Merge Document actions.

Like print templates, standard variables don't require a $formula() wrapper, and the system parses all variables without special syntax as long as they correspond to an actual field variable. However, if a field variable isn't recognized, it is printed inside a new $formula() wrapper instead of being stripped out. For example, if you enter $start_date and that variable isn't recognized, the file name shows $formula($start_date) instead.

You can include strings directly, without the use of any additional formulas. For example, to title the document "Document for Contract 42" where 42 is the Contract ID, you can use Document for Contract $id.

If a field variable needs to be immediately followed by a character that isn't a space, which normally would be parsed as part of the variable, use the concat() function. For example, if you use $start_date: the colon isn't printed after the start date value. Instead, use $formula(concat($start_date,":")) to print a colon after the start date value.

Formula Functions for Updating Field Values

The sections below detail commonly used functions and provide syntax help. Remember that there are variations in how formulas are used in different contexts. For instance, formulas in an Update Fields action do not require the $formula() wrapper, while print template formulas do.

Simple Operations

Standard mathematical operations can be used in formulas, such as * for multiplication, / for division, + for addition, and - and subtraction. For example, if $amount is a field variable, the formula $amount*10 multiplies the value in the amount field by ten.

For a list of available operators, see Operators. You can also view the list of operators and definitions from the Formula Help wizard.

Common Functions

Here, find more information about the most commonly used functions. You can find a complete list of functions on the Functions tab of the Formula Help wizard.

Concatenate Strings

concatenatestringsexcerpt

Currency Conversion

convertcurrencyexcerpt

Date Format

dateformatexcerpt

Deep Link

deeplinkexcerpt

Find

findexcerpt

Matches

matchesexcerpt

New Line or Line Break

newlineexcerpt

Numbers to Words

num2wordsexcerpt

Rate of Exchange

rateofexchangeexcerpt

String Replacement

Text string replacement can replace, or append, an existing string.

To replace part of a text string, delimit the text to be replaced, also known as a search string, and the replacement text with a slash, "/".

/dog/cat/
changes 'Good dog! Your dog food is in the dog dish'
to 'Good cat! Your cat food is in the cat dish'

Regular expressions can be used to specify the search string. Some examples of regular expressions are given below:

Expression

Matches...

sep [ae] r [ae] te

separate, seperate, seperete, or separete

\bcat\b

cat as an entire word, rather than part of another word, as in catalog

Dec (ember) ?

Dec or December

cat|dog|mouse|fish

cat, dog, mouse, or fish

variable [0-9] a

variable0a, variable1a, etc., ..., variable9a

th (is) *

th, this, thisis, thisisis, ...

this+

this, thiss, thisss, ...

You can find more information on usage of regular expressions at http://www.regular-expressions.info.

Two functions can be used to find and match on substrings using regex. These are detailed below.

Ternary Operators

ternaryoperatorsexcerpt

Related articles