Skip to main content

Report output based on sums within groups

  • April 7, 2021
  • 3 replies
  • 39 views
D

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!

    3 replies

    Justin_Barrett
    Forum|alt.badge.img+21

    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?

    D
    • debbie_dunn
    • Author
    • New Participant
    • April 13, 2021

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

      D
      • debbie_dunn
      • Author
      • New Participant
      • April 15, 2021

      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?

      Terms & ConditionsAccessibility statement