Help

Re: Copy records from one table to another and updating existing ones.

1830 7
cancel
Showing results for 
Search instead for 
Did you mean: 
Michiel
4 - Data Explorer
4 - Data Explorer

Hi y’all. 
I’m breaking my head over the next automation.

I want to copy records from one table (A) to another (B) or update existing records in table B (Input table=A ; Copy table=B)

Input Table A is using the import excel possibility. This is raw data from another source. In order to import correctly i’ll have to empty table A every time before importing the new data. Not using this function is out of the option because certain data fields disappear when trying to copy paste. 

My solution so far:

Automation 1: when imported i create linked field to table B and let the automation update this field with the filenumber (unique) from table A. Thus A&B are linked. after deleting the data in A, with the next import this wil link A back to B no matter it’s position. 

Automation 2: via lookup fields from table A in table B i copy then to the fields in table B. So when the data in Table A is deleted, this wil not delete the data in table B. 

I tried setting up an update record automation but i get jammed the moment i try to make automation that first updates table A, and then copy the data in table B for the linked records in one automation. 

Why not 2 separate automations? this needs to be done daily on a minimum of 2000 records. So i’ll burn through the automation limit in no time. I tried scripting but the 30 sec. Rule makes it impossible to find, update or create records in time. Even if i linked 25 behind each other. 

if someone could help me, encountered something similar i would be mighty grateful 🙏 

thanks in advance!

Best regards Michiel - fellow airtable enthousiast!

 

12 Replies 12

Hmm, what issue did you face when making the automation?  I think I'd set it up as:
1. "Update record" action to link the Table A record to the Table B record
2. "Find record" action to find the record that was just created in Table B
3. "Update record" action to paste the values from the lookup fields into the text fields in Table B

I have a problem with the record ID. I can’t manage to find or link the proper record ID from the second table in the update record automation. 

If you provide a read-only invite link to a duplicated copy of your base I can troubleshoot this for you! 

Hi Adam

Thank you for your response allready. 

Below you can find the read only link. 

https://airtable.com/invite/l?inviteId=invQxYhMKtpVKRIle&inviteToken=beaef4c10e4531d5e2dc5a581912d02...

 

I tried to explain in the base per record what the steps are. 

 

Best regards Michiel

Thanks for the link!  I set up the automation like so:
1. "Update record" action to link the Table A record to the Table B record
2. "Find record" action to find the record that was just created in Table B
3. "Update record" action to paste the values from the lookup fields into the text fields in Table B

Screenshot 2024-07-04 at 8.59.59 PM.png

And it now works as expected:

Screen Recording 2024-07-04 at 9.03.48 PM.gif

Link to base

Hi Adam.

You make it look so easy 🙂

Do you want to share the base with me so i could my way back to how you designed this? 

The base you shared is something differently without any automation in it. 

Best regards, Michiel

 

Ah, sorry!  I've updated the link and it now goes to the correct base

Alexey_Gusev
13 - Mars
13 - Mars

Hi,
I would use scripting. No matter how much records you have, all in-memory operations will be completed in less than a second. The 'bottleneck' is table create/update operations. How much records expected to be in Table B, to be updated/created?
For example, this one completed in 18 seconds 

Alexey_Gusev_0-1720264885704.jpeg

 

const {rec_id,item,command,name}=input.config()
const source=base.getTable('T1')
const writeTo=base.getTable('T2')

//Fetch data 
const url = command
const options = {
     method: "GET",
     headers: {'Cache-control':'no-cache', Authorization: O } };
const fetchdata=await fetch(url, options).then(request => request.json())
const loaded=fetchdata['result']
console.log(fetchdata)

//Process data
const unpack=(el,{date,status,price,...rest}=el)=>({item,name,date,status,price})
const unwrap=obj=>unpack(obj)
const newdata=loaded.map(unwrap)

//Load existing data
const itemdate=r=>r.getCellValue('item')+r.getCellValue('date')
const query=await writeTo.selectRecordsAsync({fields:writeTo.fields})
const filtered=query.records.filter(r=>r.name===name)
const existed=new Map(filtered.map(r=>[itemdate(r),r.id]))

//Functions to compare, update and create
const create=(el)=>({fields:el})
const update=({item,date,...rest})=>({id:existed.get(item+date)||'',fields:rest})
const compare=['name','status','price']
const otherdata=r=>compare.map(c=>r.getCellValueAsString(c)).join(',')
const databyid=id=>otherdata(query.getRecord(id))

const upd=[];const crt=[]

//Distribute - records with new item+data to create, existing - update. Exclude if there is nothing to update
const needSave=({item,date,...rest})=>databyid(existed.get(item+date))!=Object.values(rest).join(',')
newdata.forEach(j=>{ existed.has(j.item+j.date)? upd.push(j) : crt.push(create(j)) }) 
const tosave=upd.filter(needSave).map(update)

//Write to table
while (tosave.length) await writeTo.updateRecordsAsync(tosave.splice(0,50))
while (crt.length) await writeTo.createRecordsAsync(crt.splice(0,50))
output.set('status','Completed succesfully')

 

It's not ready-to-work, I wiped 3 lines with auth and edited some sensitive data , but most of it can be used as example to build yours. 
Note: all my data is text. You have single select field, which can't be just written in the same form as read 
you can pass value through function 
const  writeSngSel_orNameItAsYouWish=({name,...rest})=>({name})  
or use    {name: value.name}  as value to write
or just turn second table field to text and write the result of getCellValueAsString()

Hi Adam

 

Thanks for sharing. Because of this i now understand the find records automation better. But sadly it still triggers record per record. So if i import 2000 records daily i'm through my automations on less then 15 days. 

Still, thanks for the support.