Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Apr 12, 2023 03:37 PM - edited Apr 12, 2023 03:40 PM
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 : )
Solved! Go to Solution.
Apr 17, 2023 02:04 PM - edited Apr 17, 2023 03:39 PM
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-summar...
I swapped out the field and table names and voila!
Here's the new script with the changes in bold:
Apr 12, 2023 10:45 PM
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
Apr 13, 2023 02:41 AM - edited Apr 13, 2023 02:43 AM
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.
Apr 13, 2023 06:03 AM
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.
Apr 13, 2023 02:01 PM
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.
Apr 13, 2023 02:23 PM
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.
Apr 13, 2023 02:31 PM
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.
Apr 13, 2023 02:42 PM
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:
Apr 14, 2023 04:23 AM
Apr 17, 2023 02:04 PM - edited Apr 17, 2023 03:39 PM
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-summar...
I swapped out the field and table names and voila!
Here's the new script with the changes in bold: