Calculations are powerful. In addition to enabling calculations relating to date/number fields, they can also be used for validating data etc, below you will find worked examples and ideas, along with syntax.
Attached is a form that we advise you to import into your Forms product which shows an array of calculations/functions available.
This includes;
Detailed information on the contains function
Some Example if statements
Methods for manipulating strings
Date Formats (Note: time and datetime fields always STORE the seconds, even if not shown eg HH:MM, therefore when calculating with time fields need to include SS in calculations)*
Repeatable Subform equations.
Some useful tricks
A full list of logical operations.
Keyword list
Function list
To use this form properly, import it and Fill in the sections to find the calculation type you are looking for. Then go to 'Edit' to see how it was set up. The examples will help with defining your own required calculations
Using A Mathematical Calculation in a Form
The Form Designer can set up a calculated field by entering the mathematical formula into the “Default Value” field. Other fields on the form can be referenced by using their dataname in curly braces {}
They can perform mathematical operations on Number, Text and Date fields. They can be used to calculate costs, totals, or other more complex expressions.
eg in this example the number in the "distance" field will be multiplied by 0.34:
{distance}*0.34
If a repeating field is referenced, the value returned will be the sum of the values in the field. Other aggregate functions, such as “count” can be referenced. The following example calculates the average value in a repeating field:
sum({myfield})/count({myfield})
To calculate the totals from a select many (only works where the values of each item in the select many are unique)
sum({selectmany})
Most calculations will be dependent on the usage of tokens which are the value place holders in curly parenthesis, for example '{myfield}'.
Complex Calculations
Avoid using lengthy calculations as these are likely to introduce syntax errors and make solving the issue difficult, when calculating a fee payable based on a series of select 1 fields there is a natural tendency to build a beast of a calculation based on an if statement, we would avoid such scenarios, and suggest
Use "helper" fields to break the calculations into smaller more manageable chunks, this enables each element to be checked for syntax
For fee structures it is advisable to hold the fee table in a database with start and end dates This allows for future planning when fees change year on year, and removes the pressure of amending complex calculations out side of normal working hours. It also ensure that fees are calculated correctly depending on both when the request/order is placed and the date from which the order/service will be supplied (no beating the system by placing a order a few days before the price goes up). The correct fees can then be pulled into form from a lookup.
Using Calculations to Manipulate Text
Calculations can be used to control messages or the way text appear to the users or is presented for integrations, eg
Joining two or more fields/tokens together to create a text string - using ???concat({something},' ',{somethingelse})
For example an address string can be created using
"Your "+{application_type}+" request will receive a response by "+{deadline}
Or;
concat({fielda},' ',{fieldb})
Concat is recommended for numeric fields, + is easier but relies on the fields concerned to be textual. To add a line break between two fields (Text Area fields) You can use;
{text1}+' \n' + {text2}
Calculations can be used to display specific messages to the form filler based on previously completed fields
if( {tree} is 'yes' and {bench} is 'yes' and {lamppost} is 'yes' ,'One sentence can be displayed','Or another can be displayed')
But if calculation is more complex then often better to use brackets to ensure calculation is actioned in correct order
It is sometimes easier to manipulate the text to be displayed for an email or printable within the form itself using an "if calculation" rather than build complex af:if statements
Calculations can be used to create standard text for emails/PDFs - using a combination of the above and HTML fields it is possible to create a standard output for each form/process whic can then be dropped into a generic email/Printable reducing the over head for these.
Calculations can also be used to create specific redirect URLs
Using Calculations to validate
Calculations can be used to validate entries and give specific warnings to the form filler
if( {tree} is 'yes' and {bench} is 'yes' and {lamppost} is 'yes' ,'valid','invalid')
which returns true/false in the field if used as a default value Statements defining valid status can be added in Validation Condition eg {text3}='true' - if the field is not true then the Validation condition message will be displayed. In many cases the simple calculation to define valid status can simply be added in a validation condition box
Logic Operators
Any of the following operators and functions can be used in a calculated field:
Logical Operator
Description
Logical Operator
Description
+
Add.
<=
Less than or equal to.It can also be used the operator lte
-
Subtract.
>=
Greater than or equal to. It can also be used the operator gte
*
Multiply.
is
Equal to
=
equal
isnt
Not equal to.
===
triple equal . It also can be used the operator “is”
(equal value & type)
&&
and. it also can be used the operator “and” Please note this must be lowercase
!=
Not equal to.
||
or. It can also be used the logic operator “or” Please note this must be lowercase
!==
Not equal to.
contains
contains. See the example below for more information
<>
Not equal to. The same as the operator !=
icontains
case insensitive contains.
/
Divide.
notcontains
not contains
mod
Modulo.
noticontains
not contains case insensitive
<
Less than.It can also be used the operator lt
match
matches an expression
>
Greater than.It can also be used the operator gt
notmatch
doesn't match an expression
Examples:
The following example depicts the display condition of a field, using some of the previous operators. In this case, the field will be displayed whether the fields number1 is less than number2 and the field text1 contains the word “test“ ,or in the case that the field {text2} is “Firmstep”
({number1} < {number2} && {text1} contains 'test') ({text2} is 'Firmstep')
Given the field text2 "AAQ978A&%" the following expression will be evaluated as true
{text2} match '[^A-Za-z0-9_]'
See regex note below - when using regex statements
3. The following example if statement can be used to indicate this week/next week, Using a select field {date} and a cut off time field {time1} of HH:MM*
if({day} is 'Tuesday' and {time1} <'17:15:00' ,'This week','Next week')
Detailed examples are available in the attached functions demo form
When using display conditions, AND and OR have to be as and, or lowercase for this to work.
Keywords are special words which can be used in calculations.*
Keyword
Description
days
Used in date calculations to add a specific numbers of days to a given date. For example:
To add seven days to a date field called Order Date with the calculation would be {Order_Date}+7 days.
Please note that this keyword cannot be used in a DateDiff function. See the Functions table below for available parameters. Note when using negative numbers, a plus sign is still required - e.g. "format((now + -6 months),'%Y-%m-%dT%T')"
hours
Used in date calculations to add a specific number of hours to a given time. For example (time}+ 7 hours
months
Used in date calculations to add a specific numbers of months to a given date. For example:
To subtract three months from a date field called Delivery Date the calculation would be {Delivery_Date}-3 months.
Please note that this keyword cannot be used in a DateDiff function. See the Functions table below for available parameters.
now
Returns the current date and time. Is used in the DateDiff function. It allows date calculations to be performed without a second date field on the form. For example:
To calculate the difference in days between the current date and a date field called Collection Date, the DateDiff function is used with the now key word. The calculation is DateDiff(d, {now}, {date1}).
randomstring
Returns an 13 -character securely-generated random string (alphanumeric)
workdays
Used in calculations and date fields to add working days (Monday - Friday) to a date.
{today} + 10 workdays
(Note this does not exclude all bank holidays - from our testing, Christmas/Boxing/New Year's Day, as well as May Day and Easter, are excluded - there is no workaround to achieve working days excluding other bank holidays).
years
Used in date calculations to add a specific numbers of years to a given date. For example:
To add a year from a date field called Purchase Date the calculation would be {Purchase_Date}+1 years.
Please note that this keyword cannot be used in a DateDiff function. See the Functions table below for available parameters.
There are a number of built in functions to add more flexibility to calculations.
Function
Syntax
Description
count
count({field})
Counts the number of instances of a repeating subform or the number of uploads in an upload fields.
datediff
datediff(<d m y>,{date1},{date2})
Returns the number of time periods between date1 and date2. If date1 is later than date2 then the result will be positive.
The time period can be 'd', 'm', 'y', or 'fy'.
'fy' = full year - useful for calculating age from a DOB. E.g. datediff(fy,{today},{dateOfBirth})
'fm' = full month - Useful for calculating the full calendar months between two dates.
timediff
timediff(period, {time}, {time})
where period one of mis s min h fm fh
This calculation shows the difference between two time fields. Note this only works with time or date/time fields.
eg If you want the difference to be shown in minutes it should be;
timediff(min, {time1}, {time2})
If you want the difference shown in hours it would be;
timediff(h, {time1}, {time2})
{time1} and {time2} both have to be time/datetime fields in order for this to work.
dow
dow({date})
Takes an argument of a date and returns the day of a week as a number 0 to 6.Sunday is 0 through to Saturday which is 6.
if
if(expression,true,false)
This is a logical IF function which evaluates the given expression and returns the true if the condition is met, or false if it is not met. The expression can use fields, logical operators, and keywords. For example:
The statement if({a}>{b},{a},0) will return field a if a is greater than b, otherwise 0 will be returned.
An if function returning valid will pass validation. Returning invalid will cause the validation message to be shown. For example:
The statement if({a} is {b},invalid,valid) will not pass validation when a is equal to b and the validation message will be shown.
round
round({field},int)
This function will round the given field to the number of specified decimal places. E.g: round(3.4,0)=3 (be sure to set decimal places in the field properties too)
floor
ceil
floor({field},int)
ceil({field},int)
This function will round the given field DOWN to the number of specified decimal places. E.g: floor(3.99,0)=3 (be sure to set decimal places in the field properties too)
The related ceil function will round the given field UP to the number of specified decimal places. E.g: ceil(3.99,0)=4 (be sure to set decimal places in the field properties too)
sum
sum({subform/field})
Sums a repeatable field.
min
min({subform/field})
Returns the lowest value entered for a repeatable field. Only works with integer values.
max
max({subform/field})
Returns the highest value entered for a repeatable field. Only works with integer values.
contains
contains({array},string)
This function will be true if any one element of the array is equal to the search string.
substr
substr ({field},position,length)
This will return the characters or sub string of a field's value starting at 'position' (the first charater of a string is position=0) and lasting 'length' characters.
substring
substring ({field},start,end)
This will return the characters or sub string of a field's value starting at 'start’' (the first character of a string is position=0) until the character at position (end -1). e.g. substring(“Firmstep”,1,5) will return “irms”
This will convert the string to lowercase. It can be used to remove case sensitivity for validation calculations where input and test fields may be different cases.
E.g. if(lowerstr({input})=lowerstr({output}),valid,invalid)
upperstr
upperstr({field})
This will convert the string to uppercase. It can be used to remove case sensitivity for validation calculations where input and test fields may be different cases.
E.g. if(upperstr({input})=upperstr({output}),valid,invalid)
instr
instr(string1,string2)
Checks whether the first string contains the second. E.g. if(instr({field},'A'),1,0)) returns 1 if {field} contains 'A' and 0 otherwise.
entry
entry({subform/field},1)
Returns the value of a field in a particular row of a subform.
length
length({allfields})
Determines the length of the array evaluated
concat
concat({text1},{text2},{text3})
This function concatenates the arguments being passed, eg becomes text1,text2,text3
using + with differing field types will cause it to try and add instead of concat. Concat is advised for use instead with non-string fields.
trim
trim(string)
This function removes whitespaces from the beginning and from the end of the string. e.g. trim(“ test “) will convert the string into “test”
ucfirst
ucfirst({token})
Capitalizes the first character of a given lower case string that does not contain spaces. (If necessary may need to use lowerstr first)
toCamelCase
toCamelCase({string})
Removes white espaces of the input and capitalize the first letter of each string contained in the input. e.g. toCamelCase(“test bank statement.pdf”) will return testBankStatement.pdf
fromCamelCase
fromCamelCase({string})
it returns the input to its original format.
toUnderScore
toUnderScore({text1})
It returns the string replacing whitespaces with underscores. e.g. toUnderScore(“John Smith”) will return 'john_Smith' Please note the first letter is set to lowercase. replaceAll can be used instead if you want to maintain the case sensitivity. For example: replaceAll({text1}," ","_")
fromUnderScore
fromUnderScore({text1})
It returns the string replacing underscores with whitespaces. e.g.fromUnderScore(“John_ Smith”) will return “John Smith”
startsWith
startsWith({token1},'test')
Determines whether the beginning of the first string starts with the second argument of the function. e.g.
startsWith(“Firmstep”,”Fi”) will return true
endsWith
endsWith({token1},'test')
Determines whether the first argument of the function ends with the second string
dateFormat
dateFormat({date1},format)
This function changes the format of a given date. e.g. dateFormat(“21/01/1999”, 'MM-dd-yy') will return 01-21-99 or dateFormat({date1}, 'ddd') does the shorthand day name. Only works when used with date fields.
This function will return the day of a given date field. e.g. getDay(“11/02/2015”) will return 11. Only works when used with date fields. This functionality is currently not available. Instead use dateFormat. E.g dateFormat({date1},'dd')
getWeek
getWeek({date1})
It returns the week of a given date. The result for the previous date will be 2. Only works when used with date fields. This functionality is currently not available.
getMonth
getMonth({date1})
It returns the month of a given date
The result for the previous date will be 2. Only works when used with date fields. This functionality is currently not available. Instead use dateFormat. E.g dateFormat({date1},'MM')
getYear
getYear({date1})
This functionality is currently not available. Instead use dateFormat. E.g dateFormat({date1},'yyyy')
dayofweek
dayofweek({date1})
It returns the day of the week of a given date. The result for the 11/02/2015 will be 4 (Wednesday). Only works when used with date fields.
dayofyear
dayofyear({date1})
It returns the day of the year of a given date.The result for the 11/02/2015 will be 42. Only works when used with date fields.
weekofyear
weekofyear({date1})
It returns the week of the year of a given date.The result for the 11/02//2015 will be 7. Only works when used with date fields.
dayName
dayName({date1})
It returns the day of the year of a given date.The result for the 11/02/2015 will be Wednesday. Only works when used with date fields.
monthName
monthName({date1})
It returns the month of the year of a given date.The result for the 11/02/2015 will be February. Only works when used with date fields.
isLeapYear
isLeapYear({date1})
It returns whether the year is leap or not. Only works when used with date fields.
The following functions are commonly used to complete a calculation that references a repeatable subform. To make them more easily found on this page, we have made another table below specifically:
sum
sum({subform/field})
Sums a repeatable field.
min
min({subform/field})
Returns the lowest value entered for a repeatable field. Only works with integer values.
max
max({subform/field})
Returns the highest value entered for a repeatable field
entry
entry({subform/field},1)
Returns the value of a field in a particular row of a subform.
concat
concat({subform/field})
Appends every value entered for a repeatable field into a single string, without a delimiter. You can also just use {subform/field} to get a comma-delimited list, but this isn't always useful if the field can be blank or contain commas
Regex
Regex statements can be used where required to check validity/format of data etc
Note when using regex the product will append /g to the regex which can break some regex statements eg /gim becomes /gim/g which causes the regex to fail
So when using match do not combine with /gim
eg.
upperstr({postcode}) match '^(GIR ?0AA [A-PR-UWYZ]([0-9]{1,2} ([A-HK-Y][0-9]([0-9ABEHMNPRV-Y])?) [0-9][A-HJKPS-UW]) ?[0-9][ABD-HJLNP-UW-Z]{2})$'
This will always check the postcode field as if all characters are upper case.
All tokens must be shown in a calculation within the curly brackets eg {now}
How can I add together the total of the same field from a repeating subform? - Use a calculated field on the main form with the following calculation: sum({subformname/fieldname})
How do I get numbers from numerous select one fields to add correctly rather than being concatenated? - The calculation needs to look like this: n({field1})+n({field2})
How do I add a day (24 hours) to a date field? - By adding a calculated field to the form with the following calculation: {datefield} + 1 days The 'days' statement is not a typo but is the expected syntax. The results of this calculation can then be used in another date field, or within a paragraph, subheading or rich text field to display the result.
Can I pull values in from the url? - Yes, you need to set the default value of field to be {querystring:parameter}. So for example if in the url loading the form you have ?parameter=1 this would populate the field with a default value of {querystring:parameter} with 1.
How can I stop a form being submitted before a large upload has completed its upload? Use count({upload1}) where upload1 is the upload field - to check that the upload has been made and then set a validation calculation that this must be >0 to prevent the form from submitting without the file upload being completed
If a default value is based on a lookup/calculation - then the first time it is filled the result provided clears the default value - so it will not update if moving from one section to another and back - making the field read only will enforce the default value token to be maintained so it will update if you return to the section and make any changes
Date calculation that only involves weekdays and ignores weekends - (datediff('d', {date1}, {date2}) - (datediff('fw',{date1},{date2}) * 7) ) + ( datediff('fw',{date1},{date2})*5).
Note: if you wish to create a calculation that only involves working days, you will have to create your own lookup that queries a database. Database integrations of this type have been used in the FOI and Contact Us MyServices.(see https://www.gov.uk/bank-holidays.json)
Making a select field read only once a selection has been made - add a read only condition of {select1}<>'' to the advanced tab (note this means the users cannot correct their entry)
The default values of read-only fields are evaluated in a different order than other fields. For read-only fields, the default value takes precedence over all other types of value. This is explained in full in our field evaluation guide.
How to use 'or' and 'and' conditions in a if statement When using an 'or'/'and' condition in an if statement it must be within brackets. 'or' is represented as ||, 'and' is represented as &&. For example;
if(({text1}='Yes'&&{text2}='Yes'),'Both are Yes','One or both are not Yes')
In the above, the result will only be 'Both are Yes' when {text1} and {text2} have a value of 'Yes'.
For a or condition it would be;
if(({text1}='Yes'||{text2}='Yes'),'Either are Yes','None are Yes')
It is possible to use tokens to enable conditional switching between Submission Redirect URLs as required eg: add a hidden field on your form eg {redirectURL} then set a default value for this field using syntax if({selectfield} = 'something','https://weblink','https://weblink2')
Use of double quotes (") rather than single ones (') as string delimiters can cause issues. For this reason we recommend using only single quotes.
When using calculations based on field tokens within a form, the calculation needs to be after the input fields which are used in the calculation.
Using getYear in calculations can cause knock-on issues with other date fields. We recommend putting the getYear call in a separate field, then formatting it to a string when using it in calculations. e.g format({seperateGetYear},string) + 1
'replace' calculation doesn't work when trying to remove a full stop. For example this wouldn't work 'replace({text2},'.','')' instead you can use custom regex. For example replace({token},"/\\./g",'') .
You are not able to reference repeating values directly in calculations. If you have a multiple select field for example this creates a comma string when you use the token directly in a calculation which will cause it to fail. Instead pass the value into a hidden text field first. Then use this new fields data name in your calculation.
'Calculation Error: Undefined' message that appears is likely caused by a mistake in the calculation. The only way to resolve this issue is to break down the calculation and carefully check that everything is correct. Two examples could be a misplaced bracket or a special character being in the wrong place.
{today} - there are some issues with this token and others may prove more accurate
Calculating a task or case due date in the task or case due date fields themselves creates issues. We would recommend doing the date calculation inside the form and then using this token only in the case or task due/warn date fields.
