Automatically sort linked records in a cell by name with this automation script

Hi Omer !!! Is there any chance that you can help me modify this code to order linked records by date ?
I´m not a programmer and need this functionality.
Find attached screenshots of my problem.
Thanks a lot !!!

Hi Luis!

Try this modification of the script.
It assumes your linked records’ key is a date, as it seems from your screenshots.
In the script, you should replace “tableName” and “linkedRecordsField” with values according to your base.

Good luck!

// Set a trigger: when a record is updated -> watch the linked records field
// Set an action: run a script
// You should set the record ID of the triggering record as an input variable

// Grab the triggering record's ID
let inputConfig = input.config();
let recordID = inputConfig.recordID;


let table = base.getTable("tableName"); // Load table
let queryResult = await table.selectRecordsAsync(); // Get all records in the table
let record = queryResult.getRecord(`${inputConfig.recordID}`) // Find the one with the ID from the input

// Get the linked records array
let linkedRecordsArray = record.getCellValue("linkedRecordsField");

// Sort the linked records array
// Assumes the linked recorods' key is a date
linkedRecordsArray.sort(function(a,b){
  return new Date(b.name) - new Date(a.name);
});

// Update the triggering record with the sorted linked records array
table.updateRecordAsync(record, {
    "linkedRecordsField": linkedRecordsArray
})

Hi Omer !!!

I’m getting:

ERROR

TypeError: Cannot read property ‘sort’ of null
at main on line 21

Do you want me to give you access to my DB ?
And please let me know how much do i owe you for this !! This is very helpfull to me !!
Thanks
Luis

This is probably because there is some records that have NO LINKED values, it is possible ?

Here’s an updated script.
It shows some errors in the editor but it looks like it works.

// Set a trigger: when a record is updated -> watch the linked records field
// Set an action: run a script
// You should set the record ID of the triggering record as an input variable called "recordID" (without the quotation marks)

// Grab the triggering record's ID
let inputConfig = input.config();
let recordID = inputConfig.recordID;


let mainTable = base.getTable("Main table"); // Load the main table - change "Main table" according to your base
let datesTable = base.getTable("Dates table"); // Load the table containing the linked records that have a date field - change "Dates table" according to your base
let mainQueryResult = await mainTable.selectRecordsAsync(); // Get all records in the mainTable
let datesQueryResult = await datesTable.selectRecordsAsync(); // Get all records in the datesTable
let record = mainQueryResult.getRecord(`${inputConfig.recordID}`) // Find the updated record by using the ID from the input

// Get the linked records array
let linkedRecordsArray = record.getCellValue("Linked records field"); // Change "Linked records field" according to your base

if (linkedRecordsArray) {
  // Add the date field to each linked record object
  for (var linkedRecord of linkedRecordsArray) {
    linkedRecord.date = datesQueryResult.getRecord(linkedRecord.id).getCellValue("Date field"); // Change "Date field" according to your base
  }

  // Sort the linked records array by date
  // Yo can change the order by switching c and d
  linkedRecordsArray.sort(function (a, b) {
    var c = new Date(a.date).getTime();
    var d = new Date(b.date).getTime();
    return c - d;
  });

  // Remove the date attribute from the linked records objects so it can be put back to the linked records field
  for (let obj of linkedRecordsArray) {
    delete obj.date;
  }

  // Update the triggering record with the sorted linked records array
  mainTable.updateRecordAsync(record, {
    "Linked records field": linkedRecordsArray // Change "Linked records field" according to your base
  })
}

It worked like a charm !!! Thanks a lot Omer !!

// Set a trigger: when a record is updated -> watch the linked records field
// Set an action: run a script
// You should set the record ID of the triggering record as an input variable called "recordID" (without the quotation marks)

// Grab the triggering record's ID
let inputConfig = input.config();
let recordID = inputConfig.recordID;


let mainTable = base.getTable("Main table"); // Load the main table - change "Main table" according to your base
let datesTable = base.getTable("Dates table"); // Load the table containing the linked records that have a date field - change "Dates table" according to your base
let mainQueryResult = await mainTable.selectRecordsAsync(); // Get all records in the mainTable
let datesQueryResult = await datesTable.selectRecordsAsync(); // Get all records in the datesTable
let record = mainQueryResult.getRecord(`${inputConfig.recordID}`) // Find the updated record by using the ID from the input

// Get the linked records array
let linkedRecordsArray = record.getCellValue("Linked records field"); // Change "Linked records field" according to your base

if (linkedRecordsArray) {
  // Add the date field to each linked record object
  for (var linkedRecord of linkedRecordsArray) {
    linkedRecord.date = datesQueryResult.getRecord(linkedRecord.id).getCellValue("Date field"); // Change "Date field" according to your base
  }

  // Sort the linked records array by date
  // Yo can change the order by switching c and d
  linkedRecordsArray.sort(function (a, b) {
    var c = new Date(a.date).getTime();
    var d = new Date(b.date).getTime();
    return c - d;
  });

  // Remove the date attribute from the linked records objects so it can be put back to the linked records field
  for (let obj of linkedRecordsArray) {
    delete obj.date;
  }

  // Update the triggering record with the sorted linked records array
  mainTable.updateRecordAsync(record, {
    "Linked records field": linkedRecordsArray // Change "Linked records field" according to your base
  })
}

This lovely script to reorder links by date is very useful and can easily be modified to sort records by other properties as well, including doing multidimensional sorts (e.g. first sort by a numerical value, then sort by date, then sort by name).

One thing though:

For the selectRecordsAsync() functions, I would recommend putting in some options in order to keep the script from trying to pull all the data from the tables, which can overload the memory and processing limits for the script if your tables have a lot of data in them. Here’s an example of how to modify the relevant bits of Omer’s code to do this:

let mainQueryResult = await mainTable.selectRecordsAsync({ fields: ["Linked records field"] });
let datesQueryResult = await datesTable.selectRecordsAsync({ fields: ["Date field"] });

This change will cause the script to only load the fields that it needs to know about in order to perform the sorting. Otherwise the functionality will be the same. Doing this in advance can save you from having the automations stop working at an inopportune time when the tables get too big.

My apologies for the very basic question. I am a new-comer to scripting.

The instructions above state “You should set the record ID of the triggering record as an input variable.”

How exactly is this done? What is the code used to set the record ID. Specifically, my field that has the linked records is in a table called INVESTMENT and the field is called LEDGER. These records are coming from the LEDGER table.

Thank you so much in advance!

Note that the triggering record in this script is the parent record that contains the linked record field. It is not the child record that contains the date. Thus, you need to be careful with how you structure your trigger so that it will update when the child record’s date changes.

The instructions above state “You should set the record ID of the triggering record as an input variable.”

How exactly is this done?

@Omer_Frydman script is designed to run as a Run script action inside an automation.

When you’re configuring the script in the Edit code window, there will be a sidebar that shows Input Variables. Here is what it looks like when configured properly for this script:

In the name field, just type the name as shown. In the value field, click the blue + button, click Record from step 1 and then Airtable record ID in the lists that appear in the window.

@Omer_Frydman Thanks so much for posting this, looks exactly what I need but do not have the skills to write myself!

One quick question before I try to implement - does the field to sort by need to be the linked records’ key or can it be another field in the linked record table? Note that I'm also looking to sort by date.

Thanks in advance 🙂 

@luke247 My pleasure!

The field to sort by doesn't have to be the linked record's key, it can be another field.

Have a look at this snippet from the code:

You can change the "Date field" according to your base.

works for multi-select fields, too.

if someone can explain to me what exactly the {return -1;}, {return 1} and return 0 do in the following block of code, I would be much appreciative.

linkedRecordsArray.sort(function(a, b){
  var x = a.name.toLowerCase();
  var y = b.name.toLowerCase();
  if (x < y) {return -1;}
  if (x > y) {return 1;}
  return 0;
});

// Set a trigger: when a record is updated -> watch the linked records field
// Set an action: run a script
// You should set the record ID of the triggering record as an input variable called "recordID" (without the quotation marks)

// Grab the triggering record's ID
let inputConfig = input.config();
let recordID = inputConfig.recordID;


let mainTable = base.getTable("Main table"); // Load the main table - change "Main table" according to your base
let datesTable = base.getTable("Dates table"); // Load the table containing the linked records that have a date field - change "Dates table" according to your base
let mainQueryResult = await mainTable.selectRecordsAsync(); // Get all records in the mainTable
let datesQueryResult = await datesTable.selectRecordsAsync(); // Get all records in the datesTable
let record = mainQueryResult.getRecord(`${inputConfig.recordID}`) // Find the updated record by using the ID from the input

// Get the linked records array
let linkedRecordsArray = record.getCellValue("Linked records field"); // Change "Linked records field" according to your base

if (linkedRecordsArray) {
  // Add the date field to each linked record object
  for (var linkedRecord of linkedRecordsArray) {
    linkedRecord.date = datesQueryResult.getRecord(linkedRecord.id).getCellValue("Date field"); // Change "Date field" according to your base
  }

  // Sort the linked records array by date
  // Yo can change the order by switching c and d
  linkedRecordsArray.sort(function (a, b) {
    var c = new Date(a.date).getTime();
    var d = new Date(b.date).getTime();
    return c - d;
  });

  // Remove the date attribute from the linked records objects so it can be put back to the linked records field
  for (let obj of linkedRecordsArray) {
    delete obj.date;
  }

  // Update the triggering record with the sorted linked records array
  mainTable.updateRecordAsync(record, {
    "Linked records field": linkedRecordsArray // Change "Linked records field" according to your base
  })
}

Thank you, @Omer_Frydman ! This is such a life saver for my database! Abd thank you, @Katerie, for the memory-saving addition!

linkedRecordsArray.sort(function(a, b){
  var x = a.name.toLowerCase();
  var y = b.name.toLowerCase();
  if (x < y) {return -1;}
  if (x > y) {return 1;}
  return 0;
});

In short, JS sort() works good for strings, to sort in alphabet order, but



If you want to sort something else, you need to define sort function.
to sort numbers ascending, you can use sort( function (x,y) {
 return x-y
} , or its better to use arrow-function form

function should return negative when first argument less than second,  zero for equal and positive when second is less.  

for descending order, you can just swap  in such way:    sort ( ( x , y ) => y - x )

you cannot compare strings by substraction, but you can do it with '<' , ' > ' 
so, you can use function, in shorter form it looks like