Calculate a Percent Completed Column from groups of records in another table

[Sarah_Krutz]

I am new to Airtable and having a hard time finding solutions in other threads, so thank you in advance for any help you can provide!

I have two tables: one is “Courses” and one is “Workshop Planning.” I have an automation that creates a new record in Workshop Planning every time a record is added to Courses. I fill the Workshop Planning records with tasks to be completed for each course. The Workshop Planning table is grouped by Course name in order to keep all relevant tasks together.

What I would like to do is show the percent of tasks completed in the Workshop Planning table, in the Courses table. So each Course record would have a “% Completed” column that would automatically calculate as tasks are marked Complete in the Workshop Planning record.

Is there a way to do this? It would be very helpful for managing our team workflow.

Thanks!

[ScottWorld]

Welcome to the community, @Sarah_Krutz!

The summary bar can show you the % completed above each group:

https://support.airtable.com/docs/the-summary-bar

[Sarah_Krutz]

So can I somehow take that summary for each group and add it to a column in the courses table? This is where I’m stuck.

[ScottWorld · Accepted solution]

Unfortunately, the summary bar can not be used programmatically in any way.

You would need to use some combination of more advanced fields to do that (i.e. linked record fields, formula fields, lookup fields, count fields, and/or rollup fields).

For example: Each course would need to be linked to all of its associated workshop planning records. Then you could create a field of type “count” that adds up all the linked records. Then, you could create another field of type “count” that conditionally counts only completed linked records. Then, you could create a formula that divides those 2 fields by each other, and format that formula to display as a percentage.

[Sarah_Krutz]

This is awesome. I figured there must be some way to manipulate it to display - I just didn’t have the know-how for what Airtable terms to be searching. Thank you so much for your help!!

[ScottWorld]

You’re welcome! Glad I could help!

By the way, since you’re new to Airtable, you may enjoy my free Airtable training course.

Coincidentally, a new version of this course is coming out in April 2023, which actually covers many of the topics you mentioned in your post! :slightly_smiling_face:

LinkedIn

Learning Airtable Online Class | LinkedIn Learning, formerly Lynda.com

Learn the basics of Airtable, a spreadsheet-database platform, including how to create tables, use different types of fields, leverage views, work with records, link tables, and more.