Creating a Scoring System

I am trying to create a scoring system using Airtable where judges can score a team (with the different metrics in columns).

What I have done:

  • Created 3 different tables for each judge
  • Each judge’s table has a list of all the teams (teams are populated as new records across all judges’ tables automatically using external software)
  • Judges can manually key in the score for each metric and an overall score is automatically calculated in the respective record

What I need help with:
However, I want to create a new table which gives a summary of the overall score for each team for each judge (i.e. looking across a particular record, I will be able to see the overall score for judges A, B and C). And I want this table to be updated AUTOMATICALLY whenever each judge finish scoring the team.

What I have achieved thus far is using linked tables and lookup function. However, this is very inefficient as I have to manually choose which the team (or record) to be linked manually before I can lookup the overall score. Moreover, I have to do this N times for N number of judges (since each judge has his/her own table).

Is there a way to automatically update the overall score column for the respective judge each time they complete scoring a particular team? Cheers!

Hi @Jason_Tay - I would recommend a different base structure to the one you have as the problem with it is that you need to explicitly make links (manually) to get another table to sum or group records.

I recommend something more like this:

A teams table:

A judges table:

And a scores table which links teams and judges:

You can see the total for each team/judge and with a grouped view you can see the score for each team for all judges:

Appreciate you’ve already integrated your external software into a specific base config, but this set-up gives you the updated totals without having to make any manual links, so might be worth looking at. Generally speaking, when I’ve got multiple “things” of the same type (judges in this case), I would tend to put them in the same table if I can.

Hope this helps!

JB

1 Like

Appreciate this alot! Definitely helps but I have 2 questions when using this structure:

  1. I guess I have to manually name the teams with a suffix -Judge X? (Or any recommended efficient way of doing this will help!)

  2. is the “Team” and “Judge” column populated automatically or has to be entered manually? This is not a very big issue but just checking.

Thanks again

Hi Jason

  1. No - on the scores table the team-judge is a formula field, so autopopulates based on the team and judge selection:

Team & '-' & Judge

  1. Yes, when a judge wants to enter a score they choose the team and the judge, i.e. themself, and then enter the scores. You could get them to do this using an AT form if you don’t want them to see or have access to the underlying base.

Here’s a link to my mockup:

Copy it into your workspace to see the underlying field settings.

JB

2 Likes