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):
let masterTableName = "Master List";
let masterEmailField = "Name";
let selectedViewName = "Selected";
let selectedField = "Selected";
let incomingTableName = "Incoming Emails";
let incomingEmailField = "Name";
let incomingNoteField = "Notes";
let masterTable = base.getTable(masterTableName);
let incomingTable = base.getTable(incomingTableName);
let interval = 50;
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
});
}
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.");
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("--------------------------------------------");
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("--------------------------------------------");
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!