2 tables linked - when the Table 2's data is updated via API, link is broken ?

[NewAirtabler]

Hello Airtable Community,

I have 2 tables linked - the common column being "Deal Link" a.k.a "URL" (which is primary in Table 2).
I have a script that updates Table 2 via API.

The problem is, when updating via API, all the data linked in Table 1 from Table 2 disappears (even when record is unchanged). Is the link broken at this point ? How to remedy that ?

Thank you.

[Alexey_Gusev]

Hello,
Yes, it seems like breaking the link, sending empty value to the field 'Furniture'. This field is a link from Table2 to Table1, and it's a reflection of all links that you made in Table1 (even in 'one to many' or 'many to many' relations, the total number of links in Field 'Deal Link' of Table1 is the same as in Field 'Furniture' in Table2).

I think you need to review the script and fix it to don't touch link field. Or find other reason why 'Furniture' field is clean after API update.

[TheTimeSavingCo]

Hm, can you provide the script you're using so that I can help to troubleshoot it?

[NewAirtabler]

Thanks

[TheTimeSavingCo]

Hm, that's weird.  I don't see the linked field names in your script at all, which means they aren't being modified?  Hopefully someone else can help, sorry!

[NewAirtabler]

Thank you. In that case, is there any way to automate re-linking a field (from Table 1 ) to Table 2 every 1 minute for example ?

[Alexey_Gusev]

Hi,
Expand any record in Table2, try with changed and unchanged and look at revision history

Just to ensure it is done by script.

But in other case I suspect, that you can see records without history. Text column 'Deal page link copy' looks like it was linked field, but then linked table was completely removed, and re-created again with all fields and new linked field. I don't know why and not sure. Extension/automation script can't remove table or field, but API scripting - maybe yes.
The problem is that lookups still rely on the field, which is no more linked.
The only thing I didn't get - where formula in Table1_post-update takes these values if links are empty

I'm not sure automation can help with it, because in-base scripting has very limited abilities to change base schema elements compared to API scripting.

I don't know python, but one thing abstract from language, looks suspicious:
object to create record  usually looks like object to update, without 'id' property. I mean - it should be wrapped into 'fields', which is absent in script. (I might be wrong, just guessing possible reasons)

Anyway, you can also map all data to fields, without dividing update/create and throw this payload into webhook automation. Where you can use script step and gently put data into the table. It's old JS script, but i think it's easy to adjust it, I can help if you choose this way, but I hope somebody ever used python and Airtable Web API can suggest easier solution.

let json = await response.json();
let table = base.getTable('Tab2');
let query=await table.selectRecordsAsync({fields:['ID']});
let existed=new Map(query.records.map(r=>[r.getCellValue('ID'),r.id]))
const update=(el)=>({'id':existed.get(el.ID),'fields':{'Title':el['Title']}})
const create=(el)=>({fields:{'Title':el.Title,'ID':el.ID }})
let upd=[];let crt=[]
json.forEach( j=>{ existed.has(j.ID)? upd.push(update(j)) : crt.push(create(j)) }) 
while (upd.length) await table.updateRecordsAsync(upd.splice(0,50))
while (crt.length) await table.createRecordsAsync(crt.splice(0,50))