Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 08, 2023 08:20 AM
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!
Solved! Go to Solution.
Mar 08, 2023 10:47 AM - edited Mar 08, 2023 02:51 PM
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:
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
}
)
}
}
Mar 08, 2023 10:47 AM - edited Mar 08, 2023 02:51 PM
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:
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
}
)
}
}
Apr 17, 2023 02:01 PM
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
Apr 17, 2023 03:41 PM - edited Apr 17, 2023 03:51 PM
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
}
)
}