Formula Fields

What You'll Learn

You'll discover how to create formula fields that perform calculations on connected records, understand the different formula types available (sum, average, max, min, count), and learn how to implement filters for more targeted calculations. This knowledge will help you automatically generate totals, calculate averages, find maximum or minimum values, and count records across connections without manual effort.

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

Requirements for Using 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.

To be eligible, the field type must be:

• Numeric (e.g. Number, Equation)

📘

The table where you add the formula field must be connected to the table containing the data you wish to calculate.

This connection should support multiple records being connected to the table where 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 in the Builder by clicking "Add Field" from the Fields tab, or right-clicking in the field header in the Records From there, select any of the formula field types (Sum, Average, Min, Max, Count).

Note: If you cannot select these options, the table in question is not connected to many of any other table's records.

Editing a Formula Field

In the Builder, select the table where you want to edit your formula field. To edit, click on the "..." more options icon in the field header, then select "Edit Field 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.

❗️

Removing a field is a significant action that will result in the removal of all its values. 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, as those fields will also be removed.

Building a Formula Field

To build your formula field:

1. First, check for an eligible field

   • Table to use in the 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).

2. Next, add your formula field:

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

3. Add Filters (optional):
   • You can customize your formula fields by adding filters to ensure that only the relevant records are included in the calculations.
   • For example, you can add a formula filter to a sum field that only includes records from the last year.

.

🚧

Notes & Troubleshooting

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.

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 are 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

To optimize performance, formula fields (eg. sums, counts, etc.) that include filters are currently limited to processing 10,000 records. If you see inconsistent results, ensure the filters bring the count of the connected records below 10,000 records, and the formula will correctly calculate against those records.