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.
The Formula Help wizard differs based on the context from which it is launched. It generally contains these tabs.
The sections below detail commonly used formulas and provide syntax help.
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.
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, "/".
Regular expressions can be used to specify the search string. Some examples of regular expressions are given below:
sep [ae] r [ae] te
separate, seperate, seperete, or separete
cat as an entire word, rather than part of another word, as in catalog
Dec (ember) ?
Dec or December
cat, dog, mouse, or fish
variable [0-9] a
variable0a, variable1a, etc., ..., variable9a
th (is) *
th, this, thisis, thisisis, ...
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.
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.