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.
The Formula Help wizard differs based on the context from which it is launched. It generally contains these tabs:
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 |
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.
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.
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.
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
”
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.
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.
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.
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.
concatenatestringsexcerpt |
convertcurrencyexcerpt |
dateformatexcerpt |
deeplinkexcerpt |
findexcerpt |
matchesexcerpt |
newlineexcerpt |
num2wordsexcerpt |
rateofexchangeexcerpt |
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/ |
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.
ternaryoperatorsexcerpt |
Related articles |