Hi @Adam_TheTimeSavingCo,
Thank you very much for the script! I’m trying to adapt it to my needs but I need your help.
I would like to apply the ranking only to filtered records.
The filtered records come from a linked table. I have modified your script in this way. I get the right selection of records but I can’t apply the ranking calculation only to this one.
Here is my script.
//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.");
// Change this name to use a different table
let table = base.getTable("Etapes de concours");
// Prompt the user to pick a record
// If this script is run from a button field, this will use the button's record instead.
let catAclasser = await input.recordAsync('Select a record to use', table);
if (catAclasser) {
// Customize this section to handle the selected record
// You can use record.getCellValue("Field name") to access
// cell values from the record
output.text(`You selected this record: ${catAclasser.name} (${catAclasser.getCellValue('record id')})`);
//enregistrement du nom de la catégorie qu'il faudra utiliser comme filtre dans la table prestations (Etape de concours) : catAclasser.name
//This script will grab the rows from the chosen table, rank them, and then update the chosen column
output.text(`Ce script attribue les classements à l\'étape : ${catAclasser.name}`);
//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("Prestations");
//For the purposes of this example base, it would be:
//let table = base.getTable("Sales");
//////////////// //////////////// ////////////////
// FILTRER LES PRESTATIONS POUR GARDER QUE Celles de la catégorie sélectionnée
let query = await table.selectRecordsAsync();
let filteredRecords = query.records.filter(presta => {
return presta.getCellValue('record id etape').includes(catAclasser.getCellValue('record id'))
})
console.log(filteredRecords);
//////////////// //////////////// ////////////////
//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("Classement");
//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("Note");
//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(`En cours...`);
//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(`Toujours en cours...`);
}
}
// 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(`C'est fait!`);
}
//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
} else {
output.text('No record was selected');
}
Thank you very much for your help.
