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.