Sports results/ranking manipulation

[Fabian]

I have an Airtable with sports results. Any athlete can submit results for their performances. An athlete can submit results for the same workout.

Workout | Athlete | Result

A | Peter | 13:22
B | Peter | 4:32
A | Mary | 13:12
A | Peter | 11:53

I want to embed rankings for each workout on my website.

BUT…

For each athlete I only want their best result to be included in the rankings.

So the ranking for workout A should look like this:

1. Peter 11:53
2. Mary 13:12

and NOT

1. Peter 11:53
2. Mary 13:12
3. Peter 13:22

Do I need to create a new table for this? How do I only put highscores in that table? How do I get the ranking position 1, 2, 3 etc. into that table…

I am pretty new to Airtable and have no idea what to search for…

[Nick_Dennis]

Hi @Fabian!

If I’m understanding correctly, this can be done with 2 tables, let’s call them ‘Athletes’ and ‘Scores’. You will need to create a Linked field and a Rollup field on your ‘Athletes’ table.

The end result for the Athletes table is something like this:

First you would create a linked field that is a Link to ‘Scores’, and allows for linking to multiple records. It sounds like you have this already.

Then create a Rollup field to display each athlete’s best time. For field, select the one that is linked to Scores, and Time (or whatever this field is called on Scores) as the field you’d like to rollup. Then for the aggregation formula, use: MIN(values)

On the formatting tab of the rollup field, you can select ‘Duration’ as the format, and choose the 2nd option - h:mm:ss for the duration format.

Then you would want to sort your view by ‘Best Time’ or whatever you’ve named this new Field

In the example I also created a ‘Date of Best Time’ field by duplicating the rollup field we just created, but selecting Date (which is created on the Scores table) instead of Time as the field to roll up.

If instead of duration you were counting reps of something (more is better), then you would change the formula in these Rollup fields to MAX(values) and change the formatting from duration to number.

Also note that I didn’t build in the Workout A, Workout B formatting from your table, but instead just used the date of the workout and name of the athlete to differentiate Score records.

Hope that helps! Let me know if you run into problems.

[Fabian]

Thank you, @Nick_Dennis! I definitely learned something here. :grinning_face_with_big_eyes: :thumbs_up:

I am running into a problem here though. On my website I have a page for each workout. And on that page I need a table with the rankings.

So I have 3 dimensions: Workouts, Results, Athletes.

Any hint how to adapt your excellent tutorial above?

[Fabian]

I have an idea. Will post the results of my try here…

[Nick_Dennis]

Aaah that makes sense, but does add a layer of complexity. Let us know if you have any success with your idea.

You’ll definitely need a 3rd table for Workouts. And at least one more linked field on Results linked to Workouts.

My best guess is you will then want several more Rollup fields in Athletes, along the lines of “Workout A - Best Time”, and then you’ll create a View in Athletes for each of those workout. That way you could hide the other workout’s fields, and sort by best time in that specific workout.

This is complex though, and there are other variables. I’d most likely need to see it in action or some screenshots to get more specific with it. Happy to help more if I can though.