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

A Formula Radio Button

Formula Help

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


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

Related articles