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

Rollup fields can be set to only include records when conditions are met. You can set it ti where the checkbox field is not empty, and sum values from the Score field.

This works great by the way, thanks. Would be even better if I could automate the checkbox.

You could add an Automation Script Action to calculate and set the checkboxes for you.

Thank you. I’ll read up on that.
Thanks again for your help.

Here’s a script which should be of use. It assumes you have a script input called playerName which reports the Automation’s triggering record’s value of the Player name:

const table = base.getTable("Name of table where scores are recorded")
const query = await table.selectRecordsAsync()
const records = query.records

const inputConfig = input.config()
const playerName = inputConfig.playerName

const sorted = records.filter(x => x.getCellValueAsString("Name of field linking to Player") == playerName).map(x => {
    return ({
        id: x.id,
        score: x.getCellValue("Name of score field")
    })
}).sort((a,b) => {return b.score - a.score})

const top5Scores = sorted.slice(0, 5)
const otherScores = sorted.slice(5)

table.updateRecordsAsync(top5Scores.map(x => {
    return ({
        id: x.id,
        fields: {
            "Name of checkbox field": true
        }
    })
}))

table.updateRecordsAsync(otherScores.map(x => {
    return ({
        id: x.id,
        fields: {
            "Name of checkbox field": false
        }
    })
}))

This should reassign the checkboxes whenever the Automation runs.

const table = base.getTable("Name of table where scores are recorded")
const query = await table.selectRecordsAsync()
const records = query.records

const inputConfig = input.config()
const playerName = inputConfig.playerName

const sorted = records.filter(x => x.getCellValueAsString("Name of field linking to Player") == playerName).map(x => {
    return ({
        id: x.id,
        score: x.getCellValue("Name of score field")
    })
}).sort((a,b) => {return b.score - a.score})

const top5Scores = sorted.slice(0, 5)
const otherScores = sorted.slice(5)

table.updateRecordsAsync(top5Scores.map(x => {
    return ({
        id: x.id,
        fields: {
            "Name of checkbox field": true
        }
    })
}))

table.updateRecordsAsync(otherScores.map(x => {
    return ({
        id: x.id,
        fields: {
            "Name of checkbox field": false
        }
    })
}))

Thanks again for this. I’ve tried every combination, and even started again with a new table but, I get the same error.
Error: Request parameters failed validation.
at main on line 27
Here’s my amended 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 => x.getCellValueAsString(“Player Name”) == playerName).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)

table.updateRecordsAsync(top5Scores.map(x => {
return ({
id: x.id,
fields: {
“Check”: true
}
})
}))

table.updateRecordsAsync(otherScores.map(x => {
return ({
id: x.id,
fields: {
“Check”: false
}
})
}))

Like I said, I’m pretty new to this so appreciate the help.
thanks.

Replace the two table.updateRecordsAsync() functions with this:

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);
}

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);
}

Sorry, still erroring on 18. Same error.

Error: Request parameters failed validation.
    at main on line 18

Thanks for the help again.

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 => x.getCellValueAsString("Players") == playerName).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);
}

How did you set up the input variable? It should be pulling from the {Players} field, the same field being used in the .filter(). Assuming {Players} is a Record Link field, and your input variable is set to Field values | Players | List of 'name', try replacing the filter:
filter(x => x.getCellValueAsString("Players") == playerName)
with:
filter(x => playerName.includes(x.getCellValueAsString("Players")))

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.

• 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.

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.

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);
}

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: