Skip to main content

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 = m];


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

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

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


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?

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






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?

For those that still need a solution to this, the following script (written by ChatGPT!) worked great. Background:

I needed a script that would run on a scheduled automation, that would:

  1. Clear the previous rankings
  2. Ignore fields that meet a criteria (in this case, it should only rank those records where the field "# Referrals" is greater than zero. It can skip the rest of the records)
  3. Write ranking to a specific field (Rank, in this case) based on descending amounts in another cell (Total Approved, in this case)

 

let table = base.getTable('Your TableName'); // Replace 'Your TableName' with the name of your table

let query = await table.selectRecordsAsync();

let records = query.records;



// Clear 'Rank' field for all records initially

let clearUpdates = records.map(record => ({

id: record.id,

fields: {

'Rank': null // Clear the 'Rank' field

}

}));



// Batch update to clear 'Rank' due to Airtable's batch update limit

while (clearUpdates.length > 0) {

await table.updateRecordsAsync(clearUpdates.slice(0, 50));

clearUpdates = clearUpdates.slice(50);

}



// Filter records where "# Referrals" is greater than 0

let filteredRecords = records.filter(record => {

let referrals = record.getCellValue('# Referrals'); // Replace '# Referrals' if your field name is different

return referrals > 0;

});



// Proceed with filtered records to calculate 'Total Approved' and rank

let recordsWithValues = filteredRecords.map(record => ({

id: record.id,

totalApproved: record.getCellValue('total approved') || 0, // Ensure a default value

}));



// Sort by 'total approved' in descending order

recordsWithValues.sort((a, b) => b.totalApproved - a.totalApproved);



// Prepare updates for ranked records

let rankUpdates = recordsWithValues.map((record, index) => ({

id: record.id,

fields: {

'Rank': index + 1

}

}));



// Update records with new ranks in batches

while (rankUpdates.length > 0) {

await table.updateRecordsAsync(rankUpdates.slice(0, 50));

rankUpdates = rankUpdates.slice(50);

}

 

Here's an elegant solution to ranking. It assumes that you have a Table, e.g., "Users," with a field with some value, e.g., "CLV," and an empty numbers field, e.g., "Rank."

1) Create an automation trigger to run the script, e.g., When a record is created.
2) Run a script (paste below):

 

let table = base.getTable("Users");
let query = await table.selectRecordsAsync();
let records = query.records;

// Create a copy of the records array to sort
let sortedRecords = [...records]; // Spread syntax to create a shallow copy

// Sort the copied array by the "CLV" field in descending order
sortedRecords.sort((a, b) => b.getCellValue("CLV") - a.getCellValue("CLV"));

// Iterate over the sorted records and update each record with its rank
for (let i = 0; i < sortedRecords.length; i++) {
await table.updateRecordAsync(sortedRecords[i].id, {
"Rank": i + 1
});
}

Reply


Terms & Conditions