Report output based on sums within groups

[debbie_dunn]

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!

[Justin_Barrett]

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?

[debbie_dunn]

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!!!

[debbie_dunn]

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?