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
Sorting linked records in automation script for summary
+3Best answer by Autumn_Grassel
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:
- Get the list of linked records from the cell value of the linked record field
- For each linked record in the list, add an attribute for the date (or other field) that you want to sort by
- Sort the list of linked records by the attribute just added
- For each linked record, delete the attribute that was added
- 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
}
)
}
}
+3I 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:
- Get the list of linked records from the cell value of the linked record field
- For each linked record in the list, add an attribute for the date (or other field) that you want to sort by
- Sort the list of linked records by the attribute just added
- For each linked record, delete the attribute that was added
- 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
}
)
}
}
+7Autumn_Grassel wrote:
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:
- Get the list of linked records from the cell value of the linked record field
- For each linked record in the list, add an attribute for the date (or other field) that you want to sort by
- Sort the list of linked records by the attribute just added
- For each linked record, delete the attribute that was added
- 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
}
)
}
}
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
+7Autumn_Grassel wrote:
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:
- Get the list of linked records from the cell value of the linked record field
- For each linked record in the list, add an attribute for the date (or other field) that you want to sort by
- Sort the list of linked records by the attribute just added
- For each linked record, delete the attribute that was added
- 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
}
)
}
}
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
}
)
}
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.