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.
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.");
}
