This article will provide a guide on how to use Formula fields in your Knack app.
What is a Formula Field?
Formula fields are utilized to perform calculations on connected records and store the resulting total. Here are a few typical instances where formulas come into play:
- 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
Here are several types of formulas that can be added:
- Sum: All records for a given field on a connected table added together.
- Max: The highest value for a given field on a connected table.
- Min: The lowest value for a given field on a connected table.
- Average: The sum of all the records’ values for a given field on a connected table 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 table on which you add the formula field must be connected to the table on whose field data you want to run your calculation. That connection must allow for many records to be connected to the table 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.
Managing Formula Fields
Adding a Formula Field
Formula fields can be added to tables from the Data section of the Builder by clicking "Add Field" in the top menu. From there, select any of the fields indicated in the image below.
Note: If you do not see these options, the table in question is not connected to many of any other table.
Note: Fields can also be added while in the Records section of the Builder, by choosing to insert a field to the right or left of the column being edited by hovering your cursor next to the field name and selecting the arrow down button.
Editing a Formula Field
From the Data section in the Builder, select the table where you want to edit your formula field. To edit, click on the "..." more options icon, then select "Settings" to edit the field.
Deleting a Formula Field
From the Data section in the Builder, select the table 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.
Caution: Removing a field is a significant action that will result in the removal of all its values. Therefore, it is crucial to confirm that this is the correct action to take in your app before proceeding.
Additionally, please note that if this field is being used in any other equations or text formulas, those fields will also be removed.
Building 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.
- Table to use in formula: The table where the formula field will be placed must be connected to a table 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 your Formula Field:
- Add field: On the "host" table, 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): You can customize your formula fields by adding filters to ensure that only the relevant records are included in the calculations. When using multiple filters on the same formula, all the filters must evaluate to true for a record to be included.
- For example, you can add a formula filter to a sum field that only includes records from the last year.
Formula Field Examples
Order Total
In this particular scenario, you may need to determine the overall total of all the Line Items that are connected to an Order. To achieve this, you can utilize a sum formula field that calculates the sum of the Total fields in the Line Item table, which is connected to the Order table.
This field calculates the sum of the Total field on the Line Item table, which is connected through the Purchase Order connection field.
Average Order
To determine the average of all connected Orders for each Sales Rep, you can utilize an average formula field. This formula field calculates the average of the Total fields in the Orders table that are connected to the Sales Rep.
This demonstrates the calculation of the average of the Total field on the Order table, which is connected through the Sales Rep connection field:
Employee Count
In this scenario, we 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.
This field counts the number of Employee records that are connected through the Sales Rep connection field.
Using Formula Fields in Your App
Just as in any other field on a table connected to another table, formula fields can be used in text formulas or equation fields. Refer to the linked articles for guidance on how to implement formula fields in text formulas or equation fields.
Notes & Troubleshooting
It is essential to understand the process and order in which formula fields calculate information from other fields. This knowledge is crucial to keep in mind while working with formula fields.
Processing: Calculations
When you add a formula field to a table with existing records, the formula will begin calculating for those records right away. If the table contains a large number of records, the calculations may 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 our support team via the chat widget in the Builder or by submitting this form for them 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. 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 table with more records than this will display inconsistent results.