The examples below are the calculations that come up most often on real forms. Each one goes in the field's Value property, in place of a fixed default value, and each is written the same way: field names in curly braces, and the whole expression wrapped in one more pair of curly braces so the form knows to work it out rather than display it.


Two rules apply to every example. Field names are case sensitive, so {Subtotal} and {subtotal} are not the same field. And if the field being calculated is one that saves data - Text, Number, Date and so on - set its Where to Get Initial/Latest Value setting to Do Not Set Value / Compute Value, otherwise the calculation is overwritten when the form loads a saved value.


If a calculation shows up on the form as its own text instead of a number, that is the form telling you the expression could not be worked out. Nine times out of ten it is a misspelled field name, a missing curly brace, or a missing quote.


Math Examples

Add two numbers together
{cleanNumericValue('{Fee1}') + cleanNumericValue('{Fee2}')}
Wrapping each field in cleanNumericValue is what makes this safe: an empty field counts as 0, and a value typed as $1,200.00 is read as 1200. Without it, two blank fields can produce a joined-up piece of text rather than a total.


Line total from quantity and price, to two decimal places
{getFormattedNumber(cleanNumericValue('{Qty}') * cleanNumericValue('{UnitPrice}'), 2)}
A quantity of 3 and a price of 12.5 gives 37.50.


Show a total as currency
{formatCurrency(cleanNumericValue('{Total}'), 2)}
Gives $1,234.00. Use this for anything the user reads as money, and keep the plain number in a separate field if that value also has to be saved or used in further math.


Add up a whole column of a table
{calcSum('{LineItems| |Amount}')}
The {TableName| |ColumnName} reference returns every value in that column, and calcSum totals them. The middle part is the row number, and leaving it empty is what asks for the whole column instead of one row. Put a row number there to read a single cell - {LineItems|3|Amount} is the amount on row three.


Add up only some of the rows
{calcSum('{LineItems| |Amount|Type=Billable}')}
The fourth part is a filter: only rows whose Type column equals Billable are included. Use != instead of = to exclude rows, and separate two conditions with ^.


Count, average, highest and lowest of a column
{calcCount('{LineItems| |Amount}')} and {calcAvg('{LineItems| |Amount}')} and {calcMax('{LineItems| |Amount}')} and {calcMin('{LineItems| |Amount}')}
These work exactly like calcSum. To count rows rather than values, {LineItems|rowcount} is shorter.


Add up the amounts attached to selected options
{calcSum('{Extras:Amount}', ',')}
Option lists, radio buttons and multi-option lists can carry an Amount on each option, which is how a form prices a list of add-ons. A multi-option field returns the amounts of everything ticked as a comma-separated list, so the delimiter has to be given as a comma. A single-choice option list returns one amount and needs no delimiter.


Work out a percentage
{getFormattedNumber(cleanDivision(cleanNumericValue('{Answered}'), cleanNumericValue('{Total}')) * 100, 1)}
Gives 62.5 for 5 out of 8. cleanDivision is what stops a blank or zero denominator from breaking the form - it returns 0 instead of an error.


Apply a discount percentage
{getFormattedNumber(cleanNumericValue('{Subtotal}') * (1 - cleanNumericValue('{DiscountPercent}') / 100), 2)}
A subtotal of 200 with a discount of 15 gives 170.00. To show the discount itself rather than the discounted price, use {getFormattedNumber(cleanNumericValue('{Subtotal}') * cleanNumericValue('{DiscountPercent}') / 100, 2)}.


Add sales tax to a subtotal
{getFormattedNumber(cleanNumericValue('{Subtotal}') * (1 + cleanNumericValue('{TaxRate}') / 100), 2)}
This is the version to use when the tax rate is a field on the form. If the form is set up as an order form with amounts on its fields, the built-in {TOTALAMOUNT}, {TOTALTAX} and {TOTALWITHTAX} values already do this work and need no expression at all.


Add several fields into a grand total
{getFormattedNumber(cleanNumericValue('{Labor}') + cleanNumericValue('{Parts}') + cleanNumericValue('{Freight}') - cleanNumericValue('{Deposit}'), 2)}
Any number of terms can be chained this way, and ordinary math rules apply, so use parentheses wherever the order matters.


Show what is left of a budget

{getFormattedNumber(cleanNumericValue('{Approved}') - calcSum('{Expenses| |Amount}'), 2)}
Mixing a plain field and a table column in one expression is fine. The result goes negative when the table overruns the approved figure, which is usually what you want a budget field to show.


Work out a unit price safely
{getFormattedNumber(cleanDivision(cleanNumericValue('{TotalCost}'), cleanNumericValue('{Units}')), 4)}
Using cleanDivision means the field simply shows 0 until a quantity has been entered, rather than showing an error while the user is still typing.


Round to a whole number
{getFormattedNumber(cleanNumericValue('{Hours}') * cleanNumericValue('{Rate}'), 0)}
getFormattedNumber with 0 places rounds and drops the decimals.


Show nothing until there is something to show
{cleanNumericValue('{Balance}') > 0 ? formatCurrency(cleanNumericValue('{Balance}'), 2) : ''}
A question mark and colon is a plain "if this, then that, otherwise the other". It keeps a form from displaying $0.00 in a dozen places before anything has been filled in.


Convert between units
{getFormattedNumber(cleanNumericValue('{Pounds}') * 0.4536, 2)}
Any fixed factor works the same way - miles to kilometers, gallons to liters, or a currency conversion held in another field.


When you do not need an expression at all
A Math Label field does straightforward arithmetic without any typing: add a row for each step, pick the operation from the drop-down and choose the field or type a fixed number, and set Decimal Places for the result. It starts from 0 and applies the rows in order, following normal math rules, so multiplication and division happen before addition and subtraction. Reach for a calculated expression when you need the functions above - table columns, currency formatting, safe division, or a condition.


Things to watch with math

Empty fields are the most common cause of a wrong total. Always put cleanNumericValue around a field reference in a math expression so blanks count as 0.

Currency symbols, commas and percent signs are removed by cleanNumericValue and tryParseFloat, so a user typing $1,250.00 causes no trouble. Anything you do not pass through one of those functions is treated as plain text.


A value with a leading zero, such as a reference number entered as 0700, can confuse the calculation. Pass it through cleanNumericValue and it is read correctly.

cleanDivision returns 0 if either number is 0, blank or not a number. That is what makes it safe, but it also means a division that legitimately involves 0 shows 0 rather than an error.

getFormattedNumber and formatCurrency return formatted text, not a number. If a formatted value feeds another calculation, wrap it in cleanNumericValue again before doing more math with it.


Date and Time Examples

Date fields do not recalculate until the cursor leaves them, so when testing any of these, click out of the date field to see the result.


Put today's date on the form
[DATE]
This one needs no curly braces and no function. [DATETIME] and [TIME] work the same way for the date and time, and the time on its own. However, square brackets (server variables) only set when the form loads and the value will not change when the date or time change in the form.


Number of days between two dates
{dateDiff('{StartDate}', '{EndDate}', 'd')}
The last part is the interval, and can be days, months or years, as well as hours, minutes and seconds. It counts forward, so a start date later than the end date gives a negative number.


A due date thirty days after an invoice date
{dateAdd('{InvoiceDate}', 0, 0, 30)}
The three numbers are years, months and days in that order, so pass 0 for the parts you are not changing. Negative numbers go backwards: {dateAdd('{InvoiceDate}', 0, 0, -10)} is ten days earlier.


A renewal date one year from today
{dateAdd('[DATE]', 1, 0, 0)}
Combining [DATE] with dateAdd is how you set any deadline relative to now without the user entering a starting date.


The end of a ninety-day probation period
{dateAdd('{HireDate}', 0, 3, 0)}
Three months rather than ninety days keeps the date on the same day of the month, which is usually what a policy actually says.


Age in whole years from a date of birth
{dateDiff('{BirthDate}', '[DATE]', 'y')}


Length of service in months
{dateDiff('{HireDate}', '[DATE]', 'm')}
To show it as years and months, use two fields, or {dateDiff('{HireDate}', '[DATE]', 'y')} years, {dateDiff('{HireDate}', '[DATE]', 'm') % 12} months.


Days remaining until something expires
{dateDiff('[DATE]', '{ExpirationDate}', 'd')}
This counts down, and goes negative once the date has passed.


Label something as expired
{dateDiff('[DATE]', '{ExpirationDate}', 'd') < 0 ? 'EXPIRED' : 'Current'}
The same shape covers any date-based status: warn at thirty days with {dateDiff('[DATE]', '{ExpirationDate}', 'd') < 30 ? 'Renew soon' : 'OK'}.


Days overdue, showing zero when nothing is overdue
{dateDiff('{DueDate}', '[DATE]', 'd') > 0 ? dateDiff('{DueDate}', '[DATE]', 'd') : 0}


Build a reference from the date
{dateYear('[DATE]')}-{dateMonth('[DATE]')}-{RequestNumber}
dateYear, dateMonth and dateDay pull the parts of a date out on their own. They can take a field instead of [DATE] - {dateYear('{InvoiceDate}')} gives the year of the invoice.


Hours between two Time fields
{timeDiff('{TimeIn}', '{TimeOut}', 'h')}
This gives whole hours only, so 8:00 to 16:30 gives 8. Use 'm' for the number of minutes, and leave the interval off entirely to get the elapsed time formatted as HH:MM.


Hours worked as a decimal, for payroll
{getFormattedNumber(cleanDivision(timeDiff('{TimeIn}', '{TimeOut}', 'm'), 60), 2)}
8:00 to 16:30 gives 8.50. This is the version to use whenever the hours are going to be multiplied by anything.


Pay for the shift
{formatCurrency(cleanDivision(timeDiff('{TimeIn}', '{TimeOut}', 'm'), 60) * cleanNumericValue('{HourlyRate}'), 2)}


Total hours across a table of entries
{getFormattedNumber(calcSum('{TimeSheet| |Hours}'), 2)}
Where each row already holds a decimal number of hours, this totals the week.


Show a time built from numbers
{formatTime(9, 30)}
Gives 9:30. Add a third number for seconds. The numbers can come from fields, so a form can assemble a time from separate hour and minute entries.


Things to watch with dates and times

A day count is rounded up when the values carry a time of day, so two dates a few hours apart count as one day. Date fields hold no time and are not affected; date and time fields are.

A month difference compares calendar months, not elapsed time, so the last day of January to the first day of February counts as one month.


dateAdd returns a date written in the format the user's browser uses, which is not necessarily the format the field displays. It is a date to read, and to feed to another date function - not something to compare as text.


The time functions expect times in twenty-four hour form. A Time field with its 12-Hour setting turned on stores an AM/PM value that timeDiff cannot read correctly, so leave that setting off on any time field being used in a calculation.


A blank date makes a date calculation meaningless rather than zero, so guard anything users will see before both dates are filled in - {'{EndDate}' == '' ? '' : dateDiff('{StartDate}', '{EndDate}', 'd')} leaves the field empty until there is something to count.