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

[Michiel]

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!

[Michiel_Becue]

Hi Alexey, your right i think. And we encountered the bottleneck in the update and comparisson as well. It's expected to be between 1000 and 5000. But i only manage to let our script take 50 records per time, create and compare and then delete the records in Table A (so the next import there won't be double values in table A) (It's not only 1 column but approx. 60 columns per record)

But i think i found a work around. 

I think i'll run the first automation to link records Table A to Table B. 

Another script that, when triggered, will copy the notes from the lookup field in Table B to a "writeable" field. I noticed that when the value remains the same before and after the update my watch fields are not triggered. 

Third script to batch delete all records in table A. 

Maybe this will be much quicker then to do a real search, check and update. 

Any thoughts on this approach? I'm just thinking about what kind of triggers i could use.

Best regards, Michiel

[TheTimeSavingCo]

Nice!  Now that you've managed to combine the automation into a single one, you could attempt to reduce the runs by setting it up like this:

The "Find Records" action can only do up to 1000 records though, and you're doing min 2000.  When facing this issue I end up creating a "Task" table where each record represents 8000 records as the Repeating Group function handles up to 8000 items.  I'd use a script action that grabs all the relevant records and creates one record per 8000 records found, and have another automation that would trigger once per record created in that table, resulting in 2 automation runs for 8k records, 3 automation runs for 16k records etc

Uses slightly more automation runs than fully scripting it, but I like it because I feel like it's easier to manage; all very subjective though!

[Alexey_Gusev]

That approach looks reasonable. Instead of triggered by record, you can schedule automation to run at a scheduled time and take 1000 records with each run.
You should understand that data visible as lookup is just a reflection of data from other table. So, after you delete records from table A, their lookup in table B became empty.