Ranking Field Calculation Script

[Flavio_Carlucci]

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!

[Kamille_Parks]

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

Comparing records in different fields in the same table Formulas

This can’t be done with a formula alone. Formulas can only reference data in the record to which that formula is applied. You could do one of several things including: Use an automation with a script (see example from the podcast linked below) [S09-E07] Full Podcast for 10-26-2021 [RAW ORIGINAL VIDEO] - YouTube The improved version of the script discussed in that episode is copied below. Since you seem to want absolute ranking (i.e. no grouping), you can set your let groupPath = [] /* …

[Joey_Brennan]

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.

[Kamille_Parks]

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

[Joey_Brennan]

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!

[James_Burton]

Flavio_Carlucci:

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

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?

[James_Burton]

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?

[egordin]

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

[Patrik_Hellstra]

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

});

}