Duplicate record and specific linked records

[GREG_Witkamp1]

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.

[GREG_Witkamp1]

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:

• get the “old” parent record id
• get array/query of existing child records from table1 linked to “old” parent
• duplicate parent record and get new record id
• (function) duplicate each child records, and replace the “old” parent id with the “new” parent id
• reuse function for each subsequence child table

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.

[GREG_Witkamp1]

Found this post that might be key to get past the filter issue:

Completely stuck on filtering through and array Scripting app

Hey @Kim_Trager1: The return type of a linked record field is an array of linked record objects, which look like {id: string, name: string}. So in order to compare a linked record against your filter, you need to index into the array. You could try something along the following lines: let reportTable = base.getTable('report'); let namesField = tableReport.getField('names'); let contactsTable = base.getTable('Contacts'); let staffView = tableContact.getView('Staff'); let personRecord = await …

[GREG_Witkamp1]

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);
    });

[GREG_Witkamp1]

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.