Table of Contents

Formulas

Danielle Kellogg Updated by Danielle Kellogg

What is a Formula Field?

Formula fields are used to run calculations on connected records and store the total of that calculation. The following are common examples of when formulas are used:

  • Order Total: an Order record sums the prices of all the Line Item records connected to that Order.
  • Average Order: a Salesperson record averages the total of all the Orders records connected to that Salesperson.
  • Employee Count: a Company record counts all the Employee records connected to that Company.

Formula Field Types

The following are different types of formulas you can add:

  • Sum: all records for a given field on a connected object added together.
  • Max: the highest value for a given field on a connected object.
  • Min: the lowest value for a given field on a connected object.
  • Average: the sum of all the records’ values for a given field on a connected object divided by the total number of records.
  • Count: the total number of connected records.

What do I need to use formula fields?

When you add a formula field, you must select from a list of eligible fields on which you will run the calculation. Ineligible fields will not appear as options.

In order to be eligible, the field type must be:

  1. Numeric (e.g. Number, Equation).
  2. The object on which you add the formula field must be connected to the object on whose field data you want to run your calculation.
    That connection must allow for many records to be connected to the object to which you are adding your formula field; for example, a Sale connected to many Line Items could use formula fields to tally up data for a specific Line Item field.

Manage Formula Fields

Add a Formula Field

Formula fields can be added to objects from the Schema section of the Builder by clicking "Add Field" in the top menu. From here, select any of the fields indicated below.

If you do not see these options, the object in question is not connected to many of any other object.
Fields can also be added while in the Records section of the Builder, by choosing to insert a record to the right or left oft he column being edited.

Edit a Formula Field

From the Schema section in the Builder, select the object where you want to edit your formula field. To edit, click on the "..." more options icon, then select "Settings" to edit the field.

Delete a Formula Field 

From the Schema section in the Builder, select the object where you want to delete your formula field. To delete, click on the "..." more options icon on the field and select 'Delete' from the dropdown menu.

Deleting a field is destructive and will cause all of its values to be removed, so confirm this is the correct action in your app before proceeding. If this field is used in any other equations or text formulas, it will also remove those fields.

Build a Formula Field

Formula fields are used to run calculations on connected records and store the total of that calculation. See the Formula Field Examples section below for specific examples on how to build a formula field.

To build your formula field: 

First, check for an eligible field.

  • Object to use in formula. The object where the formula field will be placed must be connected to an object where a numeric field that you want to sum/count/etc.
  • Field to calculate. Make sure the field type for which you want to run the formula on is a numeric one (e.g. number, equation).

Next, add Formula Field.

  • Add field. On the "host" object, add the formula field (Sum/Average/Minimum/Maximum/Count).
  • Choose field for which the formula will be calculated on. Eligible fields will appear in the dropdown when you add the field.

  • Add Filters (optional): Filters can be added to your formula fields so that only the records that are relevant to you are summed. When using multiple filters on the same formula, they must all be true for a record to be displayed. An example of this in action would be adding a formula filter to a sum field that only sums records from the last year.

Formula Field Examples

Order Total

In this scenario you want to find the total of all connected Line Items in an Order. You can use a sum formula field to calculate the sum of all the Total fields in the Line Item object that is connected to your Order object.

Here you can see the field is summing the Total field on the Line Item object which is connected via the Purchase Order connection field.

Average Order

In this scenario you want to find the average of all connected Orders for every Sales Rep. You can use a average formula field to calculate the average of all the Total fields in the Order object that is connected to the Sales Rep.

Here you can see the field is averaging the Total field on the Order object which is connected via the Sales Rep connection field.

Employee Count

A Count field that will display a count of all the Employee object records for that connected Company.

In this scenario you want to find the number of Employees for every Company. You can use a count formula field to count the number of Employee records connected to every Company record.

Here you can see the field is counting the Employee records which is connected via the Sales Rep connection field.

Using Formula Fields in Your App

Using Formulas in Equations or Text Formula fields

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

Notes & Troubleshooting

Formula fields calculate information from other fields, so having information about how these calculations process and the order in which they process is important information to keep in mind. 

Processing: Calculations

If you add a formula field to an object with existing records, the formula will start calculating for those records immediately. If an object has many records, these calculations could take some time, so please be patient.

Formulas that do not process any connected values should show a 0 value, rather than blank. If your values show as blank they still may be processing, but if they appear to never complete, reach out to us at support@knack.com to take a look.

Processing: Order

You can add formulas based on other formulas and equations. When a record is updated, it takes place in the following order:

  1. All equations are updated first.
  2. Then any equations and formulas from parent records connected to the updated records. If those connected records have additional records connected to them, those formulas will also update.

Processing: Scheduled Time

Formula fields are processed (calculated) at :01 on your app's timezone. Values for formula fields are processed on individual records any time other edits are made to the record as well.

Calculation limits on connection fields

In order to optimize performance, formula fields (eg. Sums, Counts, etc.) which include filters are currently limited to processing 10,000 records.If the filters bring the count of the connected records below 10,000 records, the formula will correctly calculate against those records. A formula field that includes a filter that tries to calculate a connected object with more records than this will display inconsistent results. 

How To Guides

How did we do?

Numeric Equation Settings

Equations

Contact