Sorting linked records in automation script for summary

[Matthew_Weidert]

Hi there - looking for some help sorting linked records by ascending date in an automation script. Tried using sorts in the query, but no luck so far. Appreciate it!

[Autumn_Grassel · Accepted solution]

I just did this yesterday! In my context, I had a table of People (our customers) that have registered for multiple events, and each registration record is linked.

I wanted the registration records to be linked and ordered by the date of the event people registered for. In my code, the 'Registrations' field is the linked record field that contains the records I wanted to sort.

The general steps I followed were: 

1. Get the list of linked records from the cell value of the linked record field 
2. For each linked record in the list, add an attribute for the date (or other field) that you want to sort by
3. Sort the list of linked records by the attribute just added
4. For each linked record, delete the attribute that was added 
5. Update the original record to store the now sorted list of records in the linked record field.

Below is some sample code for a Run Script action in an automation.  It requires that a record Id is included as an input variable.

// get recordId that triggered automation from
// input variables
let recordId = input.config()['recordId'];

// Change this name to use a different table
let peopleTable = base.getTable("Customers");

let registrationsTable = base.getTable('Registrations');

// Prompt the user to pick a record 
// If this script is run from a button field, this will use the button's record instead.
let record = await peopleTable.selectRecordAsync(recordId)

if (record) {
    output.text(`You selected this record: ${record.name}`);

    // get cell with linked records 
    let linkedRegistrations = record.getCellValue('Registrations');

    if (linkedRegistrations) {

        for (let registration of linkedRegistrations) {
            // get the full linked record info
            let registrationRecord = await registrationsTable.selectRecordAsync(registration.id);

            registration['date'] = registrationRecord.getCellValue('start date')
        }

        // sorts list by date from earliest date to latest date
        linkedRegistrations.sort((a, b) => (a.date > b.date) ? 1 : -1)


        // remove the added date attributes after sorting
        for (let registration of linkedRegistrations){
            if (registration.date) {
                delete registration.date;
            }
        }

        
        // output.inspect(linkedRegistrations)

        await peopleTable.updateRecordAsync(
            record.id,
            {
                Registrations: linkedRegistrations
            }
        )
    }
    
}

[InfoWrangler]

Oh man, if I could give more than 1 Kudo I'd hit it a dozen times - this is exactly what I needed!

Bravo @Autumn_Grassel - thank you!

Shawn

[InfoWrangler]

In case someone comes here looking for a solution that will work with invoice line items, here's how I adapted this script to do just that:

// get recordId that triggered automation from
// input variables
let recordId = input.config()['recordId'];

// Change this name to use a different table
let draftTable = base.getTable("BUILD DRAFTS");

let lineitemsTable = base.getTable('LINE ITEMS');

// Prompt the user to pick a record 
// If this script is run from a button field, this will use the button's record instead.
let record = await draftTable.selectRecordAsync(recordId)

    // get cell with linked records 
    let linkedlineitems = record.getCellValue('LINE ITEMS');

    if (linkedlineitems) {

        for (let lineitem of linkedlineitems) {
            // get the full linked record info
            let lineitemRecord = await lineitemsTable.selectRecordAsync(lineitem.id);

            lineitem['number'] = lineitemRecord.getCellValue('OPTION #')
        }

        // sorts list by date from earliest date to latest date
        linkedlineitems.sort((a, b) => (a.number < b.number) ? -1 : 1)


        // remove the added date attributes after sorting
        for (let lineitem of linkedlineitems){
            if (lineitem.number) {
                delete lineitem.number;
            }
        }

        
        // output.inspect(linkedlineitems)

        await draftTable.updateRecordAsync(
            record.id,
            {
                "LINE ITEMS": linkedlineitems
            }
        )
    }