Help

Re: Script Block: Rank Descending and Update Field

7203 3
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
Team_Upp
5 - Automation Enthusiast
5 - Automation Enthusiast

Dude, this is incredible. I built a gamification system on Airtable, but not having a RANK function sucks and I’ve never been able to gave a basic rank number for my participants. Do you think it would work with other fields as tiebreakers?

Hi guys, I’ve updated the script in the base with the following changes:

  1. Now allows users to rank by two fields in the order that the user selects the fields to rank by, thus handling the tiebreaking scenario mentioned in @Team_Upp’s post.
  2. Now uses updateRecordsAsync instead of updateRecordAsync. The previous version of this script would exceed the 30 seconds of Automation script run time if there were a lot of fields to update; with this change that should no longer be an issue. (It’s also a lot faster in general)

===

Big thanks and shoutout to @Stephen_Suen whose post I learned how to use updateRecordsAsync from.

Big thanks also to @Team_Upp who helped with research and without whom these improvements would not have been made.

As always, all mistakes are my own.

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

//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, or currency 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, or currency 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"){


	//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, or currency 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, or currency 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"){


		//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, or currency field. Please start over.`);
	}
}
else{
	output.text(`Sorry, the field you selected (${firstFieldToRank.name}) is not a number, percent, or currency field. Please start over.`);
}
//Feedback form in case you want something fixed: https://airtable.com/shrXdwsEXSsd02oMI
Team_Upp
5 - Automation Enthusiast
5 - Automation Enthusiast

Adam has been nothing short of incredible in helping me out and the scripts he wrote always worked flawlessly. This is probably a great use case to be replicated to other users.

Thanks again for helping so much and sharing with the community!

Hi Adam,

I have a database of ski resorts, and calculated a raw score for each one. I’m trying to run this, but am having the issue that the value is stored in a formula field, not a number field.

Any thoughts on how to resolve this?

Thanks,

Hugo

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

  1. “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
Jonm
4 - Data Explorer
4 - Data Explorer

@Adam_TheTimeSavingCo thanks for sharing this script. I’m wondering if you can help me understand how to modify this script for my use case.

I’m maintaining a product backlog within Airtable. I manually drag and drop items UP/DOWN to rank them in priority order. I want a column to update and capture the rank value so its equal to the row within the current view. Sometimes the view gets sorted by other values, but I’ll like to be able to sort by my captured ranking to get back to my manually adjusted priority ranking.

The way I do this today is by manually entering the rank value in a column after I drag and drop items into my prioritized order. Just looking for a way to automate this.

I believe if we do a selectRecordsAsync on a specific View, it’ll return the records in the order that we’ve manually created via dragging and dropping. From there, we just loop through the result to get the position of the row in the view.

Here’s an example base: Airtable - Save Ranking

Simpla_Club
4 - Data Explorer
4 - Data Explorer

@Adam_TheTimeSavingCo you make it in a simple, flexible and extremely didactic way. Congratz, it couldn’t be better.

I needed to add a third criteria to tiebreak some cases here, and it was really simple.

Thank you so much for sharing with te community!

Julien_Cibat
4 - Data Explorer
4 - Data Explorer

Amazing!
It does everything I needed, flawlessly.
Thank you very much @Adam_TheTimeSavingCo !

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