Help

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

4842 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Omer_Frydman
5 - Automation Enthusiast
5 - Automation Enthusiast

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
})
15 Replies 15
Luis_Canal1
5 - Automation Enthusiast
5 - Automation Enthusiast

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.37.53 Captura de Pantalla 2020-12-02 a la(s) 09.38.23 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
})
Luis_Canal1
5 - Automation Enthusiast
5 - Automation Enthusiast

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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
  })
}
Luis_Canal1
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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:

Screen Shot 2021-08-14 at 2.38.57 PM

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
4 - Data Explorer
4 - Data Explorer

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

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
4 - Data Explorer
4 - Data Explorer
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
7 - App Architect
7 - App Architect

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

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