Help

Re: Script Block: Rank Descending and Update Field

7440 0
cancel
Showing results for 
Search instead for 
Did you mean: 

Ranks your data in descending order based on a field you select, and updates a field you select with the ranking number.

Here’s a link to an example base which you can copy and try for yourself: https://airtable.com/shr1Ymp6Z39jsN1Pq

//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, and then rank it 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 rank
let fieldToRank = await input.fieldAsync("Select the field you want to rank by.  Please pick a number, percent, or currency field.", table);

//You can replace the above line with the following if you know which field you're going to be ranking by
//let fieldToRank = table.getField("[FIELD NAME]")
//For the purposes of this example base, it would be:
//let fieldToRank = table.getField("Amount")

//Check whether the fieldToRank is a number, percent, or currency field to ensure it works as intended.  If it's not, stop the code and inform the user why
if (fieldToRank.type === "number" || fieldToRank.type === "percent" || fieldToRank.type === "currency"){

	//Allow the user to choose the field they want to update with the rank number
	let fieldToUpdate = await input.fieldAsync("Select the field for the 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")

	//Shows the user some text so they know something's happening
	output.text("Running...");

	//Selects the records from the table and then sorts it by descending
	let query = await table.selectRecordsAsync({
	    sorts: [
	        {field: fieldToRank, 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++
		//Update the selected field of the row with the rank number
		await table.updateRecordAsync(record.id, {
		    [fieldToUpdate.name]: 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...");
		}
		//Show a "Done" message to the user once the loop is complete
		if (rankNumber === query.records.length){
			output.text("Done!");
		}
	}
}
else{
	output.text("Sorry, the field you selected is not a number, percent, or currency field. Please start over.");
}
//Feedback form in case you want something fixed: https://airtable.com/shrXdwsEXSsd02oMI

It’s really basic, but I wanted to put it out there in case someone found it useful.

Apologies if it’s messy / oddly formatted; I don’t have much experience with development and I think I may have went a bit too far with my commenting.

This was heavily inspired / influenced by JonathanBowen’s Script Block: Find & Replace Text, so a lot of the formatting / concepts will look familiar to his work (Big shout out and thank you to him). That said, all mistakes are my own.

Thank you!

17 Replies 17
Joelle_Gaillard
4 - Data Explorer
4 - Data Explorer

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.

Hi @Joelle_Gaillard! I believe this might be because the script in your post does table.selectRecordsAsync twice

First time:

Second time:

And the ranking is being done to the second one, when you want it to be done on the first.

I’ve made some modifications that I think might help below, could you try it out? Thanks!

//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");
	
	
	//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"},
				]
			});

			let filteredRecords = query.records.filter(presta => {
		    	return presta.getCellValue('record id etape').includes(catAclasser.getCellValue('record id'))
			})

			console.log(filteredRecords);

			//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 filteredRecords.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 @Adam_C for the quick response.

The script returns an error on line 92:
TypeError: filteredRecords.records is not iterable
at main on line 92

line 92 is : for (let record of filteredRecords.records) {

An idea ?

Hi @Joelle_Gaillard, ah, apologies, please change filteredRecords.records to filteredRecords.

@Adam_TheTimeSavingCo Thank you, it works perfectly.

Joelle_Gaillard
4 - Data Explorer
4 - Data Explorer

Hello,

I’m back with this script. I now have an error and it doesn’t seem to have changed anything in my database.

The error is :
TypeError: Cannot read properties of null (reading ‘includes’)
at on line 84
at main on line 83

let filteredRecords = query.records.filter(presta => {
return presta.getCellValue(‘record id etape’).includes(catAclasser.getCellValue(‘record id’))
})

Do you know what the problem is? @Adam_TheTimeSavingCo ?

Thanks a lot for the help

Hi,
I think, you have empty getCellValue(“record id etape”) at some place.
try quick fix:

let filteredRecords = query.records.filter(presta => {
return presta.getCellValue('record id etape')?.includes(catAclasser.getCellValue(‘record id’))
})

you can re-write logic of your script. In other words, you asked the guy “what’s your name?”. Then you went to the crowd of people and ask each of them “are you a friend of $guy’s_name ?”, filtering them.
Instead, you can ask the guy “give me a list of your friends” :slightly_smiling_face:
it’s quite handy because “full power” of selectRecordsAsync can be used - not only ‘sorts’ parameter, but ‘recordIDs’ will output exactly the list you need.

Joelle_Gaillard
4 - Data Explorer
4 - Data Explorer

Magic! It works!
I would never have found it without your help.

Many thanks