Search table 2 to see if there are records that match table 1. If so update a field

[mattysheets]

Here it goes...

I have an email campaign table that I import email lists to from various led gen.

I have a client intake table that I use to track when a client submits a proposal request and eventual becomes a client.

Email is the unique client identifier across all tables.

I need to search my client intake table to see if any of the 'email' field matches an 'email' in my campaign table.

If it does I need to grab the 'status' from the client intake table and update the 'status' on my email campaign table.

The reason I do not want to link the tables is I have a ton of records that do not need to be brought over from one table to the other. I just need to know if a client engages or status changes after I email them.

[mattysheets]

I tried this script but keep getting error:
"Error: y: Field "fldAJQOmMzmgqtot4" cannot accept the provided value."

let table1Name = 'Email Campaigns';

let table2Name = 'Prospective Client Intake';

let field1Name = 'Status';

let field2Name = 'Status - Script';

let field3Name = 'Email';

let field4Name = 'Email';

let table1 = base.getTable(table1Name);

let table2 = base.getTable(table2Name);

try {

    let table1Query = await table1.selectRecordsAsync();

    let table2Query = await table2.selectRecordsAsync();

    // Create a mapping of Email to Status from table2 (Prospective Client Intake)

    let emailToStatusMap = {};

    for (let record of table2Query.records) {

        let email = record.getCellValueAsString(field3Name);

        let status = record.getCellValueAsString(field1Name);

        if (email && status) {

            emailToStatusMap[email] = status;

        }

    }

    // Update records in table1 (Email Campaigns) based on matching emails

    let updates = [];

    for (let record of table1Query.records) {

        let email = record.getCellValueAsString(field4Name);

        if (email && emailToStatusMap.hasOwnProperty(email)) {

            let status = emailToStatusMap[email];

            let update = {

                id: record.id,

                fields: {

                    [field2Name]: status // Update 'Status - Script' field

                }

            };

            updates.push(update);

            // If the batch size is reached, update the records and reset the updates array

            if (updates.length >= 50) {

                await table1.updateRecordsAsync(updates);

                updates = [];

            }

        }

    }

    // Update any remaining records in the updates array

    if (updates.length > 0) {

        await table1.updateRecordsAsync(updates);

        console.log(`Updated ${updates.length} records in ${table1Name}.`);

    } else {

        console.log('No records to update.');

    }

} catch (error) {

    console.error(`Error: ${error}`);

}