#### Table of Contents

# Equations

Updated by Lesley

### What is an Equation?

Equation fields work by combining field values, numbers, and other mathematical functions to create an equation that will be calculated for each record. They are a type of a **field** on an **table** within the **Data** section of the **Builder**.

There are two equation types available: Number and Date.

Numeric Equation example: (Sales - Expenses) * 12

Date Equation example: {Date Completed} - {Date Started}

#### What does an equation look like in a live app?

### Manage Equations

#### Add an Equation

From the Data section in the Builder, select the table you want to add your field to from the left menu. Then, click the ‘Add Field’ button at the top of the page. This will allow you to select the "Equation" field type from the left-side of the screen:

Once you select the ‘Equation’ field type, a popup will appear where you can edit your Equation field’s settings. Once you have setup the field, click on the ‘Add Field’ button at the bottom of the pop-up:

#### Edit an Equation

To edit an existing Equation, select the table that contains the Equation field then click on the more options icon (the “...”) next to the field’s name.

#### Delete an Equation

To delete an Equation, select the table that contains the Equation field then click on the more options icon (the “...”) next to the field’s name. Select the ‘Delete’ option from the dropdown:

#### Build an Equation

Equations can take many different forms. See the Equation Types section for details on these options.

To build to your equation:

- Type the name of the field directly in the formula input box
- Use the ‘Fields’ dropdown list to select any available fields, or
- Type numbers or mathematical operators directly into the input box

Below the equation editor, the Example Output shows you an example output of the current formula:

##### Access Connected Fields

As with text formulas, equations give you the option of retrieving data from connected records. If you start typing the name of a field on a connected table, you'll see it appear among the suggestions with the format of **Field connected with Table > Connected Table**.

##### Use Multiple Choice Fields in Equations

Multiple choice fields can be used in equations. These values convert to numbers that will be used in the equation. Any non-numeric characters are stripped and the remaining number will be used. If no number is left then zero will be used.

##### Use Yes / No Fields in Equations

Yes / No fields can be used in equations. These values convert to numbers that will be used in the equation: No = 0, Yes = 1.

##### Using Equations on Live App Forms

Requirements

**All fields**used in the equation need to be present in the form. (Similarly, if you're setting a value in a record rule from a formula field, that field must be present on the form as well.)- The equation must be a Numeric equation (not a
**Date Type**). - The equation can not use other formulas (
**sum**,**count**, etc.)

Equation fields can be added to forms to simulate real-time calculators. Here's what a simple example looks like:

##### Operators

You can use the following operators in your equations:

Operator | Meaning |

+ | adds |

- | subtracts |

* | multiples |

/ | divides |

== | equals |

!= | not equals |

> | greater than |

< | less than |

>= | greater than or equal to |

<= | less than or equal to |

& | and |

| | or |

#### Order of Operations

If your equation field depends on other formulas or fields with conditional rules, you must list it after these fields since Knack processes those in order that they’re listed in the table. Another way to say this is that if an equation depends on a field, field with a conditional rule, or another equation, those fields must be listed **above** the equation that uses it.

### Equation Types: Settings and Functions

#### Numeric Equations

Numeric equations can calculate field values, numbers, and other mathematical functions and format results in various number formats with decimals, rounding, currency, etc. Activate a numeric equation by setting the "Equation Type" option to Numeric.

##### Numeric Equation Settings

Many settings exist for numeric equations. See full list of numeric equations settings here.

##### Numeric Equation Functions

Many regular math functions (such as "ceil" or "max") can be used in Knack numeric equations. Others, like "getDateDayOfMonth" provide numeric values for a date. Read more here.

#### Date Equations

Date equations can calculate dates and format results as hours, days, weeks, etc. Activate a date equation by setting the "Equation Type" option to Date.

Selecting the ‘Date’ equation type reveals the ‘Date Type’ and Result Type options.

##### Date Equation Settings

Many options exist for date equations. See full list of date equations settings here.

##### Date Equation Functions

**currentTime():** Returns the current Date and Time according to your App's Timezone settings.

### Detailed Equation Examples

#### Conditional Equations

Your equation can accept conditional rules using the ternary operator. A ternary operation allows three operands and can be used as a shortcut for the if statement. An example:

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

Note that you can nest ternary statements to have more than two conditions/results like so:

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

Also, you can use an **&** symbol to represent AND and a | symbol to represent OR in an equation like so:

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

It is not possible to evaluate "is blank" or "is not blank" directly within a conditional equation.

##### Conditional Example 1

There may be times where you need to track total elapsed time. This could can easily be done using the currentTime() function - but what happens when your date field is blank? A blank date would be interpreted as 0, which Knack would interpret as January 1, 1969, resulting in a very large negative number. Using a conditional rule we can make sure our equation remains 0 instead:

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

##### Conditional Example 2

This example shows how to apply an additional 3% late fee to a charge:

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

##### Conditional Example 3

To trigger an equation when values are equal you can use the == operator:

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

##### Conditional Example 4

This complex example uses the following 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

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

##### Conditional Example 5

This complex example uses the following 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

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

#### Conditional equations with text fields

Conditional rules don’t currently work with text fields, but you can map your text fields to number fields and use the number fields to in your equation. Assuming you already have a short text field called **Text**:

- Add a number field (we’ll call it
**Num**) - Add a conditional rule to the
**Num**for each option you want to account for**Text**; e.g. set**Num**to 1 if**Text**is First Choice, set**Num**to 2 if**Text**is Second Choice, etc. - Run a batch update on your data (even on a new temporary field) to populate the
**Num**field via its conditional rules - Use
**Num**in place of**Text**in your equations

##### Conditional Equation With Text Fields Example

If we want the following conditions tested:

- If
**Text**is*First Choice*, ie:**Num**is 1, set**equation**to 1 - If
**Text**is*Second Choice*, ie:**Num**is 2 set**equation**to 2 - If
**Text**is anything else, set**equation**to 0

The equation would need to use the **Num** field since **Text** fields can’t be used in equations, so it will look like this:

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

### Use Equations in Your App

#### Use Equations in Equation or Text Formula fields

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

#### Viewing Equations in the Builder

Equation values will display in the "Records" tab in an table in your builder. Here you can view and reference these values, as well as using record filters to display particular values for this equation field.

#### Viewing Equations in the Live App

With the exception of Maps, Menu and Rich Text views, Equation fields can be added and used to display values in all page views.

#### Live App Form Calculations

Equation fields can be added to forms to simulate real-time calculators.

##### Requirements

**All fields**used in the equation need to be present in the form as well.- The equation can
*not*be a**Date Type**equation. - The equation can not use other formulas (
**sum**,**count**, etc.). Other equation fields and Yes/No fields can be used in equations on a Live App form calculation, provided they follow the other requirements above.

Here's what a simple example looks like:

### How To Guides

### 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 example, let's say that many Tasks can be connected to one Project, and there's a number field called Hourly Rate in the Project table.

You can create an equation in the Tasks table that pulls in the value from the Hourly Rate field in the Project's table, because there's only one hourly rate value to pull in.

However, if you had a number fields called Hours Worked in the Tasks table, you wouldn't be able to access that field in an equation in the Projects table. Because there would be multiple Hours Worked values to pull in and an equation field can't do that.

In a case like that, you could use a formula field to access all the connected Hours Worked values. Then the formula field could be used in the equation.