Bug: Can't associate more than 30 linked records in a updateRecordAsync() call

[dk82734]

I have a single base and two tables. Within each table, there’s a linked field that points to records in the other table.

For the sake of simplicity, let’s call them:
Table A
Table B

Both tables have many, many linked fields. For example, 1 record in table A has let’s say over 950 linkages to other records in table B.

Now, I’m in the process of updating an Automation with a script that updates a single record in table A using the updateRecordAsync() function, like this:

await table_A.updateRecordAsync(record_id, {
“linked_field_to_records_in_table_B”: array_of_record_ids
});

^ When the array_of_record_ids is small (like between 1 and 5 entries), this call works fine.

But as soon as the array_of_record_ids is size 30 or larger, I keep getting these sorts of errors:

Error: network timeout at: https://api.airtable.com/v2/bases/app.../tables/tbl.../records/update
at main on line 47

I suspect the problem has to do with the fact that Airtable is attempting to update all the back-links from B->A; however, I’ve hit a wall, because…

1. Airtable doesn’t provide a way to APPEND new linked records to an existing field… you have to wholesale replace everything (which is naturally very expensive).

Now someone is going to say, “well you should be updating 50 records at a time using updateRecordsAsync() instead!” … that approach would be to make 30 calls to update the backlinks, like:

await table_B.updateRecordAsync(record_id, {
“linked_field_to_records_in_table_A”: array_of_record_ids
});

^ However, the size of this array_of_record_ids is around 950 (not 30), which is even worse.

So… it honestly seems like this is a limitation of Airtable altogether… meaning, they don’t expect you to ever have more than about 25 linked records in a single field, ever… and if you do, forget about using Automation or API calls on that data, because any changes to that field will consistently fail.

[Alexey_Gusev]

Hi,

Airtable doesn’t provide a way to APPEND new linked records to an existing field
You can concatenate ‘old_array_of_ids’ with ‘new_array_of_ids’. in your case, it’s you single rec id

it should be smth like (edit lines 1,2,3 )

const table = base.getTable('your_table'); 
const LINK='your_link';
const single_id=input.config().single_rec_id 
const query = await table.selectRecordsAsync({fields:[LINK]}); 
const addlink=rec=>({id:rec.id,fields:{LINK:[...rec.getCellValue(LINK),single_id]}});  
const updates=query.records.map(addlink);
while (updates.length) await table.updateRecordsAsync(updates.splice(0, 50));

[dk82734]

Thanks for trying… I tested your code and Airtable is still kicking out timeout errors:

Error: network timeout at: Sign in - Airtable…
at main on line 23

As soon as I add the LINK field into the selectRecordsAsync() call, it throws this error. I think it’s because there’s too much data (too many linkages for it to deal with).

[kuovonne]

I have been able to write scripts, including automation scripts, that update a linked record field with hundreds of linked records, and I do not receive this timeout issue. (I don’t actually recommend having one record linking to hundreds of records–it makes for a very slow base.)

What happens when you convert the script to run from a button in Scripting App? Do you get the same error?

How many other fields are you also trying to update at the same time?

Can you share a screenshot of the error?

[dk82734]

The base I’m working with has many, many linkages across many, many tables (more than 2) – yes, a graph database may have been a better approach (long-term), but I’m forced to manage this legacy implementation.

To answer your other question, I started implementing the logic in an Automation block (as a “Run script”) – I then encountered the timeouts when manually test running the script. Next, I proceeded to port the Javascript out to a button from the Scripting App – again, test runs yielded the same timeouts.
Finally, I ditched Javascript altogether and migrated the implementation over to Integromat/Make.com as a Scenario – again, same network timeouts.

So, I’m pretty sure the issue is at the API layer and not at some other middleware layer.

What is interesting, though, is that Airtable APIs responsible for “linked record” updates appear to be “somewhat smart”. There’s some odd implementation details that I’ve inferred from additional testing.

Basically, when you populate a linked field with a single linkage, Airtable internally creates the forward linkage as well as the backward linkage. So each link update yields TWO “internal operations”. Sometimes updating the forward linkage is easy… and sometimes updating the backward linkage is hard (because maybe the backward linkage has many, many other linked records to iterate through).

Next, I measured the API round-trip time going from 0 links to 1 links – consider that a baseline measurement.

Then, I measured the API round-trip time going from 0 links to 2 links – that appears to be double the cost (which makes sense).

I then measured the API round-trip time going from 1 link to 2 links (a differential) – this measurement was about the same amount of time it took going from 0 to 1. This tells me that Airtable internally does some “link caching” (which is good) and it’s only processing the DELTA computation in adding the 1 additional link.

So, I knew that going from 0 links to 31 links was causing a timeout. So then, I split up the operations in the following way:

• Go from 0 to 10 links (failure)
• Go from 0 to 5 links (success!)
• Go from 5 to 10 links (success!)

^ This tells me that I can avoid API timeouts if I split my link updates into blocks of (at most) 5 at a time.

So, to add 31 links to a record successfully, I have to split the calls up in this way:

• Go from 0 to 5 links (success)
• Go from 5 to 10 links (success)
• Go from 10 to 15 links (success)
• Go from 15 to 20 links (success)
• Go from 20 to 25 links (success)
• Go from 25 to 30 links (success)
• Go from 30 to 31 links (success)

Bottom line: Even though it may seem like adjusting linked records in a field is an expensive operation, Airtable’s APIs appear to be smart enough to preserve existing linkages and only actually process delta changes between link updates.

^ I honestly just wished Airtable could internally manage this complexity for me, rather than me having to spend hours to figure out an acceptable workaround to something within their internal API implementation. I mean, I get it, but I wish they were more transparent about these types of problems.

[kuovonne]

Wow. Thanks for the detailed explanation! This is useful information. Glad you were able to get a working solution.