Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 09, 2022 03:36 PM
I’ve seen a few posts about duplicating records and their linked records with scripting, but none showing an example. Does anyone have some example code that will duplicate a records and it’s linked records, and update the association of the linked records to the new record?
Use case: I’ve build a quote with multiple line items (linked records). I want to create a variation of the quote by creating a duplicate of the data set, quote and line items linked to the quote.
Mar 17, 2022 02:34 PM
I’ve been using Airtable heavily for about 2.5 year and have go about as far as I can with it’s core web interface. So, I’m starting to get into the scripting app. I’ve done programming in VBA for excel, solidworks, and autocad, but Javascript is new for me. I haven’t had much luck finding code snippets that meet my need. I know what I want to do, i don’t know the code.
I’m going to document my steps of building this script app with the hope that the script app experts will help me fill in the gaps.
The scenario: I have a parent table with several fields that are linked to child tables. If I a duplicate a parent, then there are child records in 4 different tables that also need to be duplicated and linked to the “new” parent record. All other fields of the “new” parent will remain the same as the “old” parent.
The Plan:
What I have so far:
// THIS APP WILL CREATE A DUPLICATE OF A RECORDS AND IT'S CHILDREN
let jobtbl = base.getTable("JOBS"); //parent table
let witbl = base.getTable("WORK ITEMS"); //child table1
let cstbl = base.getTable("CAB SPECS"); //child table2
let fstbl = base.getTable("FINISH SPEC"); //child table3
let qstbl = base.getTable("QUOTE SPEC"); //child table4
let qbtbl = base.getTable("QUOTE BOM"); //grandchild table1 for use later
// SELECT PARENT RECORD
let jobrecord = await input.recordAsync('Select a job to duplicate', jobtbl);
let jobnum = jobrecord.name;
output.inspect(jobnum & " " & jobrecord.getCellValue("JOB NAME"));
// output.inspect(jobrecord.getCellValue("WORK ITEMS"));
let jobid = jobrecord.id // GETS JOB RECORD ID
output.inspect(jobid)
let query = await witbl.selectRecordsAsync();
output.inspect(query.records)
let workitems = query.records.filter(workitem => {return workitem.getCellValue('JOB').includes(jobrecord.name)})
I’m trying to get an array of the work items linked to a job by filtering the query for records that have job number that matches what I selected.
I get this error:
Error
TypeError: workitem.getCellValue(...) is null
at main/workitems< on line 21
at main on line 21
The job I selected has 12 work items linked to it.
I don’t full understand what this line is doing.
let workitems = query.records.filter(workitem => {return workitem.getCellValue('JOB').includes(jobrecord.name)})
I got this from Using 'filter' | Airscript. I’ve tried jobrecord.id
and jobrecord.name
and still get null
.
Mar 17, 2022 02:48 PM
Found this post that might be key to get past the filter issue:
Mar 17, 2022 03:29 PM
This successfully filtered the query down to a list of items only linked to the job. This works in my case because have an ID field (key) in my child tables that use the use the linked job number as a prefix. The right way to do this would probably be to use the record id, but I don’t know how to do that yet.
// THIS APP WILL CREATE A DUPLICATE OF A RECORDS AND IT'S CHILDREN
let jobtbl = base.getTable("JOBS"); //parent table
let witbl = base.getTable("WORK ITEMS"); //child table1
let cstbl = base.getTable("CAB SPECS"); //child table2
let fstbl = base.getTable("FINISH SPEC"); //child table3
let qstbl = base.getTable("QUOTE SPEC"); //child table4
let qbtbl = base.getTable("QUOTE BOM"); //grandchild table1 for use later
// SELECT PARENT RECORD
let jobrecord = await input.recordAsync('Select a job to duplicate', jobtbl);
let jobnum = jobrecord.name;
output.inspect(jobrecord.getCellValue("JOB NAME"));
let query = await witbl.selectRecordsAsync();
// output.inspect(query.records[0].name)
// filters query to workitem names that include the job number
let workitems = query.records.filter(record => {
return record.name.includes(jobnum);
});
Mar 17, 2022 04:30 PM
Here’s what works for filtering down the queries from the 4 different table:
// THIS APP WILL CREATE A DUPLICATE OF A RECORDS AND IT'S CHILDREN
let jobtbl = base.getTable("JOBS"); //parent table
let witbl = base.getTable("WORK ITEMS"); //child table1
let cstbl = base.getTable("CAB SPECS"); //child table2
let fstbl = base.getTable("FINISH SPEC"); //child table3
let qstbl = base.getTable("QUOTE SPEC"); //child table4
let qbtbl = base.getTable("QUOTE BOM"); //grandchild table1 for use later
// SELECT PARENT RECORD
let jobrecord = await input.recordAsync('Select a job to duplicate', jobtbl);
let jobnum = jobrecord.name;
let jobserial = jobnum.substr(3,4)
jobserial = jobserial.replace("0","")
output.inspect(jobserial)
output.inspect(jobrecord.getCellValue("JOB NAME"));
// work items
let query = await witbl.selectRecordsAsync();
// filters query to workitem names that include the job number
let workitems = query.records.filter(record => {
return record.name.includes(jobnum);
});
output.inspect(workitems)
//
// cab specs
query = await cstbl.selectRecordsAsync();
// filters query to cab specs names that include the job number
let cabspecs = query.records.filter(record => {
return record.name.includes(jobserial);
});
output.inspect(cabspecs)
//
// finish specs
query = await fstbl.selectRecordsAsync();
// filters query to finish specs names that include the job number
let finspecs = query.records.filter(record => {
return record.name.includes(jobserial);
});
output.inspect(finspecs)
//
// quote specs
query = await qstbl.selectRecordsAsync();
// filters query to quote specs names that include the job number
let quotespecs = query.records.filter(record => {
return record.name.includes(jobserial);
});
output.inspect(quotespecs)
//
I’m sure there is a much more elegant and concise way to accomplish this with loop, but I think this gets me what I need for the next step. I’m open to any recommendations for refactoring this.