How to figure out average percentages from one table into a single row in another table

1

I seem to be an absolute idiot when it comes to column Relations and using formulas in other tables.

I am working on a homeschool easy grading system for my daughters, and I have a table called “Grades” that has the following columns

Date | Course | Lesson | Percent

Date: Date data type
Course: Select List data type
Lesson: Text data type
Percentage: Percent data type

I have another table called “Report Card” and this is supposed to calculate the average percentage from the “Grades” table based on the Course from the “Grades” table. This has the following columns:

Course Name | Percentage | Grade

Course Name: Relation data type (set to the Course column from the Grades table)
Percentage: Percent data type
Grade: Text data type

An example of the data stored is below

05/29/2025 | Math 4 | Lesson 4 | 100%
05/29/2025 | Language Arts 4 | 401 - Lesson 4 | 100%
05/30/2025 | Language Arts 4 | 401 - Quiz 1 | 94%
05/30/2025 | Math 4 | Lesson 5 | 100%

And so and and so forth as it has several rows of data.

The idea is that the Report Card table would take the average of all the percentages from a specific course, calculated from the Grades table, and this would be the “Percentage” column in the Report Card table.

Example:

Language Arts 4 | 99.85% | A
Math | 99.2% | A

The problem is, no matter how many times I have tried to write the formula by filtering the Grades table and averaging the Percent column based on the Course from the Grades table, it either comes out blank or at 100%. Problem is, the grades are supposed to be from the Example above, so I’m obviously not putting the formula in correctly.

Here are formulas I have tried:

Grades.filter(thisRow.Course Name = Course).Percent.Average()

This comes back blank.

Average([Grades].Filter(Course = thisRow.[Course Name]).Percent)

This also comes back blank.

Average(thisRow.[Course Name].FormulaMap(CurrentValue.[Percent]))

This comes back with 100% for both columns.

I am just at a loss because I feel like this should be SO much easier than it is to get the information I need to work the way I want it to. I mean, I can just manually enter in the data by filtering the “Grades” table and manually entering the averages I get with the filters, but the whole point is I want this to work in a different way, and this use case has so many applications, especially at my place of work (which we use Coda for).

Any help would be greatly appreciated. I really think I just suck at understanding Relations and how they work.

2

Hi Jessie,

It would be easier to tell you what’s up if you shared your doc, but I suspect the problem is that the ‘Course’ column from ‘Grades’ should be a relation column pointing to the ‘Report card’ table (the name is a bit confusing, as it seems to contain courses).

Then the formula would be something like Grades.filter(Course Name = thisRow).Percent.Average()

Let me know if this helps!
Pablo

2 Likes
3

Thank you so much! I just switched the Relations around so I had it pointing to “Report Card” rather than “Grades” and that worked perfectly. Now, is there an article I can read about why this has to be set up this way so I can understand it more? I didn’t even think about this as a solution because I didn’t realize it had to be a specific way. Again, the Relations thing is such a mystery to me and I feel like I just need a crash course on this.

4

Maybe this article can help:

When you use relations, you’re working with the row reference, so you can access all of its data just by using the dot operator. This makes things much easier to manage. Plus, with a relation, you don’t have to worry about updates, like fixing a course name if there was a misspelling, since it’s just a reference and can be updated in one place.

Of course, you can choose not to use relations, but doing so would make the workflow, formulas, and filtering much more complex and probably not as efficient.

I was actually building a demo doc just before Pablo answered. Check it out here!

Cheers
Arnhold

1 Like
5

Thank you so much for taking the time to reply! I will definitely check out the article as well as your demo doc!

I see you have done the Doctorate program, and I am really hoping to be able to do that. I’ve tried applying a few times, but never really got a response. I just submitted again, though, so I’m hoping to be able to get the one in Q3.