Ranking Field Calculation Script

Hey guys, how are you?

Since I was looking for this and couldn’t find any solution to it… I decide to create a custom script that get the user score of a field and calculates the ranking position based on this score.

I´ll try to explain how it works in detail below!

  1. You gotta have a field in your table that has the user score.
  2. Create an automation to run a script everytime this field gets update.
  3. On the script code, you should do as follows:
// Get your Table
let table = base.getTable("table_name");
// Get the score field of your table
let queryScore = await table.selectRecordsAsync({"score_field"]});
// Save these records in a variable
let records = queryScore.records;
//Define an array of Records
let rankItems = [];
// Define the score variable
let score
//Set score variable as a number
score = Number(score)
//Set the initial position to 0 since its the first position of the array
let position = 0

//Create a function that gets the score value for each record and push into your array of records 
records.forEach((record) => {
// Get each record score value
  score = record.getCellValue('score');
//Get each record id
  let recId = record.id
// Save this record into your array
  rankItems.push({recId, score});
});

// Sort records in the array by its score value
  rankItems.sort(function(a, b){
    return b.score - a.score;
  });

//Create a function that  update each record
rankItems.forEach((rankItem) => {
//set each record id into a variable
  let recordId = rankItem.recId
//Update record position on your table
  table.updateRecordAsync(recordId, {
    "position" : position
})
//Add 1 to position so it goes to the next record of the array
  position++
});

I guess this is it. Pretty simple and straightforward! Just folow instructions on the code comments!

An alternate approach is linked below; it effectively lets you use sorted views instead so one could compare records against more than one field.

Curious if you’ve had any issues with a runtime error?
My table has over 200 entries and has begun throwing an error every run.

The script in this thread updates records one at a time, which is not best practice. The other thread I linked to in my first reply shows a simpler solution less likely to run into runtime errors (it makes record updates in batches).

1 Like

Amazing… I missed your note related to the group path and was struggling to set this up for absolute ranking. Thanks for pushing me to look back at your original response and try again, it worked beautifully!

This looks awesome but I can’t get it to work. Should this basically be a cut and paste job while plugging in “table_name” and “score_field”? Or is there some other customization that needs to happen?

This is the error I’m getting:

Help?

Hi there… I’ve been trying to get this scipt to work but I am getting the following error:

Does this restrict the field type I can use?