Copy set of records, then copy links

I’m trying to create a script that would copy a set tasks that I have identified as my “template” set for use in multi-project tracking. My script would create all the new tasks for a new projects based on the template. The template tasks have a linked field for predecessor’s to use in the Gantt block (my goal is project schedule tracking).

I figured out how to copy all the template tasks and prepend those task names with a mnemonic so they are unique. In doing that, I realized I can’t create the links in the predecessor field at the same time because the records are created serially and as such many of the links for a record don’t yet exist. I want to keep the same relative linking between records to allow for a new Gantt block to show the new project schedule with appropriate links. From there I can edit the projects dates and add/remove tasks specific to that project.

Here is the code to create the records.

for(let record of templateViewRecords.records) {
await tasks.createRecordAsync({
    "Name": projectMnemonic + ' ' + record.getCellValue("Name"),
    "Start Date": record.getCellValue("Start Date"),
    "End Date": record.getCellValue("End Date"),
    "Category": record.getCellValue("Category"),
    "Phase": record.getCellValue("Phase"),
    "Notes": record.getCellValue("Notes"),
});

};

I can’t quite figure out how to create the relative links. My thought was to cycle through the template records again and for each one find the new record with the appended mnemonic and then update the predecessor field with links to the new records, also with the prepended mnemonic.

Any help would be appreciated.

Hi @Jason_Smith1, welcome to the community!

As you mentioned, you’ll need the ID of the record you’re trying to link in order to establish the linked relationship, but we don’t know that ID until after the record is created!

Your thought is spot on - you can iterate through your template records to populate a map of the links to create, keeping track of each template record name (which you can later use to find the “new version” of that template record). Example:

const linkedRecordNamesByRecordName = {
   "Task 1": "Task 2",
   "Task 2": "Task 3",
}

Then, after creating the new records (without links), you can iterate through that map of links to create. You’ll need to lookup the ID of the new records to link based on this data. createRecordsAsync returns a list of new record IDs, but it might just be easier to fetch the records from that table again and identify the record you’re looking for by doing something along the lines of

recordIdToLink = newRecords.find(rec => {
    return rec.getCellValue("Name") === `${prefix}_${templateRecordName}`;
}).id

Then use table.updateRecordsAsync to establish the links.

If you’re trying to set up multiple linked records in a single field, or linked records in multiple fields, you may need to tweak the format of the linkedRecordNamesByRecordName map you create accordingly.

Hope this gets you going in the right direction!

2 Likes

I’ve gone in a few different directions trying to make this all work. I couldn’t get the find() method to work on a set of records (records queried from a view with selectRecordsAsync()). I came up with a less pretty workaround, but now I’m stuck on creating a list of the new records.

When a record is created using table.createRecordAsync() is there a way to return the record from this function, not just the ID?

@Jason_Smith1

Would you mind sharing the snippet that you were trying to get working for this? Something like the following should work, assuming theres a record that meets the criteria used in find:

const myTable = base.getTable('My Table');
const queryResult = await myTable.selectRecordsAsync();
const specificRecord = queryResult.records.find(rec => {
    return rec.getCellValue('My Field') === 'EXPECTED VALUE');
});

The current API only supports returning the ID, but you can fetch the newly created record with another selectRecordsAsync call after creation (just make sure you await the record creation call). You can also use createRecordsAsync (note the plural) to batch-create records (rate-limited at 50 per batch)

This has been something of a side project for me, so I haven’t been working on it much. Yesterday however I finally got everything working in my script to completely copy my template and recreate all of the relative links.

The issue I had with find() was a misplaced semicolon after “newRecordName”. Just took a few tries to find the right syntax.
let newRecordToLink = allTasks.records.find(rec => {
return rec.getCellValue(“Name”) === newReocrdName
});

As for returning records with selecRecordsAsync, I couldn’t figure out a way to return a specific record from it’s ID without first returning all records and then search through the list for the ID. I don’t know if there is a quicker, more efficient way to do that, but I found something that works.