Table of Contents
Calculations & Logic
Updated
by Lesley
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.
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, like calculating the average order size of each customer.
- Equations combine math functions with record values to calculate new values, like 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 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.
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 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
- About Equations
- About Numeric Equation Functions
- About Numeric Equation Settings
- About Connections
- How to Start an Auto-Increment Field From Any Number
Complex Equations
Our project management app shows a more complex implementation of calculations. Each Project record has Hours and Costs records connected to it, represented the staff time and materials cost associated with completing the project.
We can calculate the total cost of a Project, including both labor and materials, using a mix of equations and formulas that perform calculations across multiple tables.
The possibilities are near limitless with how complex you can build your equations, this is just one relatively straightforward example.
Learn More
Date Equations
Date equations enable you to perform calculations on date fields and store the result as either a number or date.
For example, you can calculate the warranty expiration date of an item by adding the warranty duration to the purchase date. The result will be stored as a date.
You could also subtract two dates together and store the result as a number. To calculate the number of days remaining on an item’s warranty, subtract the purchase date from the warranty date.
Learn More
- About Date Equation Settings
- How to Calculate Age in Years From a Birthday
- How to Notify a Use That an Item is Ready for Review
Conditional Equations
Conditional equations enable you to perform different calculations based on predefined criteria using a single equation field. This is well illustrated in certain ordering scenarios:
- Charge an extra 3% fee of the order total only if an invoice is paid after the due date.
- Offer a bulk discount of 10% on the order total only if the quantity ordered is above a certain threshold.
Learn More
Text Formulas
Text formulas don’t apply math, but they can be useful for formatting and displaying your data in specific ways. You can use text formulas to extract parts of a field and combine multiple fields into a single field.
For example, you may use a text formula to create a unique ID field for your users by concatenating their name and combining that with an auto-increment field. So John Smith’s unique ID may be “JSMITH-1234.”
Learn More
Logic
Logic allows you to employ if/else decisions to trigger actions throughout your application. You do these by defining rules and deciding what will happen when those rules are triggered.
The logic features we’ll cover here include the following:
- Conditional rules: used to set record values
- Validation rules: used to enforce record value criteria
- Page rules: used to control how pages display
- Display rules: used to control how views display
See the below examples for different areas in Knack where you can apply this type of logic.
Set Record Values
Conditional rules are logic you can add to your fields to set the values. When a field has conditional rules, the value is set dynamically based on the rules. This means fields with conditional rules are not editable in the Live App.
For example, in our Inventory Manager app, we need to know when to reorder a specific product, and we want that to be automatically updated when the inventory levels change.
We’ll call this field “Needs Reorder.” We can set this to “Yes” or “No” with conditional rules that compare the “On Hand” field to the “Minimum Required” field.
If “On Hand” is greater than “Minimum Required,” set Needs Reorder to “No.” However, if “On Hand” is less than “Minimum Required,” set Needs Reorder to “Yes.”
These conditional rules will automatically run whenever the “On Hand” value changes, ensuring that the value is always up-to-date.
Learn More
Prevent Bad Data
Validation rules are used to ensure that the values of specific fields meet the criteria you define.
Validation rules allow for more granular restrictions beyond the basics field settings that ensure a value is required or must be unique. For example, you can validate:
- what characters a value can and cannot contain, start with, or end with
- the minimum and maximum number of characters a value can have
- what type of file can be uploaded
When a value is entered into a form that doesn’t comply with a validation rule, an error message will display and the user will not be able to submit the form until the value is corrected.
For example, if you want to restrict the size of a resume that job applicants can upload to a job portal, you can add a validation rule to ensure those files are no bigger than 20MB.
Learn More
Control How Users Interact With Pages
Page rules are a form of logic that applies to individual pages. They are used to control how users interact with those pages based on the rules you define.
You can use page rules to:
- show/hide views
- show messages and alerts
- redirect to other pages
For example, in our Volunteer Management app, volunteers can sign up for available jobs. When a job is no longer available, we use page rules to hide the registration form and display a message that the job has been filled.
Page rules can go a long way toward simplifying your app and your hands-on management of it. With page rules, you can enforce view-based permissions on a more granular level than with logins. This allows you to minimize the number of pages you need to build and manage while still allowing multiple user roles to access the same page.
One thing to keep in mind when creating page rules is that that all the criteria must be met in order to trigger an action. For example, if you want to hide an edit form for any Projects that are “Pending Approval” or “Closed,” you’ll need to set up two page rules. One to hide the edit form when the status is “Pending Approval,” another to hide the edit form when the status is “Closed.”
Learn More
- About Page Rules
- Volunteer Management Example App
- How to Create a Registration with Limited Availability
- How to Display a Message When a Deadline is Approaching
Control How Forms and Views Display
Display rules are a form of logic that can apply to individual views, like forms or grids. They can be used to control which parts of the view are visible.
You can use display rules to:
- show/hide fields based on other field values in form views
- change the label of an input in form views
- add styles, like colors and icons, to fields in grid, list, and details views
One example is a contact form when a business asks where you heard of them. You can check the “Other” option to reveal a short text field for you to write-in a custom option.
Another example is with our Inventory Manager app. We want to make any products that need reordering obvious at a quick glance. Using display rules, the “Needs Reorder” field is highlighted with a bright background color and emergency icon when the value is “Yes.”
Learn More
- About Display Rules in Forms
- About Display Rules in Grids
- Inventory Manager Example App
- How to Build an Inventory Manager
Combining Calculations and Logic
There are limitless options for the ways you can use and combine calculations and logic in your apps.
For example, in our Inventory Manager app, the following calculations and logic are combined:
- “Received” and “Shipped” fields are formula fields that count inventory totals.
- “On Hand” is an equation that subtracts “Received” from “Shipped.”
- “Needs Reorder” uses conditional rules to set the value to Yes when “On Hand” is less than “Minimum Required”
The Products grid uses display rules to highlight records where “Needs Reorder” is “Yes” with a yellow background and warning icon.
This flexibility opens up endless possibilities of adding rich functionality to your applications.
Learn More
- Inventory Manager Example App
- How to Build an Inventory Manager
- How to Set a Random ID
- How to Create a Registration Form with Limited Availability
Other Areas to Apply Logic Rules
There are a few more features that also let you apply rules to trigger different actions:
- About Scheduled Tasks - update records or send emails based on a regular schedule.
- About Record Rules - update records when a form submits.
- About Email Rules - send emails when a form submits.
In addition, as you explore Workflow & Automation and Users & Access you’ll notice that calculations and logic are both important for implementing certain features.
