Sorting linked record field values and annoying updateRecordsAsync() behavior

[Stephen_Orr1]

I'm creating a script that reorganizes linked record field values across the whole base using alphanumeric sorting. I ran into something that looks to be a limiting factor from how Airtable works but would like to pose it to the community to confirm.

The script loops through each table in the base, grabs all records of the table, sorts all linked record field values, and then applies the updated records to the table.

The problem is that this works per table using updateRecordsAsync to apply linked record field values BUT  the sort order of linked record field values in the linked table comes in randomly. In other words, sorting one table adversely affects the previously applied sorting to its linked table. When looping through tables to do a full base sort, this causes a table to sort and then unsort as soon as its linked counterpart sorts. 

I have not found a solution for this yet and this is for a client with the requirements of a one-and-done sort script that I suggested initially. I created another copy of the script that sorts a single table but this would require resorting manually every time a different table is used.

One potential solution is creating an extension that watches the active table for changes and applies the sort to the current active table when this happens. I'm not sure if Airtable intends for extensions to be used as background workers like this though and whether I'd be able to post it to the marketplace. The other way to hand it off is making it open source which I'd rather not do for a solution I'm charging for... FAQ 

Anyone have any ideas?
-Stephen

[Alexey_Gusev]

Yes, that's exactly what I told about - you shouldn't skip already sorted cells. I tested that behavior:

update test records with their current value

let table = base.getTable('test');
let query = await table.selectRecordsAsync();
let value=r=>r.getCellValue('Link').map(lnk=>({id:lnk.id}))
for (let rec of query.records) {
    output.inspect(rec.getCellValue('Link'))
    output.inspect(value(rec))
}
let upd=query.records.map(r=>({id:r.id,fields:{'Link':value(r)}}))
output.inspect(upd)
await table.updateRecordsAsync(upd)

And yes - you was right - links remain unsorted!
That was really unexpected for me before this topic.

In this case, refresh by clean worked, but after some thinking I would do it by your way with opposite sort. Because it script fails during update, table might lost many links.

let table = base.getTable('test');
let query = await table.selectRecordsAsync();
let value=r=>r.getCellValue('Link').map(lnk=>({id:lnk.id}))
const upd=query.records.map(r=>({id:r.id,fields:{'Link':value(r)}}))
const clean=query.records.map(r=>({id:r.id,fields:{'Link':[]}}))
await table.updateRecordsAsync(clean)
await table.updateRecordsAsync(upd)

I also thought about other cases, like above - when a primary field is a formula based on links. Then you need to update the table, query it again with a new sort, and do a 'refreshing update' again. And with two such tables linked on each other (possibly with more than 1 field) the task might become cyclically unresolvable.

Anyway, that's a very interesting post.

[Karlstens]

Yah, I think it's interesting too. When I get a moment, I'll link in Airtable Developers/Product Ideas to this and other threads - I feel we could all benefit with a specific efficient built-in sort function that addresses this multi-table linked field sort issue.