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:
- Numeric (e.g. Number, Equation).
- 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
To add a formula field, first click on an object in your builder’s Data section, and then hover your mouse over the Number field category. From here, select any of the fields indicated below.
Note: If you do not see these options, the object in question is not connected to many of any other object.
Edit a Formula Field
From the "Data" section in the Builder, select the object where you want to edit your formula field. To edit, click on the gear icon on the field.
Delete a Formula Field
From the "Data" section in the Builder, select the object where you want to delete your formula field.To delete, click on the gear-shaped icon on the field and select 'Delete' from the dropdown menu.
Warning: 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:
- Check for eligible field.
- Object to use in formula. 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. 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).
- Connection field. Make sure the connection field connecting the two objects contains a value (in other words, is connected to the object you want to run the formula on).
- 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. 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
A Sum field that will display the sum of all Line Item Total object records for that Order connected through the Order connection field.
- Object used in formula = Line Item
- Connection field on the Line Item object = Order
- Field on the Line Item object to Sum = Line Item Total
Field view
Record view
Average Order
An Average field that will display the average for all the Order records for that Salesperson connected through the Salesperson connection field.
- Object used in formula = Order
- Connection field on the object = Salesperson
- Field on the object to Average = Order Total
Field View
Record View
Employee Count
A Count field that will display a count of all the Employee object records for that connected Company.
- Object used in formula = Employees
- Connection field on the object = Company
- Field on the object to Count = Employee
Field View
Record View
Using Formula Fields in Your App
Builder: Fields > 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.
Builder: Data > Records
Formula field values will display in the "Records" tab in an object in your builder. Here you can view and reference these values, as well as using record filters to display particular values for this formula field.
Builder: Pages > Views
Formula fields can be inserted onto most page views and then the valued can be displayed on your Live App.
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:
- All equations are updated first.
- 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. A formula field that includes a filter that tries to calculate a connected object with more records than this will display inconsistent results.