Re: Copy records from one table to another and updating existing ones.
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
And it now works as expected:
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
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()
