Help

Re: Ranking Field Calculation Script

2664 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Flavio_Carlucci
4 - Data Explorer
4 - Data Explorer

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!

7 Replies 7

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

Joey_Brennan
4 - Data Explorer
4 - Data Explorer

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

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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:

Screen Shot 2022-10-28 at 8.45.28 PM

Help?

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

Screen Shot 2022-10-28 at 8.56.50 PM

Does this restrict the field type I can use?

egordin
7 - App Architect
7 - App Architect

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