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

[Katerie]

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.

[luke247]

@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 🙂 

[Omer_Frydman]

@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:

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
  }

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

[Marc_Z]

How would I change this if I want to sort by a field that is a number?

I'm defining the field here: 

if (linkedRecordsArray) {

// Add the position field to each linked record object

for (var linkedRecord of linkedRecordsArray) {

linkedRecord.position = linkQueryResult.getRecord(linkedRecord.id).getCellValue("Position Sync"); // Change "field" according to your base

}

But I have not found a way to use the field result in the following code

by changing 'a.name.toLowerCase();' to use it.

// 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;

});

The code works otherwise but sorts by the linked record's name.

[Eli_Kent]

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

[Aernout_de_Beau]

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

[Alexey_Gusev]

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

numbersArray.sort( ( x, y ) => x - y )

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

sort( ( x ,y )=> x.name.toLowerCase() < y.name.toLowerCase() ? -1 :
  x.name.toLowerCase() > y.name.toLowerCase() ? 1 :0 )
//means if(x<y is true) then -1 else ( if (x>y) then 1 else 0 )