Creating a script to search for a list of values

I’ve got some thoughts on how to write a script to tackle this. I’ll dive into it tomorrow morning and let you know what I come up with.

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):

1// Update these values based on your configuration
2
3// Master table
4let masterTableName = "Master List";        // The name of your master table
5let masterEmailField = "Name";              // The master table field containing the email address
6let selectedViewName = "Selected";          // The master table view displaying filtered records
7let selectedField = "Selected";             // The checkbox field to mark when indicating a match
8
9// Incoming data table
10let incomingTableName = "Incoming Emails";  // The table containing incoming emails to match
11let incomingEmailField = "Name";            // The field containing the email address
12let incomingNoteField = "Notes";            // Field where notes can be added for non-matching emails
13
14// ---------------------------------------------------------
15// ----------------  DO NOT MODIFY CODE BELOW --------------
16// ---------------------------------------------------------
17
18let masterTable = base.getTable(masterTableName);
19let incomingTable = base.getTable(incomingTableName);
20let interval = 50;
21
22// Ask about clearing previously-matched records
23let clearCheck = await input.buttonsAsync("Remove selection from previously-matched records in the master table?", ["Yes", "No"]);
24if (clearCheck == "Yes") {
25    let matchView = masterTable.getView(selectedViewName);
26    let matchQuery = await matchView.selectRecordsAsync();
27    for (let record of matchQuery.records)
28        masterTable.updateRecordAsync(record, {
29            [selectedField]: false
30        });
31}
32
33// Build the catalog from the master list
34let catalog = {};
35let query = await masterTable.selectRecordsAsync();
36for (let record of query.records) {
37    catalog[record.getCellValueAsString(masterEmailField)] = record.id;
38}
39
40output.text("Master list size: " + Object.keys(catalog).length + " records.");
41
42// Parse the incoming list and build two arrays: one for marking matching records
43// in the master table, the other for marking non-matching records in the incoming table
44let masterUpdates = [];
45let incomingUpdates = [];
46query = await incomingTable.selectRecordsAsync();
47for (let record of query.records) {
48    let email = record.getCellValueAsString(incomingEmailField);
49    if (catalog[email]) 
50        masterUpdates.push({
51            id: catalog[email],
52            fields: {
53                [selectedField]: true
54            }
55        });
56    incomingUpdates.push({
57        id: record.id,
58        fields: {
59            [incomingNoteField]: catalog[email] === undefined ? "No match found" : "Match found"
60        }
61    });
62}
63
64output.text(`${masterUpdates.length} matches found in ${query.records.length} incoming records`);
65output.text("--------------------------------------------");
66
67// Update the master table records
68let start = 0;
69while (start < masterUpdates.length) {
70    output.text(`${start} master records updated...`);
71    await masterTable.updateRecordsAsync(masterUpdates.slice(start, start + interval))
72    start += interval;
73}
74
75output.text(`${masterUpdates.length} master records updated.`);
76output.text("--------------------------------------------");
77
78// Update the incoming table records
79start = 0;
80while (start < incomingUpdates.length) {
81    output.text(`${start} incoming records updated...`);
82    await incomingTable.updateRecordsAsync(incomingUpdates.slice(start, start + interval))
83    start += interval;
84}
85
86output.text(`${incomingUpdates.length} incoming records updated.`);
87

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!

1// Update these values based on your configuration
2
3// Master table
4let masterTableName = "Master List";        // The name of your master table
5let masterEmailField = "Name";              // The master table field containing the email address
6let selectedViewName = "Selected";          // The master table view displaying filtered records
7let selectedField = "Selected";             // The checkbox field to mark when indicating a match
8
9// Incoming data table
10let incomingTableName = "Incoming Emails";  // The table containing incoming emails to match
11let incomingEmailField = "Name";            // The field containing the email address
12let incomingNoteField = "Notes";            // Field where notes can be added for non-matching emails
13
14// ---------------------------------------------------------
15// ----------------  DO NOT MODIFY CODE BELOW --------------
16// ---------------------------------------------------------
17
18let masterTable = base.getTable(masterTableName);
19let incomingTable = base.getTable(incomingTableName);
20let interval = 50;
21
22// Ask about clearing previously-matched records
23let clearCheck = await input.buttonsAsync("Remove selection from previously-matched records in the master table?", ["Yes", "No"]);
24if (clearCheck == "Yes") {
25    let matchView = masterTable.getView(selectedViewName);
26    let matchQuery = await matchView.selectRecordsAsync();
27    for (let record of matchQuery.records)
28        masterTable.updateRecordAsync(record, {
29            [selectedField]: false
30        });
31}
32
33// Build the catalog from the master list
34let catalog = {};
35let query = await masterTable.selectRecordsAsync();
36for (let record of query.records) {
37    catalog[record.getCellValueAsString(masterEmailField)] = record.id;
38}
39
40output.text("Master list size: " + Object.keys(catalog).length + " records.");
41
42// Parse the incoming list and build two arrays: one for marking matching records
43// in the master table, the other for marking non-matching records in the incoming table
44let masterUpdates = [];
45let incomingUpdates = [];
46query = await incomingTable.selectRecordsAsync();
47for (let record of query.records) {
48    let email = record.getCellValueAsString(incomingEmailField);
49    if (catalog[email]) 
50        masterUpdates.push({
51            id: catalog[email],
52            fields: {
53                [selectedField]: true
54            }
55        });
56    incomingUpdates.push({
57        id: record.id,
58        fields: {
59            [incomingNoteField]: catalog[email] === undefined ? "No match found" : "Match found"
60        }
61    });
62}
63
64output.text(`${masterUpdates.length} matches found in ${query.records.length} incoming records`);
65output.text("--------------------------------------------");
66
67// Update the master table records
68let start = 0;
69while (start < masterUpdates.length) {
70    output.text(`${start} master records updated...`);
71    await masterTable.updateRecordsAsync(masterUpdates.slice(start, start + interval))
72    start += interval;
73}
74
75output.text(`${masterUpdates.length} master records updated.`);
76output.text("--------------------------------------------");
77
78// Update the incoming table records
79start = 0;
80while (start < incomingUpdates.length) {
81    output.text(`${start} incoming records updated...`);
82    await incomingTable.updateRecordsAsync(incomingUpdates.slice(start, start + interval))
83    start += interval;
84}
85
86output.text(`${incomingUpdates.length} incoming records updated.`);
87

Thank you SO MUCH this worked perfectly. I really appreciate the help!