Skip to main content
Solved

Finding the Top 5 values from several fields

P
Forum|alt.badge.img+2
  • Paul_L
  • New Participant
  • 3 replies

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.

Best answer by kuovonne

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.

View original
Did this topic help you find an answer to your question?

4 replies

N
Forum|alt.badge.img+4
  • Noor
  • Participating Frequently
  • 11 replies
  • April 18, 2022

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

P
Forum|alt.badge.img+2
  • Paul_L
  • Author
  • New Participant
  • 3 replies
  • April 19, 2022
Noor wrote:

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


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
Forum|alt.badge.img+27
  • kuovonne
  • Brainy
  • 6002 replies
  • Answer
  • April 20, 2022

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.

Hannah_Wiginton
1 person likes this
  • Hannah_Wiginton
    Hannah_Wiginton
P
Forum|alt.badge.img+2
  • Paul_L
  • Author
  • New Participant
  • 3 replies
  • May 3, 2022
kuovonne wrote:

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.


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.

kuovonne
1 person likes this
  • kuovonne
    kuovonne

Reply


Most helpful members this week

Terms & Conditions