Creating a script to search for a list of values

This took longer than I thought, but I wanted to make it easy to set up and use, so that add a bit of time. Anyway, here’s the code (make sure to scroll to see it all):

// Update these values based on your configuration



// Master table

let masterTableName = "Master List";        // The name of your master table

let masterEmailField = "Name";              // The master table field containing the email address

let selectedViewName = "Selected";          // The master table view displaying filtered records

let selectedField = "Selected";             // The checkbox field to mark when indicating a match



// Incoming data table

let incomingTableName = "Incoming Emails";  // The table containing incoming emails to match

let incomingEmailField = "Name";            // The field containing the email address

let incomingNoteField = "Notes";            // Field where notes can be added for non-matching emails



// ---------------------------------------------------------

// ----------------  DO NOT MODIFY CODE BELOW --------------

// ---------------------------------------------------------



let masterTable = base.getTable(masterTableName);

let incomingTable = base.getTable(incomingTableName);

let interval = 50;



// Ask about clearing previously-matched records

let clearCheck = await input.buttonsAsync("Remove selection from previously-matched records in the master table?", ["Yes", "No"]);

if (clearCheck == "Yes") {

    let matchView = masterTable.getView(selectedViewName);

    let matchQuery = await matchView.selectRecordsAsync();

    for (let record of matchQuery.records)

        masterTable.updateRecordAsync(record, {

            [selectedField]: false

        });

}



// Build the catalog from the master list

let catalog = {};

let query = await masterTable.selectRecordsAsync();

for (let record of query.records) {

    catalog[record.getCellValueAsString(masterEmailField)] = record.id;

}



output.text("Master list size: " + Object.keys(catalog).length + " records.");



// Parse the incoming list and build two arrays: one for marking matching records

// in the master table, the other for marking non-matching records in the incoming table

let masterUpdates = [];

let incomingUpdates = [];

query = await incomingTable.selectRecordsAsync();

for (let record of query.records) {

    let email = record.getCellValueAsString(incomingEmailField);

    if (catalog[email]) 

        masterUpdates.push({

            id: catalog[email],

            fields: {

                [selectedField]: true

            }

        });

    incomingUpdates.push({

        id: record.id,

        fields: {

            [incomingNoteField]: catalog[email] === undefined ? "No match found" : "Match found"

        }

    });

}



output.text(`${masterUpdates.length} matches found in ${query.records.length} incoming records`);

output.text("--------------------------------------------");



// Update the master table records

let start = 0;

while (start < masterUpdates.length) {

    output.text(`${start} master records updated...`);

    await masterTable.updateRecordsAsync(masterUpdates.slice(start, start + interval))

    start += interval;

}



output.text(`${masterUpdates.length} master records updated.`);

output.text("--------------------------------------------");



// Update the incoming table records

start = 0;

while (start < incomingUpdates.length) {

    output.text(`${start} incoming records updated...`);

    await incomingTable.updateRecordsAsync(incomingUpdates.slice(start, start + interval))

    start += interval;

}



output.text(`${incomingUpdates.length} incoming records updated.`);

At the top are several variables for you to tweak based on the setup of your base. The “master table” is your source table of 8000+ records, and the “incoming table” is where you’re bringing in the 200-ish emails that you want to isolate in the source table.

If you don’t already have a note field (single line / long text, it doesn’t matter) in your incoming table, you’ll need to add one. Part of what the script does is give you feedback in those incoming records re: whether or not a match was found for each incoming email address.

To mark the records to that you want to isolate in the source table, I used a checkbox field. If you don’t yet have such a field, add it now, and make a view that only shows records where that box is checked, putting their names into their respective variables at the top of the script. The script will check that box on all records that have a matching email in the incoming table. There’s also a prompt at the start of the script’s execution that asks if you want to clear previously-checked items before checking any new ones.

I ran a test with a source table containing 500 emails, and an incoming table of 124 emails randomly chosen from those 500. Because of Airtable’s record update limitations, records have to be updated in groups of no more than 50, but it still only takes a few seconds to get through everything.

If you have any questions about this, let me know!