Script - struggling with a parse, search, and link automation

ZTelevate · ‎Jan 04, 2024

I am working to automate a process that feels like I should be able to script but have been struggling. Admittedly I am new to scripting so I may not be understanding how to structure this, I have asked around, tried to get an assist from Chat GPT, and poked and prodded this along but the final outcome has eluded me. Let me break down the intended function and drop the code in. Any help is greatly appreciated.

Intended function:
The automation is initiated when a record enters the "Meetings" view of the "Connections" table, there is a text field called "Attendee Emails" where a formula has collected and concatenated emails using ", " as a delimiter. We are passing that through to the script as an input variable called "attendeeemails". What we need is for the automation to go through the string stored in "attendeeemails" and pull each email out one by one, and check each individual email against the "Email" field in the "Full view" in the "People" table. When any individual email item from "attendeeemails" matches a value in the "Email" field in the "Full view" in the "People" table I would like to retrieve the corresponding record ID, I want the matching record IDs to be used to link the corresponding records in the "Attend/Invite/Include" Linked record field in the Connections table.

Script:

// Import the Airtable API
let connectionsTable = base.getTable("Connections");
let peopleTable = base.getTable("People");

// Get the script input for attendeeemails
let scriptInput = input.config();
let attendeeEmails = scriptInput ? scriptInput.attendeeemails : null;

// Check if the "attendeeemails" input is provided
if (!attendeeEmails) {
  console.error("Script input 'attendeeemails' is missing.");
  return;
}

// Split the attendeeEmails string into an array of individual emails
let emailsArray = attendeeEmails.split(", ");
console.log("EmailsArray output:", emailsArray)

// Initialize an array to store the matching record IDs
let matchingRecordIDs = [];

// Iterate through each email in the array
for (let email of emailsArray) {
  // Query the People table for records with a matching email
  let queryResult = await peopleTable.selectRecordsAsync({
    filterByFormula: `{Email} = "${email}"`
  });

  // If a matching record is found, retrieve and store its ID
  if (queryResult.records.length > 0) {
    matchingRecordIDs.push(queryResult.records[0].id);
  }
}
console.log("Matching Record IDs:", matchingRecordIDs);

// Get records from the "Meetings" view of the "Connections" table
let view = connectionsTable.getView("Meetings");
let records = await view.selectRecordsAsync();

// Iterate through each record in the view
for (let record of records.records) {
  // Update the "Attend/Invite/Include" Linked record field in the "Connections" table
  await connectionsTable.updateRecordAsync(record, {
    "Attend/Invite/Include": matchingRecordIDs.map(id => ({ id }))
  });
}

// Log the result
console.log("Linked records updated successfully!");

Ben_Young1 · ‎Jan 04, 2024

Hey @ZTelevate,

There's a bit of a disconnect between how that script is written and what your requirements appear to be. Specifically, if it were to run correctly, the script you've provided would link every relevant, found person record to every "Connection" record in your "Meetings" view.

Contrasting this, the requirements and context you provided seems to indicate that you want to only want to update the connection record that entered the view.

If your intention is to use the data provided from a single record to update every connection record in that view, then this fast n' dirty script will get you what you're looking for:

const { attendeeEmails } = input.config();

const connectionsTable = base.getTable("Connections");
const peopleTable = base.getTable("People");

const fields = {
    connections: {
        invitees: "Attend/Invite/Include"
    },
    people: {
        email: "Email"
    }
}

const meetingsView = connectionsTable.getView("Meetings");


if (attendeeEmails !== null) {

    let emails = (() => attendeeEmails.toLowerCase().trim().split(","))();

    let peopleRecords = await peopleTable.selectRecordsAsync({fields: Object.values(fields.people )})
        .then(q => q.records.filter(r => r.getCellValue(fields.people.email)));

    let matchedPeopleIds = peopleRecords
        .filter(r => emails.includes(r.getCellValue(fields.people.email)))
        .map(r => r.id);

    let connectionRecords = await meetingsView.selectRecordsAsync({ fields: Object.values(fields.connections) })
        .then(q => q.records);

    let connectionUpdates = connectionRecords.map((r) => {

        let writeValue = (() => {
            let fieldValue = r.getCellValue(fields.connections.invitees);
            return Array.isArray(fieldValue) ? matchedPeopleIds.concat(fieldValue.map(r => r.id)) : matchedPeopleIds;
        })();

        return {
            id: r.id,
            fields: {
                [fields.connections.invitees]: [...new Set(writeValue)].map(id => ({ id: id }))
            }
        }
    });

    while (connectionUpdates.length) {
        await connectionsTable.updateRecordsAsync(connectionUpdates.slice(0, 50));
        connectionUpdates = connectionUpdates.slice(50);
    }
}

This is a bit of a scary script, as going through and updating every connection record in that view is an intensive process that is highly inefficient.

Now, if your intention is to only update the record that triggered your automation, then you'll have a much easier time. In fact, you won't even need to instantiate your Meetings view.
You'll need to add a new input variable for the record ID of the record that triggered the automation.
The script provided below assumes that you've named the input variable recordId.

const { recordId, attendeeEmails } = input.config();

const connectionsTable = base.getTable("Connections");
const peopleTable = base.getTable("People");

const fields = {
    connections: {
        invitees: "Attend/Invite/Include"
    },
    people: {
        email: "Email"
    }
}


if (attendeeEmails !== null) {

    let trigger = await connectionsTable.selectRecordAsync(recordId);
    let emails = (() => attendeeEmails.toLowerCase().trim().split(","))();

    let peopleRecords = await peopleTable.selectRecordsAsync({ fields: Object.values(fields.people)})
        .then(q => q.records.filter(r => r.getCellValue(fields.people.email)));
    
    let matchedPeopleIds = peopleRecords
        .filter(r => emails.includes(r.getCellValue(fields.people.emails)))
        .map(r => r.id);

    let updateValue = ((r, ids) => {

        let existingValue = (() => {
            let fieldValue = r.getCellValue(fields.connections.invitees);
            return Array.isArray(fieldValue) ? fieldValue.map(l => l.id) : [];
        })();

        if (existingValue.length)
            return [...new Set(existingValue.concat(ids))].map(id => ({ id: id }));
        
        if (!existingValue.length)
            return [...new Set(ids)].map(id => ({ id: id }));

    })(trigger, matchedPeopleIds);

    
    await connectionsTable.updateRecordAsync(trigger, {
        [fields.connections.invitees]: updateValue
    })
}

I haven't had a chance to test these scripts yet, but I spent my day writing and implementing a similar pattern.
I'm more than happy to walk you through the specifics of what's going on here or answer any questions you might have while looking over this, as there are a few tricks out of the standard JavaScript library that you'll find in both examples.

See Solution in Thread

Ben_Young1 · ‎Jan 04, 2024