Help

Finding the Top 5 values from several fields

Topic Labels: Formulas
Solved
Jump to Solution
1256 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Paul_L
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi

I’m creating a tournament ranking system. I have a table for each tournament played and within each table, I list all the players and their individual scores and then their ranking position and ranking points for that tournament.

On a separate “rankings” table, I list all the players and lookup their score, ranking points from each of the tournament table. I can then add the total ranking points across all tournaments and sort them by male/female/adult/senior categories.

However, the end of season rankings after 8 tournaments will be based on a player’s best 5 results/points scores out of the eight events. Therefore, I need to take the 5 largest (not necessarily unique) points from the 8 fields. I’ve looked at various formulas to try and find the answer (similar to LARGE function in Excel), but I’m stumped.

Does anyone know how to do this? Is the answer to structure my tables differently? I feel database design is always my downfall :frowning:

Thanks in advance.

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

I actually think that your base should be structured differently, three tables: [Players], [Tournaments], and [Rankings]. [Rankings] would be a junction table.

On the other hand, I decided to make a video about using an Airtable script for a setup very similar to yours. Note that the script in my video will not work exactly for you because your scores are lookup fields instead of number fields. You probably want a script that calculates the top 5 for all records at once, and not just one at a time. However, I wanted this video to help the code curious and I felt that a simpler situation would better suit that purpose.

See Solution in Thread

4 Replies 4
Noor
6 - Interface Innovator
6 - Interface Innovator

Hi @Paul_L
Airtable don’t have this type of pre-build function
But you can achieve this by scripting
Thanks
Noor

Paul_L
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks @Noor - unfortunately, my scripting ability is very basic (think copy and paste and tinker).

I might have to do this the old fashioned way…Excel. :frowning:

kuovonne
18 - Pluto
18 - Pluto

I actually think that your base should be structured differently, three tables: [Players], [Tournaments], and [Rankings]. [Rankings] would be a junction table.

On the other hand, I decided to make a video about using an Airtable script for a setup very similar to yours. Note that the script in my video will not work exactly for you because your scores are lookup fields instead of number fields. You probably want a script that calculates the top 5 for all records at once, and not just one at a time. However, I wanted this video to help the code curious and I felt that a simpler situation would better suit that purpose.

Paul_L
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks so much for posting the video @kuovonne . It’s really helpful to be able to see how you sort the Top 5 the way that I’ve got the table currently set up - if I can’t do it using the numeric values from lookup fields, maybe I can add some formula fields to just multiply the lookup values by 1 (or there could be a better way I don’t know :man_shrugging: ).

I do have a separate players table, tournaments table and rankings table plus a whole load of other table (Venues, Squad times, Game scores ) - what I haven’t done is link them all properly yet because I’m still not convinced that my database design is optimal, and I’m worried about going too far down a path and having to then unpick everything.