Skip to main content

Hi folks,

I've been trying to solve this puzzle all day; there are quite a few scripting examples in the community here that I've tried to adapt to my use, to no avail.

Here's the situation:

These two columns should be ordered exactly the same, except that the one on the right gets jumbled up during a "find records" step in an automation that populates this field.

They're ordered this way in the line items table, but for some reason the "find records" step jumbles them up:

I need a script or formula that will re-order the line items in ascending order according to the purple-circled numbers in the line item IDs. 

I have used Batch Update to do the job, but I'm looking for an automated solution that will sort the line items for use in a Zap that creates an invoice - and the line items need to be in a particular order.

Thanks for your help in advance,

Shawn : )

 

Hmm...if you use a "Find Record" step based on a view instead of conditions it returns stuff in the view's sort order.  Any chance we can modify the data structure so that your automation uses a find based on views instead?

Here's a base I was testing this out in in case you find it useful

The view trick that Adam listed above wonā€™t work for updating your records in a certain order, so youā€™ll need to use alternative methods.

This thread contains some scripting resources for sorting linked records.

if you donā€™t want to use scripting, Make is a low-code automation platform that lets you specify the sort order with each find. (Make is significantly less expensive and significantly more powerful than Zapier.)

Make has a bit of a learning curve, so Iā€™ve created some basic tips here.

Will sorting the linked record field work for you, or do you need to sort the ā€œFind recordsā€ result? Note that the order of a records in a linked record field is independent of the order of records in a grid view.

I wrote the Sort Linked Records extension and its companion automation script to sort linked records. The exterior for one-time bill sorting at button press. The automation script is for ongoing sorting. You can download the extension in the marketplace and get a premium license from my Gumroad store at the links above. 

Thanks @TheTimeSavingCo , @ScottWorld , & @kuovonne  😊

I believe it is the "Find records" result that needs to be sorted, as that is the point at which the records become disordered from the order they're in on the OPTIONS table. 

My thinking has been if I can sort them in the LINE ITEMS field according to an autonumber field after they've gone through the "Find records" step, that would work best - but if there's a way to sort the result of the "Find records" step, so they're then entered as linked records in the LINE ITEMS field, that would be great.

Will sorting the linked record field work for you, or do you need to sort the ā€œFind recordsā€ result? Note that the order of a records in a linked record field is independent of the order of records in a grid view.

I wrote the Sort Linked Records extension and its companion automation script to sort linked records. The exterior for one-time bill sorting at button press. The automation script is for ongoing sorting. You can download the extension in the marketplace and get a premium license from my Gumroad store at the links above. 


The Sort Linked Records extension definitely does the trick, same as the Batch Update extension - but I need it to be automated. I purchased the automation script, but upon reading that it "does not alter the triggering record, but rather all of the records that are linked to the triggering record", I don't think it will be useful to me at all. I need the linked records in the linked field in the triggering record to be sorted according to an autonumbered order from the linked table.

 

How are you using the results of the "Find records" action? Depending on how you are using the results, it may or may not be possible to sort them using a script.

However, it is usually best to keep records in order in the first place.

The results of the "Find records" action are used as a list to create a series of new records in a line-items table:

These new records sort themselves according to an order determined by an autonumbered lookup field from a table containing all of the available options to be chosen as line items:

But when those line items are then entered by the automation into the linked record field in the original table, they are all jumbled:

ā€ƒ

Unfortunately, this is only possible with scripting or a low-code automation tool that allows sorting such as Make.

Make has a bit of a learning curve, so Iā€™ve created some basic tips here.

After spending almost two weeks trying to solve this problem, I found the solution in this thread:

https://community.airtable.com/t5/automations/sorting-linked-records-in-automation-script-for-summary/m-p/147429/highlight/true#M10583

I swapped out the field and table names and voila! 

Here's the new script with the changes in bold:

// 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 number from smallest to largest
        linkedlineitems.sort((a, b) => (a.number < b.number) ? -1 : 1)


        // remove the added number 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
            }
        )
    }

 

@InfoWrangler Would you be able to add a screenshot of your entire automation so I could see how you are using this script within the automation? Also, thanks for posting the script - super helpful! 😁 

Reply


Terms & Conditions