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.