Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

Topic Labels: Automations
1142 1
cancel
Showing results for 
Search instead for 
Did you mean: 
mattysheets
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

1 Reply 1
mattysheets
5 - Automation Enthusiast
5 - Automation Enthusiast

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}`);
}