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.
• Updating fields actions, used in rules and action buttons.
• Setting default field values.
• Variable formula fields, which are evaluated by actions.

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. Sometimes you'll see a choice between Standard text and Formula; select 'A formula' and the help icon will appear.

# Formula Help

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

• Home tab: 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 tab: 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 tab: This tab lists field variables for the parent record, and only appears when editing an Update Fields action from within a Linked Record action.
• Global Variables tab: Lists global variables, including linked fields from the user table such as \$global.my_teams.
• New Variable tab: Use this tab to create a new summary variable based on the data set in a table and optionally filtered by a saved search.
• Functions tab: Lists available functions, syntax, and sample usage.
• Values, Teams tabs: Lists available units of measurement and team names, respectively, which can be inserted into formulas by clicking on the hyperlinked value.

# Formulas for Updating Field Values

The sections below detail commonly used formulas and provide syntax help.

## Simple Formulas

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.

## String Replacement

Text string replacement may replace, or append to, 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.

## New line or line break

 newlineexcerpt

## Concatenate Strings

 concatenatestringsexcerpt

## Find()

 findexcerpt

## Matches()

 matchesexcerpt

## Dateformat

 dateformatexcerpt

## Ternary operators

 ternaryoperatorsexcerpt

## Currency conversion

Two currency conversion formulas are provided using an open-source API to convert an amount between currencies or return the exchange rate. Currency conversion formulas use the open-source JSON API fixer.io to obtain exchange rates published by the European Central Bank. These rates are updated daily at approximately 4pm Central European Time (CET). If the date parameter is not specified, the current exchange rate is used.

The Demo KB uses a free API key from fixer.io to make requests to their API. If you intend to use the currency conversion for production environments, then we recommend purchasing a paid version of the API key. The various plans available are shown at https://fixer.io/product

After obtaining an API key from fixer.io, please log into the Agiloft KB and update the Fixer.io Service Access Key global variable through Setup > System > Manage Global variables to make use of the new key.

### RateOfExchange

 rateofexchangeexcerpt

### convertCurrency

 convertcurrencyexcerpt