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

Topic Labels: Formulas
Solved
3929 14
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions
16 - Uranus

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
}
})
})
}
``````
14 Replies 14
16 - Uranus

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.

5 - Automation Enthusiast

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

16 - Uranus

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

5 - Automation Enthusiast

Thank you. I’ll read up on that.

16 - Uranus

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.

5 - Automation Enthusiast

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.

16 - Uranus

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

}
``````
5 - Automation Enthusiast

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

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)`
`filter(x => playerName.includes(x.getCellValueAsString("Players")))`