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

[Omer_Frydman]

If you’re using a rollup field to summarize linked records with ARRAYJOIN(), the array’s order is determined by the order of the linked records.

I created this script to automatically sort linked records in a cell.
My use-case is to summarize interactions with companies.
I sort by descending order to show the latest summary on top.

Enjoy!

// 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
// To sort in descending order, switch the -1 and 1
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;
});

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

[Luis_Canal1]

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 !!!

[Omer_Frydman]

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
})

[Luis_Canal1]

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

[Luis_Canal1]

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

[Omer_Frydman]

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
  })
}

[Luis_Canal1]

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

[Katerie]

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.

[Brian_Reeds]

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!

[kuovonne]

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.