Skip to main content
Solved

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

Z
Forum|alt.badge.img+3

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!");

 

 

 

 

Best answer by Ben_Young1

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.

View original
Did this topic help you find an answer to your question?

Ben_Young1
Forum|alt.badge.img+20

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.

Z
1 person likes this
  • Z
    ZTelevate
Z
Forum|alt.badge.img+3
  • ZTelevate
  • New Participant
  • January 8, 2024
Ben_Young1 wrote:

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.


THANK YOU! Such a simple but important catch. I used your code with some added error flags and a few other console outputs so I could check what was going on and eventually got it all working! This will make my life a lot easier, so thank you again!

Final script:

const { recordId, attendeeemails } = input.config(); console.log("Script input - recordId:", recordId); console.log("Script input - attendeeEmails:", attendeeemails); const connectionsTable = base.getTable("Connections"); const peopleTable = base.getTable("People"); const fields = { connections: { invitees: "Attend/Invite/Include" }, people: { email: "Email" } } if (attendeeemails !== null && attendeeemails !== undefined) { 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.email))) .map(r => r.id); if (matchedPeopleIds.length === 0) { console.error("No matching people records found."); } console.log("Script - Split Emails:", emails); 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 }); console.log("Linked records updated successfully!"); } else { console.error("Script stopped: attendeeEmails is null or undefined."); }

 

Reply


Most helpful members this week

Terms & Conditions