Help

Re: Creating a script to search for a list of values

Solved
Jump to Solution
2045 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Ellen_Gottschli
5 - Automation Enthusiast
5 - Automation Enthusiast

I have a table with 8,000+ records that include email addresses (source table).
I have a list of 200~ e-mail addresses being updated manually (table 2).

I would like to create a grid view in the source table that filters to only include the list of 200 e-mail addresses, without having to manually search for each or create a filter. The end result being that my source table has a column that indicates if the record’s email exists in Table 2, and I could create the grid view based on this.

Anyone know if a script exists for this?

Originally asked the question here: Filter for a long list of values

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

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!

See Solution in Thread

3 Replies 3

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.

Justin_Barrett
18 - Pluto
18 - Pluto

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!

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