Help troubleshooting "vlookup" script

[Marty_McCoy]

I am new to the scripting app. I have a form that people fill out when they come to my office. It feeds into a table called Arrivals (QR Code). I want to create an automation that will run a script when the form is submitted:

1. If the person is a guest, they submit their email.
2. When the record has that guest email, I want to check another table, Data About People, to see if there’s a record there for them.
3. If yes, link the Data About People record to the newly created Arrivals (QR Code) record.
4. If no, create then link.

I’m getting an error with this code:

let mainTable= base.getTable("Arrivals (QR Code)");
let mainTableRecords = await mainTable.selectRecordsAsync();

let lookupTable = base.getTable("Data About People");
let lookupRangeRecords = await lookupTable.selectRecordsAsync();

for (let record of mainTableRecords.records) {
    let lookupValue = record.getCellValue("Guest Email");

    for (let rangeRecord of lookupRangeRecords.records) {
        if (rangeRecord.getCellValue("Email") === lookupValue) {
            let returnValue = rangeRecord.id;

            mainTable.updateRecordAsync(record, {
                "Guest": returnValue
            });
        }
    }
}

The error message:

Error: Field “fldwtE0s49vY9FBb9” cannot accept the provided value.
at main on line 14

Many thanks to anyone who can help get this thing running!

[Kamille_Parks]

Is the {Email } field the primary field in the Data About People table? If so, then you could do this without a script by using the Update Record step to copy the value from the {Guest Email} into the {Guest} field. If that isn’t the case, then ignore this method.

There are a few problems with your script:

• Your script is set up to check every Arrival record for any matches in Data. It sounds like you only want to check the Arrival record that just came in, not every record every time the script runs.
• If there are two matches in the Data table your script will update the Arrival record twice and the second run would override whatever change was made first (i.e. Arrival would be linked to one Data record and not both the matches found).
• {Guest} appears to be the Link to Another Record-type field that connects Arrivals to Data. You are passing a singular ID, but all Link to Another Record-type fields are arrays of objects. returnValue should be [{id: returnValue}]. This is what is causing your error.
• Your script does not yet include the portion to create a new Data record if there were no matches found.

To fix these issues, I recommend the following Automation setup. Instead of querying records within the script, my solution involves the Find Records step. You could do everything within the Script if you so chose.

• Trigger the Automation “when a form is submitted”

• Include a Find Records step that searches the Data About People table for emails which match the email used in the form (click the gear icon to the right of the field to enable dynamic variables):
  

• Include a Run a Script step with these input variables and the following script:
  

// Get the values from the trigger and previous steps in this Automation
let {matchingRecordIds, triggerRecordID, lookupValue} = input.config()

// Get the tables from this base
let mainTable= base.getTable("Arrivals (QR Code)");
let lookupTable = base.getTable("Data About People");

/*
// Un-comment this section if you do not want to include a Find Records step
// Be sure to also remove "matchingRecordIds" from Line 2 if you use this portion of the code
let lookupRangeRecords = await lookupTable.selectRecordsAsync({fields: ["Email"]});

let matchingRecordIds = []

for (let rangeRecord of lookupRangeRecords.records) {
    if (rangeRecord.getCellValue("Email") === lookupValue) {
        matchingRecordIds.push(rangeRecord.id)
    }
}
*/

// If any records in the lookup table have matching values to the trigger record, 
// then update the trigger record to link to those matches
if(matchingRecordIds.length > 0) {
    let mappedValue = matchingRecordIds.map(id => {return {id: id}})
    mainTable.updateRecordAsync(triggerRecordID, {
        "Guest": mappedValue
    })
} else {
    // If there are no matching lookup table records,
    // then create a new record that links to the trigger record and give it the value
    // from the lookup field
    lookupTable.createRecordAsync({
        "Arrivals": [{id: triggerRecordID}],
        "Email": lookupValue
    })
}

[Marty_McCoy]

Thank you @Kamille_Parks! This response helped me find a solution. Appreciate your time and I am now listening to BuiltOnAir.