How to create and manage rollup fields
Rollup fields are a powerful feature used to perform calculations or aggregations on related records. They can be used in Pitchly to summarize data from records within a table or linked records from another table in a meaningful way. Rollup fields can perform various types of calculations, such as summing values, counting records, finding the minimum or maximum value, or calculating averages. They are useful for highlighting trends and gaining insights into aggregated data without manually performing calculations for each individual record.
Admin-level account permissions are required to add and edit fields in tables. This includes rollup fields. Once the rollup is created, it remains dynamically updated, re-calculating results if values change for each row in the primary source table.
Prior to creating a rollup field, it's important to consider the following:
Fields that cannot be used as source fields include: Rollup, Lookup, Reference, Reference Multiple, Updated At, Created At.
A rollup can be included in a formula field and a formula field can be used in a rollup field, additionally, a formula can also be used to join on in a rollup.
A rollup Filter has two primary components: Match records, which serves as a normal filter on the source table, and join records, which is a dynamic filter used for matching corresponding values.
At most, one join can be added to a rollup, but any number of filter conditions are allowed. A join cannot be performed on a rollup field type.
Prior to creating a rollup, add fields to both tables, along with some content stored in records.
Avoid naming fields the same inside a rollup.
Matching by a reference field uses the record IDs, therefore, we recommend clients first add the formula field RECORD_ID() to each table. This will enable you to easily use rollup fields in conjunction with reference fields.
Create a rollup field:
For this example, we have a Companies table and Matters table. We will be creating a rollup field in the Companies table that aggregates the Deal Value field from the Matters table for all deal Record IDs that match on a company's name. This will give us the total revenue for each company. This matching is referred to as a join.
1. | Before you begin, make sure the RECORD_ID() formulas have been added to the tables. |
2. | Starting in the main table (Companies table for this example), navigate to the edit table window. |
3. | Add a new field and select rollup as the type. |
4. | Add a name and description (optional). |
5. | For the Source Table, select the foreign table to reference. |
6. | For the Source Table Field, pick the field to aggregate from inside the foreign table. |
7. | For the Aggregate Formula, add syntax from one of the options under built-in aggregation functions (included below).  |
8. | To set up the join, click Filter and then click Add Join. |
9. | The left field is the source field to join on, while the right field is the main primary table field.
Please note: Joining is based on an exact match only, so x IS y. Not, X contains Y, or X starts with Y, etc. When using a record ID, fields are exact matches.
 |
10. | Click update. Rollup results displayed below are the deal value sums for each company in the matters (foreign) table matching the record ID in the comapnies (main) table. 
To add a normal filter condition to the rollup, go back into Filter and click Add Condition. This filter acts on the source table. For this example, a filter on Practice Area by “Corporate Finance” is added to further filter joined data by practice.
With this new filter, data for every company is further narrowed.
 |
Roll up reference fields:
Specify which records to use in the source table by rolling up references in the main table. Click the toggle, Roll Up References, and choose the reference fields to roll up. Only those references will be used in aggregating data and will be aggregated in order (if multi-reference). References cannot be rolled up if a join has been specified. The rolling up of references is considered equivalent to a join.
Built-in aggregation functions:
Syntax: SUM(values)
Description: Sum records in source table.
Source Field Types: Number, Currency, Formula
Syntax: AVERAGE(values)
Description: Find average of records in source table.
Source Field Types: Number, Currency, Formula
Syntax: MAX(values)
Description: Find max of records in source table.
Source Field Types: Number, Currency, Formula, Date
Syntax: MIN(values)
Description: Find min of records in source table.
Source Field Types: Number, Currency, Formula, Date
Syntax: AND(values)
Description: And records in source table.
Source Field Types: Number, Currency, Formula, String, TextBlock, Dropdown, Date, Boolean
Syntax: OR(values)
Description: Or records in source table.
Source Field Types: Number, Currency, Formula, String, TextBlock, Dropdown, Date, Boolean
Syntax: COUNT(values)
Description: Count records in source table.
Source Field Types: Number, Currency, Formula, String, TextBlock, Dropdown, Date, Boolean
Syntax: LIST(values)
Description: List all records in source table for any matching filter/join.
Source Field Types: Number, Currency, Formula, String, TextBlock, Dropdown, Date, Boolean
Rollup bullet points in Elements:
Using a source field that is multi-line or single-line text, select LIST(values) as the aggregation function. For the delimiter input “\n” (without quotes). This indicates that the list will be joined as separate lines. In Elements, insert this field and click bullets in the format pane. This allows for generating bullets from any source table subfield. To control the order, roll up a multi reference in this way rather than using a join. Joined records will enter the field in chronological order of creation.
Filter/sort by rollups:
To filter a rollup field, click on the Filter button and choose the rollup field from the dropdown. The records can be filtered the same as strings, but with the ability to filter with “is more than…” or “is less than…” as well.
If the result of the rollup was a currency, the type of currency is ignored when filtering by this number. Follow a similar process to sort by rollup fields.
Rollup fields in Elements and other apps:
Rollups fields can be manipulated almost as if they were the field type of the result, although the app won’t know the result ahead of time until the calculation is run. A number/currency format can be set so that if the result happened to be a number or currency, that format would be shown.
Please note: An exception to using rollup fields in other apps is Pitchly Forms. Forms do NOT utilize any rollups directly.
We’d love to hear from you. If you have any feedback on product enhancements or additional questions, email the team at PitchlySupport@pitchly.com.