Duplicating records from one table to another using a script

[KVachon]

Hey there!

I have an automation that is designed to take specific records from two separate tables and duplicate them into a single table. I used the repeat for each duplicate one of the tables, but because I can't use two I'm trying to do the same using a script but failing to add many of the record details.

let inputs = input.config();
let destinationTable = base.getTable("Executive Summaries copy");
let sourceTable = base.getTable("5-Year Automation");
let importQuery = await sourceTable.selectRecordsAsync();
let fivYearInputList = inputs.fiveYearInputList;

for (let record of importQuery.records) {
    let strategicObjective = record.getCellValue('Strategic Objective')
    let updates = record.getCellValue('Monthly Update')
    let forcedOrder = record.getCellValue('Forced Order')
    let divisions = record.getCellValueAsString('Executive Sponsor')
    let whatIsBeingImplemented = record.getCellValue('What is being implemented?')
    let currentHealth = record.getCellValue('Current Health')
    let targetDeploymentDate = record.getCellValue('Target Deployment Date')
    let risksAndIssues = record.getCellValue('Risks & Issues')
    let whatAreTheBenefits = record.getCellValue('What are the benefits?')
    let whyAreWeMakingThisChange = record.getCellValue('Why are we making this change?')
    let focusArea = record.getCellValue("Focus Area")
    let taskOwners = record.getCellValueAsString('Owner')
    await destinationTable.createRecordAsync({
        "Internal Initiative": strategicObjective,
        "Strategic Objective": focusArea,
        "Divisions": divisions,
        "What is being implemented?": whatIsBeingImplemented,
        "Current Health": currentHealth,
        "Target deployment date": targetDeploymentDate,
        "Updates": updates,
        "Risks and Issues": risksAndIssues,
        "What are the benefits?": whatAreTheBenefits,
        "Why are we making this change?": whyAreWeMakingThisChange,
        "Task Owners": taskOwners,
        "Label - Benefits": "Benefits",
        "Label - Purpose of Change": "Purpose of Change"
    })
}

The reason for a single automation is that we want to initiate it using a form as needed.

Thanks for any help!

[kuovonne]

Can you share screen shots of the problem you are having?

Are any of the fields you are trying to copy over single-select or multiple-select fields? For any of the fields, are you trying to copy from one field type to another? If these are your issues, you need to make sure to match the write format for the target table, which is different from the read value from the source table. The scripting documentation shows the read and write formats for each field type.

Also note that if you have close to 30 records in your "5-Year Automation" table, you may have time limit issues with the script as written. 

[KVachon]

Hey there!

It looks like you nailed it @kuovonne. There are more than just strings being referenced and written to. I took a look at the cell values & field options and I'm not entirely sure how to get the correct information.

For example, "Strategic Objective" is a single select field in both tables. What would the read version be to get that cell's value for each record? I saw { id: string, name: string, color?: string,} but I'm not sure how to work that into the let strategicObjective = record.getCellValue('Strategic Objective') or how to write it to the new table. 

[kuovonne · Accepted solution]

For a single select field, the read format is an object that includes both the choice name and an internal choice ID. Even though different single select fields may have the same choice name, they will have different choice IDs. So when writing, specify only the choice name. 

There are multiple options, but here is one.

First read the cell value as a string instead of an object.

let strategicObjective = record.getCellValueAsString('Strategic Objective')

Then write the value as an object with only a name.

"internal initiative": {name: strategic objective},
 

Note that if the single select is blank or has a choice name that does not exist in the other table, you will need different code to handle those situations.

Apologies for not formatting the code samples properly. I am on a phone and this platform does not let me format code from my phone. 

[KVachon]

Thank you very much @kuovonne !

That was exactly what I needed.