Error with script to link record to another table in automation

Hi!
I am using an automation to run a script when a record is created in SIGNUP table. The script should link the records (which are people) between two tables—SIGNUP and CUSTOMERS. The primary field in both tables is the person’s name but it is easiest to match the records via the email addresses. I am getting an error saying that the link field cannot accept the value. I am using the input variable. See photo.

let recordId = input.config().recordID
let trainingTable = base.getTable(“SIGNUP”)
let recordQuery = await trainingTable.selectRecordsAsync()
let recordToUpdate = recordQuery.records.find(record => record.id == recordId)
let trainingEmail = recordToUpdate.getCellValue(“SIGNUP EMAIL”)
let applicationsQuery = await base.getTable(“CUSTOMERS”).selectRecordsAsync()
let applicationsToLink = applicationsQuery.records.map(applicationRecord => {
if (applicationRecord.getCellValue(“CUSTOMERS EMAIL”) === trainingEmail) {
return { id: applicationRecord.id }
}
})
await trainingTable.updateRecordAsync(recordId, {
“SIGNUP LINK FIELD”: [{id: applicationsToLink.id}]
})

Your script has a few issues that I can see. Most are related to your problem, while this first one is just a minor optimization.

let recordToUpdate = recordQuery.records.find(record => record.id == recordId)

You don’t need to use the find() method on the record list to get a record if you know its ID. You can use the getRecord() method on the returned query, like this:

let recordToUpdate = recordQuery.getRecord(recordId)

The main problem occurs when trying to narrow down the list of applications to link:

let applicationsToLink = applicationsQuery.records.map(applicationRecord => {
    if (applicationRecord.getCellValue("CUSTOMERS EMAIL") === trainingEmail) {
        return { id: applicationRecord.id }
    }
})

The map() method operates on all items in an array, building a new array as a result. Even though you’ve got an if() statement that supposedly only returns data related to the matching records, the function still returns something (either null or undefined, I’m not completely sure which) for all the rest. In short, your array of applications to link contains a mix of objects with id properties, and null/undefined items as well.

The proper way to narrow down the contents of an array is with the filter() method, which would look like this:

let applicationsToLink = applicationsQuery.records.filter(applicationRecord => {
        return applicationRecord.getCellValue("CUSTOMERS EMAIL") === trainingEmail
    }
)

The function used by the filter() method returns true or false, which will cause a given array item to be included in the new array if the test performed on it returns true, and omitted if its test returns false.

The final issue is with the syntax of the update:

await trainingTable.updateRecordAsync(recordId, {
    "SIGNUP LINK FIELD": [{id: applicationsToLink.id}]
})

Because applicationsToLink is an array, it has no id property. That syntax also won’t automatically insert all of the id property values of the array items. Even if it did, it would need to be an array of objects each with a separate id property tied to a record ID, not just an array of IDs.

Thankfully, because applicationsToLink is an array of records, those can be fed directly into that link field without any modification or property extraction:

await trainingTable.updateRecordAsync(recordId, {
    "SIGNUP LINK FIELD": applicationsToLink
})

With all of those changes applied, this is the full updated script:

let recordId = input.config().recordID
let trainingTable = base.getTable("SIGNUP")
let recordQuery = await trainingTable.selectRecordsAsync()
let recordToUpdate = recordQuery.getRecord(recordId)
let trainingEmail = recordToUpdate.getCellValue("SIGNUP EMAIL")
let applicationsQuery = await base.getTable("CUSTOMERS").selectRecordsAsync()
let applicationsToLink = applicationsQuery.records.filter(applicationRecord => {
        return applicationRecord.getCellValue("CUSTOMERS EMAIL") === trainingEmail
    }
)
await trainingTable.updateRecordAsync(recordId, {
    "SIGNUP LINK FIELD": applicationsToLink
})
1 Like

Thank you so much, Justin!! I am printing this and going to study it so I understand, but IT WORKED!! I cannot thank you enough!!