Skip to main content

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

  • September 21, 2023
  • 1 reply
  • 9 views
M
Forum|alt.badge.img+4

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

M
Forum|alt.badge.img+4
  • mattysheets
  • Author
  • New Participant
  • September 21, 2023

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

 

 
Terms & ConditionsAccessibility statement