Skip to main content
Solved

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

  • November 18, 2022
  • 5 replies
  • 182 views
S
Forum|alt.badge.img+3

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!

Best answer by ScottWorld

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.


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.

5 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • November 18, 2022

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

S
S
Forum|alt.badge.img+3
  • Sarah_Krutz
  • Author
  • New Participant
  • November 18, 2022

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


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
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • Answer
  • November 18, 2022

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.


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.

S
Forum|alt.badge.img+3
  • Sarah_Krutz
  • Author
  • New Participant
  • November 18, 2022

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.


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
ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • November 18, 2022

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:

Terms & ConditionsAccessibility statement