Skip to main content

Sorting linked record field values and annoying updateRecordsAsync() behavior

S

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

11 replies

S
  • Stephen_Orr1
  • Author
  • Inspiring
  • 272 replies
  • June 16, 2023

@kuovonne tagging you because you might be familiar with this sort of thing. 😅 Hope this is not too bothersome. Thanks

Karlstens
  • Karlstens
  • Inspiring
  • 601 replies
  • June 16, 2023

So am i understanding correctly, that you have a linked field in Table A and Table B, and that each field in both tables have multiple records enable - and that as you sort and update those linked records in Table A, the table B records also refresh and lose their previous sort order. And then when you sort and update those records in Table B, your previous initial sort/update on Table A is reset with the order randomised again?

I thought I recently spotted this problem myself in a base I’m working on, but then checked/redesigned the schema so that multi-linked records was only in one of the fields, but not the other. Ie: One project can link to many tasks, but a task can only have one project. 

Interested in how this double sided sort problem may be resolved. 

S
1 person likes this
  • S
    Stephen_Orr1
S
  • Stephen_Orr1
  • Author
  • Inspiring
  • 272 replies
  • June 16, 2023
Karlstens wrote:

So am i understanding correctly, that you have a linked field in Table A and Table B, and that each field in both tables have multiple records enable - and that as you sort and update those linked records in Table A, the table B records also refresh and lose their previous sort order. And then when you sort and update those records in Table B, your previous initial sort/update on Table A is reset with the order randomised again?

I thought I recently spotted this problem myself in a base I’m working on, but then checked/redesigned the schema so that multi-linked records was only in one of the fields, but not the other. Ie: One project can link to many tasks, but a task can only have one project. 

Interested in how this double sided sort problem may be resolved. 


@Karlstens That's exactly right. It also doesn't happen with multi-linked records pointed at the same table, which makes sense.

Yeah I don't really see a way around this unless Airtable decides to updateRecordsAsync() specifically to handle linked record fields differently. Probably not going to make it to the top of todo list but I can confirm "field value sorting" provides enough of a visual aid to be valuable to some users at least. Either a UI option or updateRecordsAsync() would be awesome! 

Karlstens
  • Karlstens
  • Inspiring
  • 601 replies
  • June 16, 2023
Stephen_Orr1 wrote:

@Karlstens That's exactly right. It also doesn't happen with multi-linked records pointed at the same table, which makes sense.

Yeah I don't really see a way around this unless Airtable decides to updateRecordsAsync() specifically to handle linked record fields differently. Probably not going to make it to the top of todo list but I can confirm "field value sorting" provides enough of a visual aid to be valuable to some users at least. Either a UI option or updateRecordsAsync() would be awesome! 


When I was considering investigating this behaviour in my own base, but opted for schema change/simplification - one aspect that I think(?) I spotted, was that, so long as the updated records contain all the same record ids (although in a different sorted order), that the child shouldn't lose their previous order... but then, I can't remember if I then proved this not to be the case, hence opting to reroute my base to avoid this very problem.

 

EDIT: Revisiting this problem again in my testing base - and yes, it is indeed a problem that remains apparent and thus I avoided by design! Keen to hear how to go about solving this one too.

Karlstens
  • Karlstens
  • Inspiring
  • 601 replies
  • June 16, 2023

@Stephen_Orr1  - I think I've found a method to approach this problem. I had a look at the official Airtable extension Batch Update - and noted that when sorting table A linked records, that all of table B's links were reshuffled into a very specific order - the exact order of how they look in the table A grid view.

I think it might be possible to script this - I'm going to find out. The one major limitation of the Batch Update app is that you can't sort based on a records lookup fields - where with a script solution, you can - but the problem being with my current sort script, it doesn't adhere to the sorting of the second table, which I'm hoping to resolve now.

Note my primary table order, 2 - 3 - 1

And note how the secondary table sorted - it looks to be based off of the Grid View order when using the Airtable Batch Update app.

 

kuovonne
Forum|alt.badge.img+17
  • kuovonne
  • Brainy
  • 5987 replies
  • June 16, 2023

I have both an extension and an automation script for sorting linked records.

The extension requires an premium license to work on views of more than a handful of records. You can try out the free version of the extension on a view with a handful of records to see if it does what you want. However, the extension cannot be run automatically. A human must push the button.

The automation script is a bit different, has completely different logic, and can be a little tricky to setup. I haven't tried running the automation on every table in the base, and it would need a lot of named automations and a lot of automation runs in order to work for every table. There are no refunds on the automation script, so if you want to share a test base with me and describe the desired behavior, I can make a copy of the test base and see if my automation script would be a fit.

Both the extension and automation script support sorting by a field in the linked table that is not the primary field. The most common use case is people wanting linked records to be sorted in reverse chronological order by a date field that does not appear in the primary field.

Alexey_Gusev
Forum|alt.badge.img+12

I didn't try, but it looks simple - if links appear in cell in the order of their creation, you should sort query by record name (or primary field). I would also half the array of linked fields using  inverseLinkFieldId option to avoid doing the same job twice.
NOTE: you should update all non-empty link cells, no matter if they already sorted or have a single link. otherwise, untouched will be at the beginning in the other linked field.  

Alexey_Gusev
Forum|alt.badge.img+12

update:
I checked and it works.
Tested on  'Frequent Flyer Ultimate Guide' by Howie Liu template from Airtable Universe . Written before Apr 2000, it has reversed links order, I disabled this option on all fields (btw doc says Once you untoggle this option on all attachment and linked record fields in the base, the option to show the items in reverse order will no longer display, but in my case it didn't happen)

according to docs
"When the toggle is on (legacy behavior), any new linked record you select will be added to the beginning of the list. The default for new linked record fields is for the toggle to be off so that items are listed in the order they are added to the cell."

So, in order to sort links in both tables simultaneously, we need to rewrite each record with sorted cell values, and we need to rewrite them in correct order. My idea with 'half the array' was bad for multiple links on the table, but the main idea was right and the key is 

let query=await table.selectRecordsAsync({fields:links(table),sorts:[{field:table.fields[0]}]})

not sure if it's the 100% right way to get primary field, but I wrote it just for example
I won't post the code source so as not to interfere with its commercial use, and because I can't guarantee anything, but in example case it worked.

 

S
1 person likes this
  • S
    Stephen_Orr1
Karlstens
  • Karlstens
  • Inspiring
  • 601 replies
  • June 22, 2023

With needing linked records in two tables sorted - my method that I've previously tinkered with does have a bug - where if the child records are coincidentally already sorted, then they don't update - which results in the child's parent field records not updating to their correct order - which would be a huge dilemma if anyone's running a script with the expectation that every record within two tables are about to be refreshed and sorted - only to find that some edge cases haven't...

The solution to this, at least in my mind, is to parse through the sort script twice. The initial parse as the opposite sort direction as to what's required, and then the final parse as to the desired sort direction - and this will trigger all records linked fields, and their linked records parent fields to have the correct order, with no edge cases skipped.

S
1 person likes this
  • S
    Stephen_Orr1
Alexey_Gusev
Forum|alt.badge.img+12
Karlstens wrote:

With needing linked records in two tables sorted - my method that I've previously tinkered with does have a bug - where if the child records are coincidentally already sorted, then they don't update - which results in the child's parent field records not updating to their correct order - which would be a huge dilemma if anyone's running a script with the expectation that every record within two tables are about to be refreshed and sorted - only to find that some edge cases haven't...

The solution to this, at least in my mind, is to parse through the sort script twice. The initial parse as the opposite sort direction as to what's required, and then the final parse as to the desired sort direction - and this will trigger all records linked fields, and their linked records parent fields to have the correct order, with no edge cases skipped.


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.

S
1 person likes this
  • S
    Stephen_Orr1
Karlstens
  • Karlstens
  • Inspiring
  • 601 replies
  • June 22, 2023
Alexey_Gusev wrote:

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.


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.

S
1 person likes this
  • S
    Stephen_Orr1

Reply


Most helpful members this week

Terms & Conditions