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.
  • Tax Calculators: Create forms that demonstrate how taxes are calculated based on the user’s income.
  • 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. The value of hidden fields
  3. Labels and descriptions of fields
  4. The recipient, name, reply-to, subject, and content of email workflows
  5. The content of success messages
  6. 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

    You cannot use a table entry field in a math calculation.

  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:

Function Description
MAX Returns the larger number of the given two numbers. 
MAX(field1, field2)
MIN Returns the smaller number of the given two numbers. 
MIN(field1, field2)
FORMAT Formats 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)
ABS Returns the absolute value of a number. 
ABS(field1)
ROUND Rounds 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)
SQRT Returns the square root of a number. 
SQRT(field1)
IF This 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(DATE(field1), 'Year 2023', 'Not 2023')
CURRENCY Formats a number based on the specified currency code. 
CURRENCY(field1, USD)
LEFT Returns the left part of a string, up to the specified number of characters. 
LEFT(field1, 5)
RIGHT Returns the right part of a string, up to the specified number of characters. 
RIGHT(field1, 5)
MID Returns the middle part of a string, starting from the specified position, up to the specified number of characters. 
MID(field1, 5, 3)
SPLIT Split a string and return the left or right part. 
SPLIT(field1, '.', 0)
UPPER Converts a string to uppercase. 
UPPER(field1)
LOWER Converts a string to lowercase. 
LOWER(field1)
DAY Returns the day of the month for a given date. 
DAY(field1)
MONTH Returns the month of the year for a given date. 
MONTH(field1)
YEAR Returns 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).