Formula newbie, find highest 5 numbers in a rollup array and add together

[Darren_Taylor]

Hi, I’m a bit new to this and not sure if it’s even possible.
I have a table of ‘scores’ with multiple ‘players’. Each player has a score, up to 30 scores each.
In another table I have a rollup which displays all the players scores.
What I’m trying to do is find the 5 best scores for each player, add as a total, then divide by 5 to give an average score for each player. This should then give a leaderboard of the best players based on their 5 best scores out of X scores.
I’ve managed to do this manually in Notion by adding a checkbox formula which when I enter the player score, I look at their top scores and check/uncheck as required. This is fine and works but I’m now looking to make it more automated with Airtable.
Is this dooable as a formula or better as a script on a front end with js?
So with airtable it works like this
‘Overall Score’ = All Scores + Checkbox = Total Score
Average Score = Total Score / 5
Thanks in advance.

[Darren_Taylor]

Really appreciate the help here, but I must have something set up incorrectly.
My aim is

1. User enters details in a form
2. Form populates the ‘Game Two’ table
   < script runs to check previous scores and apply checkbox for top 5 >
3. Player table is updated with latest scores in a scoreboard format.
   I’ve attached the tables as images if that helps. Image 1 is the table populated by the form, image 2 is the scoreboard.
   Thanks again for the help, it may be best if I do some more reading as I don’t really understand enough yet to debug for myself.

[Kamille_Parks]

• Are you still getting an error, or is the script not working as expected?
• Why is {Players} a separate field from {Player}, and why are you using {Players} in the script if {Player} is the one that links to the other table?

Can you share your base so I can see what’s going on? My script works fine in my demo base.

[Kamille_Parks · Accepted solution]

I just duplicated your base, used the same script in an automation, and everything worked. One more time, here is the setup you’ll need:

• An Automation being triggered when the {Points} field is updated.
• A Script Action with an input of playerName set to pull the List of 'name' from the {Player} field, which links to your table of [Player].
• A Checkbox field named {Check}
• A Number field name {Points}. Your {Score} field is redundant. It has no affect on this process but I would recommend making the primary field a unique identifier (i.e. converting it to an Autonumber field, a Formula field showing the RECORD_ID(), etc.)
• The following script:

const table = base.getTable("Game Two")
const query = await table.selectRecordsAsync()
const records = query.records
const inputConfig = input.config()
const playerName = inputConfig.playerName
const sorted = records.filter(x => playerName.includes(x.getCellValueAsString("Player"))).map(x => {
    return ({
        id: x.id,
        score: x.getCellValue("Points")
    })
}).sort((a,b) => {return b.score - a.score})
const top5Scores = sorted.slice(0, 5)
const otherScores = sorted.slice(5)
await table.updateRecordsAsync(top5Scores.map(x => {
    return ({
        id: x.id,
        fields: {
            "Check": true
        }
    })
}))
let updates = otherScores.map(x => {
        return ({
            id: x.id,
            fields: {
                "Check": false
            }
        })
    })
while (updates.length > 0) {
    await table.updateRecordsAsync(updates.slice(0, 50))
    updates = updates.slice(50);
}

[kuovonne]

It looks like you are going in a different direction, but your post inspired me to create this which is created using formulas and rollups (no scripting):

The base design is different from yours, it only goes up to the top 3 scores, although the process could be extended to 5. This method also only works when all of a person’s scores are unique, so it won’t work for your particular use case.

[Darren_Taylor]

Thank you, thank you, thank you Kamille! You are a genius!

I had to ‘watch all fields’ in my automation for it to fire but that is BRILLIANT. Thank you so much for the help. I’ve already learnt an awful lot about Airtable scripting I didn’t know 2 days ago. I was about to give up this morning but thank you again for persevering. Looking forward to building some cool things now.
Stay safe :clap: :raised_hands: