Skip to main content

Roll up totals to parent records

Keep a project's total hours up to date. When a task changes, Tape adds up the hours of all tasks of the project and writes the sum to the project record.

Written by Leo

A rollup adds up (or counts, averages, or finds the smallest or largest of) a number from a list of records. This recipe gets the project of a changed task, collects all tasks of that project, rolls up their hours, and saves the sum on the project. It's meant to keep totals correct without a manual count.

Scrolling through the automation Roll up task hours to the project: When a record is updated and Hours has changed in Tasks, Get related records from Projects outgoing via Project and follow to Tasks incoming via Project, Rollup collected values from Hours calculate Sum and save in Total hours, and Update all related records in Projects with Total hours set to @Total hours

What you'll need

  • A Projects database app with a Total hours number field.

  • A Tasks database app with a Hours number field and a relation field to Projects.

Build the automation

  1. Open the Tasks app and click the automations icon at the top right (Create and view automations).

  2. Select New automation, then select When a record is updated.

  3. In the trigger, select the field name. Tape preselects one of the app's fields. Select Hours under FIELDS, then select the preselected field again to remove it.

  4. Select Add action → Get related. The action reads Get related records for the current record.

  5. Next to From app, choose Projects, then Outgoing via and the relation field to Projects. Keep All records. This follows the task to its project.

  6. Select Add follow, choose Tasks next to Follow to app, then Incoming via and the same relation field. This follows the project back to all of its tasks.

  7. Select Add action → Rollup collected. In the sentence Rollup collected values from {field} calculate {type} and save in Rollup, select Hours as the field and Sum as the calculation. Click Rollup and rename the variable to Total hours.

  8. Select Add action → Update related. It reads Update all related records. Choose the Projects app.

  9. Select Add field and choose Total hours. Select Set value and insert the Total hours variable from the rollup.

  10. Open the ••• menu of Update related and check that Trigger automations is off (the default), so the update can't start other automations on the project.

  11. Select Create automation.

In Get related records for the current record with From app Projects, choosing Project next to Outgoing via, selecting Add follow, then choosing Project next to Incoming via in the new row Follow to app Tasks

Note:

  • Mind the size. A collection holds at most 1,000 records. A project with more tasks gets a total that's too low, without a warning.

  • Avoid loops. Don't start an automation on Projects that changes tasks, or the two can call each other.

The result

When someone changes the Hours of a task, the Total hours of its project is recalculated from all tasks, for example 42.5.

Variations

  • Choose Count all instead of the sum to count the tasks of a project.

  • Choose Average instead of Sum to show the average time per task. Other calculations include Median, Min, and Max.

  • Skip the rollup. In Update record or Create record, a number field can use Lookup instead of Set value: pick a number field of a related database app and choose Sum.

  • Run the same steps on When a record is created to include new tasks.

Learn more

FAQs

Can I use a calculation field instead?

Often, yes. Add a calculation field to Projects. In the calculation editor, type @, find the related database, for example ↙ Tasks via Project if the relation field is called Project, and select Sum of Hours. The total updates on its own. Use the automation instead if only some tasks should count, or if you need the total in a regular number field.

Why is the total wrong after I delete a task?

The automation runs when a task changes, not when one is deleted. To keep the total correct after a delete, recalculate it from the project, or use a calculation field.

Are you a developer using the Tape API for automations?

The automation API has its own documentation for developers. Full reference: rollup_iterable_values in the developer docs →

Still have questions? Find answers and get help from community experts in the Tape community →

Did this answer your question?