Calculations & Logic

Calculations and logic are powerful features that transform a static collection of data into a dynamic application that can self-update, re-calculate, and trigger workflows.

Calculations allow you to perform complex math on numeric and date fields using equations and formulas. You can access connected values to perform calculations. For example, you can calculate:

the available inventory of each product
the average sale amount for each customer
the due date for each invoice

Logic allows you to employ if/else decisions to trigger actions throughout your application. For example, based on different criteria you define, you can:

set and validate the values of records in your database
show and hide different features for your users
trigger emails and workflows

Calculations and logic are often combined to add advanced functionality, like hiding a registration form and displaying a message when the status of the event is full.

calculations&logic1

In this article, we will explore these options from a high level, show some examples in action, and point you to the resources you need to set up calculations and logic in your own apps.

Calculations

In Knack, you can access two types of fields to perform calculations:

Formulas perform calculations on connected records. For example, calculating the average order size of each customer.
Equations combine math functions with record values to calculate new values. For example, calculating an order total with tax.

Let’s review some examples of how formulas and equations can be used in your app.

Formulas

Formula fields run calculations on connected records. This means you must have a connection between two tables, where many child records can connect to a single parent record.

For example, let’s say you want to track the number of registrations for each event. You will need a Registration table connected to an Events table so that each Event record can have many connected Registration records.

Then, you will use a count formula in the Events table to total up the number of Registration records connected to each Event record.

There are five types of formula fields in Knack:

Sum: total up the values for a given field in all connected records.
Max: the highest value for a given field in all connected records.
Min: the lowest value for a given field in all connected records.
Average: the sum of all the values for a given field in all connected records, divided by the total number of connected records.
Count: the total number of connected records.

Here’s an example using a sum formula in an Orders table to get the order total by totaling up the subtotals of each connected Line Item.

calculations&logic2

Learn More

Equations

Equations combine math functions with record values to calculate new values. Any numeric or date fields in your records can be used in equations.

Like formulas, equations can reach across connections to perform calculations using the record’s value and values in any connected records.

The available functions open up a huge variety of calculations:

Math functions: many are available, such as ceil, max, sum, log, sum, and var
Date functions: used to extract numbers from date fields, such as the Month, Date, Quarter, or Year

Equations can also be used to perform calculations in your Live App in real-time as long as all values are in fields in the form.

Learn More

About Equations
About Numeric Equation Functions
About Numeric Equation Settings
About Connections
How to Start an Auto-Increment Field From Any Number

You can read about Complex Equations, Date Equations, Conditional Equations, Text Formulas, and more here in this article.