Hi Hugo,
Ah, sorry about that. I had a check in place that would only allow the script to run if the selected field to rank by was a number, percent, or currency field. I’ve now added formula fields to that check, so it should run fine for you.
The original base has been updated, and the changes and new code can be found below.
===
Changes
- “Formula” fields can now also be used for ranking
- Updates to the firstFieldToRank.type and secondFieldToRank.type if checks and the associated error text
===
New Code
//This script will grab the rows from the chosen table, rank them, and then update the chosen column
output.text("This code will let you pick a field you want your rows ranked by, pick another field to rank by after that, and then rank them in descending order.");
//Allow the user to choose the table they want to manipulate
let table = await input.tableAsync('Select the table');
//You can replace the above line with the following if you know what table you're going to be using
//let table = base.getTable("[TABLE NAME]")
//For the purposes of this example base, it would be:
//let table = base.getTable("Sales");
//Allow the user to choose the field they want to update with the rank number
let fieldToUpdate = await input.fieldAsync("Select the field you want updated with the rank number. (Pick 'Rank Number')", table);
//You can replace the above line with the following if you know which field you're going to be updating with the rank number
//let fieldToUpdate = table.getField("[FIELD NAME]")
//For the purposes of this example base, it would be:
//let fieldToUpdate = table.getField("Rank Number")
//Allow the user to choose the first field they want to rank
let firstFieldToRank = await input.fieldAsync("Select the first field you want to rank by. Please pick a number, percent, currency or formula field. (Pick 'Amount')", table);
//You can replace the above line with the following if you know which field you're going to be ranking by
//let firstFieldToRank = table.getField("[FIELD NAME]")
//For the purposes of this example base, it would be:
//let firstFieldToRank = table.getField("Amount")
//Check whether the firstFieldToRank is a number, percent, currency, or formula field to ensure it works as intended. If it's not, stop the code and inform the user why
if (firstFieldToRank.type === "number" || firstFieldToRank.type === "percent" || firstFieldToRank.type === "currency" || firstFieldToRank.type === "formula"){
//Allow the user to choose the second field they want to rank
let secondFieldToRank = await input.fieldAsync("Select the second field you want to rank by. Please pick a number, percent, currency or formula field. Pick ('Customers')", table);
//You can replace the above line with the following if you know which field you're going to be ranking by
//let secondFieldToRank = table.getField("[FIELD NAME]")
//For the purposes of this example base, it would be:
//let secondFieldToRank = table.getField("Customers")
//Check whether the secondFieldToRank is a number, percent, currency, or formula field to ensure it works as intended. If it's not, stop the code and inform the user why
if (secondFieldToRank.type === "number" || secondFieldToRank.type === "percent" || secondFieldToRank.type === "currency" || secondFieldToRank.type === "formula"){
//Shows the user some text so they know something's happening
output.text(`Running...`);
//Create an empty array. We'll be using this to update records in batches of 50.
let updates = [];
//Selects the records from the table and then sorts it by descending
let query = await table.selectRecordsAsync({
sorts: [
{field: firstFieldToRank, direction: "desc"},
{field: secondFieldToRank, direction: "desc"},
]
});
//Create a variable that we will update to get the rank number
let rankNumber = 0;
//Since it has already been sorted, all we need to do is create the rank number as we loop through the result
for (let record of query.records) {
//Add 1 to the rank number
rankNumber++
//Add the ID of the row, the name of the column to update, and the value of the row's cell to the "update" array
updates.push({
id: record.id,
fields: {
[fieldToUpdate.id]: rankNumber
}
});
//Whenever the loop runs three times, show a "Still running" message to the user so that they know something is still happening
if (rankNumber % 3 === 0 ){
output.text(`Still running...`);
}
}
// Update records in batches of 50.
while (updates.length > 0) {
await table.updateRecordsAsync(updates.slice(0, 50));
updates = updates.slice(50);
}
//Show a "Done" message to the user once the loop is complete
output.text(`Done!`);
}
else{
output.text(`Sorry, the field you selected (${secondFieldToRank.name}) is not a number, percent, currency or formula field. Please start over.`);
}
}
else{
output.text(`Sorry, the field you selected (${firstFieldToRank.name}) is not a number, percent, currency or forumla field. Please start over.`);
}
//Feedback form in case you want something fixed: https://airtable.com/shrXdwsEXSsd02oMI
