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.

This article covers the following topics:

• Overview

• Calculations

• Formulas

• Equations

  • Learn More

 

Overview

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:

• Available inventory of each product

• 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.

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 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.

 

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.

Knack offers users five different types of formula fields to choose from:

• Sum: Calculate the total value of a specified field across all connected records.​

• Max: The greatest value for a specified field among all connected records.

• Min: The lowest value for a specified field in all connected records.​

• Average: The sum of all the values for a specified 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.

 

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.

Learn More:

• Equation Types & Logic

• ​​About Equations​​

• ​​About Numeric Equation Functions​​

• ​​About Numeric Equation Settings​​

• ​​About Connections

• ​​​​How to Start an Auto-Increment Field From Any Number​

  
  
  

---