Finding the Top 5 values from several fields

[Paul_L]

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.

[Noor]

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

[Paul_L]

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 · Accepted solution]

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]

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.