Report output based on sums within groups

Hi, I’m not new to Airtable but I’m still very novice. I’m sure there is a way to do this but I can’t figure it out.

I run a horse show association where our members earn points. They can earn awards in 3 different areas, all are captured within our Airtable bases. I figured out groups and then adding the sum to the summary row. But how do I get an output for this, into a report or html I can insert into our website? Here is an example of what I’m trying to do:

Name - Class - Division - Points
John - Apples - Fruit - 5
John - Oranges - Fruit - 4
John - Green Beans - Vegetables - 3
John - Asparagus - Vegetables - 2

So I need to be about to output 3 things - total points (all points added together) so for John this would be 14 points. Then I need John’s points by Division - so for John this would be Fruit - 9 and Vegetables 5. Then I need it for each Class - so Apples - 5; Oranges - 4; etc.

I can get my bases sorted/grouped to give me the sums correctly, but then I don’t know what the next step is to produce a report that is usable and postable for our website.

Thanks in advance for all help!

One thing that you didn’t indicate is how you’ve designed your base in terms of choosing classes and divisions, and also storing member data. My recommendation: those three things should be tables. There should be a [Members] table for storing member info, a [Classes] table for specifying what classes are available, and a [Divisions] table for specifying available divisions. In the [Classes] table, each class would link to its appropriate division (more on how to use this later).

Then you’ll have an [Awards] table where you record the awards. Each award record would link to a member and a class, a lookup field can pull in the division for the linked class, and then you’d enter the point value for the award.

Back on the [Members] table, you can use a collection of rollup fields to collect the totals: one that rolls up all totals, and then others that use conditions to limit the rollup to certain classes/divisions.

Assuming that you don’t have a massive number of classes/divisions, this should be relatively easy to set up. You can then build a view in the [Members] table that you can embed on a website.

Will that work?

Thank you Justin, this is something I can try absolutely. Here is how we’re set up currently.

Member table (with all the member info - member ID, name, address, etc
Horse table (with all of the horse info - horse ID, name, type, etc - with a linked field to the member
Then the Points Table which is where I’ve been adding in all of the points. The points table pulls in the member info I need (name, city, etc) from the Member table with linked fields, same with horse info - it pulls in the horse info using linked fields. The divisions and classes are in that points table as single select items.

If I read what you’re saying, it would be best (easiest/most effective) if I create a table for divisions, and then a table for classes. Then they would be linked fields in my points or awards table. I’ll give that a try and see how far I can get before I stumble again. LOL

Thank you for the info!!!

1 Like

Another question now. I have the sums working for the totals. When I try to add a formula for summing points for a certain class or discipline, I can do that also, but need a new rollup column for each division and then one for each class. With approx 100 classes/divisions, is this a problem with airtable? Will it support that many columns? Is there a way to do it without having so many columns?