Calculations in Forms

Introduction

The calculation functionality enables users to derive real-time results from form inputs by applying mathematical expressions to fields, making forms smarter and more interactive.

This is specially useful for creating quotation forms ↗, payment forms ↗, tax calculators, quizzes, loan calculators, and much more.

Here are some real-world examples of how you can use calculations in your forms:

  • Quotation Forms: Calculate the total cost of a product or service based on user inputs.
  • Payment Forms: Compute the total amount to be paid, including taxes and discounts, based on the selected items.
  • Expense Reporting: Offer a table entry field where users can input multiple expenses, and calculate the total expense.
  • Loan Calculators: Ask for the loan amount, interest rate, and tenure to calculate the EMI.
  • Quizzes and Assessments: Create a quiz form that calculates the score based on the user’s answers.

Calculations can be combined with other features like dynamic lookups to create even more complex forms, such as a currency converter that fetches the latest exchange rates from an API based on the selected currencies, and then calculates the converted amount.

Where can you use calculations?

Unlike other form builders, you are not limited to special calculation fields. Math calculations can be used anywhere in your form. This includes, but is not limited to:

  1. Rich text content on your form
  2. As the value of a hidden field
  3. Within the formula column of table entry fields
  4. Labels and descriptions of fields
  5. The recipient, name, reply-to, subject, and content of email workflows
  6. The content of success messages
  7. Field mapping in workflows

How to use calculations

You can create a math calculation by typing {}. When editing the math calculation, our editor will automatically suggest the fields and functions available in your form.

Let’s take an example. We have a product order form with two input fields: Quantity and Price. Our calculated total will be the product of these two fields. We would also enforce a minimum order value of $100.

Here is the formula we used in the video:

MAX(Quantity * Price, 100)

Components of a calculation

A calculation can contain the following components:

  1. Fields
  2. Operators
  3. Functions
  4. Constants

Form fields

Fields are the input values that you want to use in your calculations. You can use any field in your form as a variable in your math calculations.

A calculation always expects a field to be numeric. If a field is not numeric, it will be treated as 0. There are also some other gotchas to be aware of when using fields in calculations:

  1. Multiple choice / dropdown field

    You can assign values to each option in a multiple choice or dropdown field. When a multiple choice field is referenced in a formula, the value used is the value assigned to the selected option.

    If your field supports multiple selections, the value used is the sum of the values assigned to the selected options.

  2. Datepicker field

    When a date field is referenced in a formula, the value used is the number of days since today. So if the date is in the future, the value will be positive, and if the date is in the past, the value will be negative.

    Example, if the user selects a date 10 days from today, the value used in the formula will be 10.

  3. Table entry field

    Within the formula column of a table entry field, you can reference other columns in the same row by their column names. Example: If you have a table with columns "Quantity" and "Price", you can create a formula column "Total" with the formula Quantity * Price to calculate the total for each row.

    Outside the table entry field, you can reference any table column. When a table column is referenced in a formula, the value used is the sum of all the values in that column. Example: If you have a table with a column "Total", you can create a hidden field with the formula @Table (Total) to calculate the grand total of all rows in the table.

  4. File upload

    You cannot use a file upload field in a math calculation.

Operators

We can also use the following operators in our calculation:

  1. + (addition)
  2. - (subtraction)
  3. * (multiplication)
  4. / (division)
  5. % (modulus)
  6. ^ (exponentiation)
  7. () (parentheses)

Expressions within parentheses are evaluated first. If there are multiple sets of parentheses, the innermost set is evaluated first.

Supported functions

You can also use built-in functions to perform more complex calculations. The following functions are supported:

FunctionDescription
MAXReturns the larger number of the given two numbers. 
MAX(field1, field2)
MINReturns the smaller number of the given two numbers. 
MIN(field1, field2)
FORMATFormats a number to include commas and decimals, based on the browser locale. So a number like 1234567.89 would be formatted as 1,234,567.89 in the US, and 1.234.567,89 in Germany. 
FORMAT(field1)
ABSReturns the absolute value of a number. 
ABS(field1)
REPLACEReplaces all occurrences of a substring or pattern within a string with a new substring.

Replace ‘old’ with ‘new’ in field1

REPLACE(field1, 'old', 'new')

Replace all spaces and single quotes with hyphens in field1

REPLACE(field1, '/[ \']/g', '-')
ROUNDRounds a number to a certain number of decimal places. A number like 1234.56789 would be rounded to 1234.57 if the second argument is 2.

Round also takes an optional third argument, which specifies the rounding method. It can be DOWN or UP.

ROUND(field1, 2)
SQRTReturns the square root of a number. 
SQRT(field1)
IFThis formula has a special syntax. It takes three arguments: the condition, the value if true, and the value if false. The condition can be a simple comparison, using > (greater than), < (less than), or = (equal to) operators.

The second and third arguments can be numbers, fields, or other functions. They can also be simple strings, enclosed in single quotes. These strings can contain alphabets and numbers.

If field1 is equal to field2, return field3, otherwise return field4

IF(field1 = field2, field3, field4)

If field1 is greater than field2, return ‘Message one’, otherwise return ‘Message two’

IF(field1 > field2, 'Message one', 'Message two')

Return field1 if it is not empty, otherwise return field2

IF(field1, field1, field2)

If selected year is 2023, return ‘Year 2023’, otherwise return ‘Year 2024’

IF(YEAR(field1)='2023', 'Year 2023', 'Not 2023')

Using IF with a mathematical expression (note the parentheses)

IF((field1 * 2) > 100, 'Exceeds 100', 'Within limit')

Using IF with dates (where field1 is a datepicker field)

IF(field1 > 2, 100, 'You cannot book less than 2 days in advance')

Using nested IF functions

IF(YEAR(field1) = 2023, IF(MONTH(field1) > 6, IF(DAY(field1) = 15, '15th of the second half', 'Not 15th, but second half'), 'First half of the year'), 'Not the year 2023')
CURRENCYFormats a number based on the specified currency code. 
CURRENCY(field1, USD)
LEFTReturns the left part of a string, up to the specified number of characters. 
LEFT(field1, 5)
RIGHTReturns the right part of a string, up to the specified number of characters. 
RIGHT(field1, 5)
MIDReturns the middle part of a string, starting from the specified position, up to the specified number of characters. 
MID(field1, 5, 3)
SPLITSplit a string and return the left or right part. 
SPLIT(field1, '.', 0)
UPPERConverts a string to uppercase. 
UPPER(field1)
LOWERConverts a string to lowercase. 
LOWER(field1)
DATEReturns the date in the specified format. 
DATE(field1, 'DD.MM.YYYY')
Return today in the format YYYY-MM-DD 
DATE(0, 'YYYY-MM-DD')
DAYReturns the day of the month for a given date. 
DAY(field1)
MONTHReturns the month of the year for a given date. 
MONTH(field1)
YEARReturns the year for a given date. 
YEAR(field1)

Some examples

Here are some practical examples:

  1. Adding two fields 
    field1 + field2

    This adds the values of field1 and field2.

  2. Multiplying a field value by a constant 
    field3 * 10

    This multiplies the value of field3 by 10.

  3. Using parentheses for order of operations 
    (field4 + field5) * field6

    This adds field4 and field5, then multiplies the result with field6.

  4. Combining functions and operators 
    MAX(field7, field8) + MIN(field9, field10)

    This calculates the maximum of field7 and field8, adds it to the minimum of field9 and field10.

  5. Using the IF function 
    IF(field11, field12, field13)

    If field11 is 1, the result would be field12, otherwise it would be field13.

  6. Tiered pricing 
    IF(field1 > 100, field1 * 0.9, IF(field1 > 50, field1 * 0.95, field1))

    This applies a tiered discount to a price: 10% discount for orders over 100 units, 5% discount for orders over 50 units, and no discount otherwise.

Best practices

Calculations can quickly become complex and unwieldy. Here are some best practices to keep your calculations manageable:

  1. DRY - Don't Repeat Yourself

    If you find yourself using the same calculation in multiple places, consider creating a hidden field with the calculation and using that field in your form.

    For example, you can create a hidden field labelled Total which calculates the total price of an order. You can then use this field in your form, email workflows, and success messages.

  2. Use staged calculations

    Break down complex calculations into stages. Use intermediate fields to store the results of intermediate calculations. This makes your calculations easier to understand and debug.

    An excellent example is the mortgage calculator form template ↗. You can break down the calculation into stages: future value factor, monthly amount, and monthly payments (formatted).