---
title: "Formulas"
canonical: "https://help.agiloft.com/space/HELP/43715029/Formulas"
format: markdown
---
Formulas are used throughout the system to perform calculations on selected fields. Formulas for updating fields are used in the following scenarios:

- Mass editing field values.
- [Update Fields Actions](https://agiloft-public.atlassian.net/wiki/spaces/HELP/pages/43718440), used in rules and action buttons.
- Setting default field values.
- [Variable Formula Fields](https://agiloft-public.atlassian.net/wiki/spaces/HELP/pages/43715637), which are evaluated by actions.

The Formula Wizard helps you create formulas by providing a list of relevant [field variables](https://agiloft-public.atlassian.net/wiki/spaces/HELP/pages/43714212), 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.

![image](media://92d80ee9-fcd8-4ec5-8311-117108b034cd)

# Formula Help

<span style="color: #000000">The Formula Help wizard differs based on the context from which it is launched. It generally contains these tabs:</span>

- <span style="color: #000000">**Home: **</span>Provides general guidance on formulas and syntax instructions, including which fields and functions can be used. You can insert the functions and fields you need, then edit the formula to arrange them as necessary.
- **Fields:** Contains a list of field variables and field descriptions in the current table. Click on a field name to insert the variable into the formula.
- **Parent Fields:** Contains a list of field variables for the parent record, and only appears when editing an Update Fields action from within a Linked Record action.
- **Global Variables:** Lists global variables, including linked fields from the user table such as $global.my_teams.
- **New Variable:** Allows you to create a new summary variable based on the data set in a table and optionally filtered by a saved search.
- **Functions:** Lists available functions, syntax, and sample usage. Functions can perform calculations, retrieve and adapt values, and perform other simple transformations on data.
- **Values, Teams:** Lists available units of measurement and team names, respectively, which can be inserted into formulas by clicking on the hyperlinked value.

# > Macro (anchor)

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)`

## Document 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 [Formulas#contexts](https://agiloft-public.atlassian.net/wiki/spaces/PROD/pages/44010245). For instance, <span style="color: #333333">formulas in an Update Fields action</span> 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](https://agiloft-public.atlassian.net/wiki/spaces/HELP/pages/43719618). 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.

### Capitalize

> Macro (include)

### Concatenate Strings

> Macro (include)

### > Macro (anchor)

Currency Conversion

> Macro (include)

### Date Format

> Macro (include)

### Deep Link

> Macro (include)

### Extract

The extract formula evaluates text in a field and extracts the segment of the text that matches a pattern specified by [regular expressions](http://www.regular-expressions.info/) (regex). This allows you to use regular expressions to select just a segment of a given line of text, and apply that segment as the new value.

#### Syntax

`extract($field, pattern[,flags])`

#### Example

|  |  |
| --- | --- |
| `extract($company_name, "^.{3}")` | Returns the first three characters of the contents of the Company Name field. |

#### Flags

Case-insensitive matching and multi-line mode are enabled by the optional flags "i" and "m" respectively, or "im" to enable both. Multi-line mode affects searches on text fields or other data types with multiple lines. If multi-line mode is not enabled in these circumstances, line breaks are treated as spaces.

### Find

> Macro (include)

### Matches

> Macro (include)

### New Line or Line Break

> Macro (include)

### Numbers to Word

> Macro (include)

### > Macro (anchor)

Rate of Exchange

> Macro (include)

### 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, "/". You can also use [regular expressions](http://www.regular-expressions.info/) (regex) to specify the string search.

#### Syntax

`replace($field, pattern, new text)`

#### Examples

| **Example formula** | **Output** |
| --- | --- |
| replace($summary, "spring", "summer") | Replaces instances of *spring *with *summer*. |
| replace($text, "\bCat\b", "Andrew") | Replaces instances of Cat with Andrew, but only when Cat is an entire word, rather than part of another word like catalog. |

### Ternary Operators

> Macro (include)

> Macro (hide-if)
> 
> #### Related articles
> 
> > Macro (contentbylabel)