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

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

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

Captura de Pantalla 2020-12-02 a la(s) 09.39.21

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