Equation Fields

What You'll Learn

This comprehensive guide will walk you through setting up and using equation fields in your Knack app. You'll discover how to create dynamic calculations, work with dates, implement conditional logic, and incorporate equations into your forms. By mastering equation fields, you'll be able to automate calculations and derive valuable insights from your data without manual intervention.

What Are Equation Fields?

Equation fields function by merging field values, numbers, and other mathematical functions to construct an equation that will be calculated for each record. These fields are a specific type of field found in a table within the Data section of the Builder.

There are two equation types available:

• Number Equation example: (Sales - Expenses) * 12
• Date Equation example: {Date Completed} - {Date Started}

Managing Equations

Adding an Equation Field

To add an equation field to a table in the Builder's Data section:

1. Select the desired table from the left menu
2. Click on the "Add Field" button located at the top of the page
3. Choose the Equation field type from the left side of the screen
4. Configure the settings for your Equation field in the popup window
5. Click on the "Add Field" button at the bottom of the popup

Editing an Equation

To modify an existing equation:

1. Choose the table that includes the equation field
2. Select the additional options icon (represented by "...") located beside the field's name
3. Select "Edit" from the dropdown menu

Deleting an Equation

To remove an equation:

1. Navigate to the table that contains the equation field
2. Click on the additional options icon (represented by "...") next to the field's name
3. Select the "Delete" option from the dropdown menu

Caution: Deleting a field will result in the removal of all record values associated with that field. Confirm that this is the correct action for your app before proceeding.

Building an Equation

Equations can take many different forms depending on your needs. To build your equation:

1. Type the name of the field directly in the formula input box, or
2. Use the "Fields" dropdown list to select any available fields, or
3. Type numbers or mathematical operators directly into the input box

Below the equation editor, the Example Output shows you a preview of the current formula's result.

Accessing Connected Fields

Equations give you the option of retrieving data from connected records:

1. Start typing the name of a field on a connected table to see available options in a list, or
2. Select the "Fields" dropdown and choose the field

Fields available will include any fields from the current data table and any fields from connected tables.

Note: Fields from tables connected with a many-to-many relationship will not be available since multiple connected record values exist.

Tip: Check out our quick guide on how to include connected values in equations: How to Include Connected Values in Equations

Using Multiple Choice Fields in Equations

Multiple choice fields can be utilized in equations, with values automatically converted into numerical values for calculation:

• Non-numeric characters are automatically removed and the remaining value is used for calculation
• If no number remains, zero will be used as the default value

Note: Equations using multiple choice fields will not display live results in forms, even if the multiple choice field is used in the form.

Using Yes/No Fields in Equations

Yes/No fields can be utilized in equations with the following conversions:

• No is represented as 0
• Yes is represented as 1

Using Equations in Forms

Equation fields can be incorporated into forms to create dynamic calculators that update in real time.

Requirements:

• All fields required for the equation must be included on the form
• If you are setting a value in a record rule using a formula field, that field must also be present on the form
• The equation must be a Numeric equation (not a Date Type)
• The equation cannot use other formulas (sum, count, etc.)

Note: Currently, form submissions cannot be validated using equation fields.

Operators

You can use the following operators in your equations:

Order of Operations

If your equation field relies on other formulas or fields with conditional rules, it is important to arrange them in the correct order. Knack processes these fields in the order they are listed in the table under the "Fields" section.

In simpler terms, if an equation depends on a field, a field with a conditional rule, or another equation, make sure to list those fields above the equation that utilizes them.

Equation Types: Settings and Functions

Numeric Equations

Numeric equations allow you to calculate field values, perform mathematical functions, and format results in different number formats, including decimals, rounding, and currency.

To activate a numeric equation, simply select the "Equation Type" option and set it to "Numeric".

Note: Result Format options in equation fields' settings such as decimals and rounding are for display only. For example, an equation that uses division will store all the extra decimals in the database, regardless of the Result Format settings applied.

When using the round, ceil, or floor function, the resulting value will be stored in the database. This is recommended for achieving accurate rounding in your equations.

Numeric Equation Settings

There are numerous settings available for numeric equations. See a full list of numeric equation settings in our documentation.

Numeric Equation Functions

Many regular math functions can be used in Knack numeric equations:

• Standard functions (ceil, floor, max, min, etc.)
• Date-related functions that provide numeric values (getDateDayOfMonth, etc.)

Note: If the "currentTime()" function is used on an equation set up in the Numeric Equation Type, the value will only be reprocessed on record update. If you need this to process on a schedule, use the Date Equation Type.

Date Equations

Date equations are capable of performing calculations involving dates and formatting the results in various units such as hours, days, weeks, and more.

To enable a date equation, select the "Equation Type" option and set it to "Date". This reveals the "Date Type" and "Result Type" options.

Note: When blank date values are used in an equation, they are considered as "1/1/1970", which is the default start date for date values in Knack.

Date Equation Settings

There are a variety of options available for date equations. See the full list of date equation settings in our documentation.

Date Equation Functions

See our comprehensive list of date equation functions in our documentation.

Detailed Equation Examples

Conditional Equations

Your equation can accept conditional rules using the ternary operator, which allows three operands and can be used as a shortcut for the if statement:

Condition ? Equation_1 : Equation_2

This means:

• If your condition is true, Knack will use the equation or value followed by the "?" symbol
• If your condition is false, Knack will use the second equation or value followed by the ":" symbol

You can nest ternary statements to have more than two conditions/results:

Condition A ? Result 1 : (Condition B ? Result 2 : Result 3)

You can use an "&" symbol to represent AND and a "|" symbol to represent OR:

Condition A > 0 & Condition B > 10 ? Equation 1 : Equation 2

Note: Unfortunately, it is not feasible to evaluate whether a field "is blank" or "is not blank" within a conditional equation.

Conditional Example 1: Handling Blank Dates

When tracking total elapsed time, handle blank date fields to avoid incorrect calculations:

{Invoice Date} > 0 ? {Invoice Date} - currentTime() : 0

This ensures the equation remains "0" when the Invoice Date is blank, instead of using January 1, 1970.

Conditional Example 2: Late Fee Application

Apply an additional 3% late fee to a charge when payment is late:

{Time of Payment} > {Due Date} ? {Cost} * 1.03 : {Cost}

Conditional Example 3: Budget Period Selection

Trigger different date selections based on budget period using the "==" operator:

{Budget Period} == 1 ? {Q1 Date} : {Budget Period}==2 ? {Q2 Date} : 0

Conditional Example 4: Dynamic Pricing Based on Payment Timing

This complex example implements dynamic pricing:

{Time of Payment} > {Due Date} ? ({Cost} * 1.03) : ({Time of Payment} < {Due Date} ? {Cost} * 0.99 : {Cost})

This logic:

• If Time of Payment is later than Due Date, charge an extra 3%
• If Time of Payment is earlier than the Due Date, charge 1% less
• Otherwise (if Time of Payment equals Due Date), charge the normal amount

Conditional Example 5: Date Difference Calculations

Calculate date differences with appropriate handling of blank fields:

{End Date} > 0 & {Start Date} > 0 ? {End Date} - {Start Date} : {Start Date} > 0 ? currentTime() - {Start Date} : 0

This logic:

• If Start Date is blank, then nothing should be calculated
• If End Date is blank, then calculate the difference between the current date and the Start Date
• If Start Date and End Date are both populated, then calculate the difference between the End Date and the Start Date
• Otherwise (if they're both blank), set the value to zero

Conditional Equations with Text Fields

Since conditional rules don't directly work with text fields, you can map text fields to number fields for use in equations:

1. Add a number field (we'll call it "Num")
2. Add a conditional rule to the Num field for each text option (e.g., set Num to "1" if Text is "First Choice")
3. Run a batch update on your data to populate the Num field via its conditional rules
4. Use Num in place of Text in your equations

Example Implementation

If we want the following conditions tested:

• If Text is "First Choice" (Num is 1), set the equation to "1"
• If Text is "Second Choice" (Num is 2), set equation to "2"
• If Text is anything else, set the equation to "0"

The equation would be structured as:

{Num} == 1 ? 1 : {Num} == 2 ? 2 : 0

Using Equations in Your App

Use Equations in Equation or Text Formula Fields

Just as with any other field on a table connected to another table, equation fields can be used in text formulas or other equation fields.

Viewing Equations in the Builder

Equation values will be displayed in the "Records" tab in a table in the Builder. Here you can view and reference these values, as well as use record filters to display particular values for this equation field.

Viewing Equations in the Live App

Equation fields can be added and used to display values in all page elements except for maps, menus, and rich text elements.

How-To Guides

• Calculate Age in Years from a Birthday
• How To Start an Auto Increment Field from Any Number

Troubleshooting

Can't Access Connected Field

If you're unable to access a connected field in a formula, check the connection between the two tables:

• You can only access fields from a table with a many-to-one or one-to-one relationship with the table you're adding the equation field to
• For many-to-many relationships, use a formula field instead of an equation field to access all the connected values

Example:

• In a Tasks-to-Projects relationship where many Tasks connect to one Project:
  • You can create an equation in the Tasks table that pulls in the "Hourly Rate" from the Project
  • You cannot create an equation in the Projects table that accesses "Hours Worked" from multiple Tasks (use a formula field instead)