Nov 22, 2023 05:36 PM
Hello community! I would like support to create a custom script that finds a specific:
1. Table
2. Record within that table
3. Cell within that record
And it removes an email (inked record) from that cell.
The 'email to be removed 'and 'RecordId' are updated automatically using input variables in the scripting code using input.config().
I would really appreciate any help with this. Thanks in advance!
I tried using ChatGPT to create the script but I kept getting errors. Here's an example of the script it provided:
// Airtable script to remove an email from a list of emails within a specific cell
// Replace 'YourTable' with your actual table name
const table = base.getTable('YourTable');
// Replace 'EmailsField' with your actual field name containing the list of emails
const emailsField = table.getField('EmailsField');
// Replace 'LinkedEmailsField' with your actual linked records field name for email addresses
const linkedEmailsField = table.getField('LinkedEmailsField');
// Extract the input variables from Automations
const { emailToRemove, recordId } = input.config();
try {
// Fetch the specific record
const records = await table.selectRecordsAsync({
filterByFormula: `RECORD_ID() = '${recordId}'`,
});
// Ensure a record is found
if (records.length === 0) {
throw new Error(`Record with ID ${recordId} not found.`);
}
// Get the first record from the fetched records
const specificRecord = records[0];
// Get the current list of linked emails in the specified cell
const currentLinkedEmails = specificRecord.getCellValue(linkedEmailsField);
// Ensure the field has a valid value and is an array
if (currentLinkedEmails && Array.isArray(currentLinkedEmails)) {
// Remove the specified email from the list
const updatedLinkedEmails = currentLinkedEmails.filter(email => email !== emailToRemove);
// Update the record with the modified list of linked emails
await table.updateRecordAsync(specificRecord, {
[linkedEmailsField.name]: updatedLinkedEmails,
});
console.log(`Email ${emailToRemove} removed successfully from the specified cell in record ${recordId}.`);
} else {
throw new Error(`Error accessing or updating the linked emails field for record ${recordId}.`);
}
} catch (error) {
console.error(error.message);
}
Nov 22, 2023 08:45 PM
Hello @MovementPlus
Please share proper screenshots of all the steps you do on your automation like errors and your script.
which helps more to find issues.
Nov 23, 2023 01:56 PM
Hey @dilipborad,
Here are the columns and automation I'm working with:
Step 1: When a record is updated
- When an email lands in the "Activity Complete" column this triggers the automation and is working successfully.
Step 2: Run a script
- I've tried a range of different scripts that ChatGPT created but none have worked. In the table below is the most recent.
- The intention is for it to take the list of emails in the "Email List as String" column, remove the email that is shown in the "Activity Complete" column and paste the updated list of emails in the "Mark Activity As Complete" column, which is a linked records field.
// Airtable script to remove an email from a comma-separated string of emails in one cell // and update a different cell with the modified list of emails // Replace 'YourTable' with your actual table name const tableName = 'Course Activities'; // Replace 'EmailsField' with your actual field name containing the comma-separated string of emails const emailsFieldName = 'Email List as String'; // Replace 'UpdatedEmailsField' with your actual field name where you want to store the updated list of emails const updatedEmailsFieldName = 'Mark Activity As Complete / Activity Complete Button'; // Extract the input variables from Automations const { emailToRemove, recordId } = input.config(); try { // Get the specified table const table = base.getTable(tableName); // Get the specified record const records = await table.selectRecordsAsync({ filterByFormula: `RECORD_ID() = '${recordId}'`, }); // Ensure a record is found if (records.length === 0) { throw new Error(`Record with ID ${recordId} not found.`); } // Log the found records for debugging console.log('Found records:', records); // Get the first record from the fetched records const record = records[0]; // Check if the record is defined if (!record) { throw new Error(`Error fetching record with ID ${recordId}.`); } // Log the selected record for debugging console.log('Selected record:', record); // Get the current list of email addresses as a string in the specified cell const currentEmailsAsString = record.getCellValueAsString(emailsFieldName); // Log the current value for debugging console.log('Current emails as string:', currentEmailsAsString); // Ensure the field has a valid value if (currentEmailsAsString !== null && typeof currentEmailsAsString === 'string') { // Convert the string of email addresses to an array const currentEmailsArray = currentEmailsAsString.split(',').map(email => email.trim()); // Remove the specified email from the array const updatedEmailsArray = currentEmailsArray.filter(email => email !== emailToRemove); // Convert the updated array of email addresses back to a string const updatedEmailsAsString = updatedEmailsArray.join(', '); // Update the record with the modified list of email addresses in the different cell await table.updateRecordAsync(record, { [updatedEmailsFieldName]: updatedEmailsAsString, }); console.log(`Email ${emailToRemove} removed successfully from the specified cell in record ${recordId}.`); } else { throw new Error(`Error accessing or updating the email addresses field for record ${recordId}.`); } } catch (error) { console.error(error.message); } |
- These are the errors that appeared in this most recent script test:
Let me know if you need any more information.
Thanks so much for taking the time to help out!
Nov 24, 2023 03:47 AM
I understand what you're trying to do.
As per Step 2: Run a script you're doing all the basic stuff like get table instance(object), setup fields, and the getting value of it. Then you convert comma comma-separated string to an array, then remove a specific email from the array and update the other string to the original field. Just confirm that is it right?
This does not work because while you need to update any linked(relational) field using script you need to pass their record ID rather than its primary field value.
For reference, you can read more here https://airtable.com/developers/scripting/api/table#update-record-async
I hope you can understand something from it, If you don't understand anything let me know I'll try to create a video for you by today or tomorrow.
Nov 25, 2023 01:29 PM
Hey @dilipborad, thanks for your reply!
I'm not fully sure what you mean?
The linked record field in the attached image above displays our member emails and links to our member records in our Member table. So I'm guessing that means that I have chosen to use emails as the primary field.
Are you saying that in order for the script to work, I would need to update the primary field in our Member table so that it displays the record ID?
Dec 06, 2023 07:10 PM
Hey @dilipborad, just posting this incase you missed my last message. Would greatly appreciate your guidance in relation to my last post.
Dec 06, 2023 09:58 PM
Hello @MovementPlus
I've created another script for you. Just try this directly and run it. If you find errors then just show me screen shot of it.
// Replace 'YourTable' with your actual table name
const tableName = 'Course Activities';
// Replace 'UpdatedEmailsField' with your actual field name where you want to store the updated list of emails
const updatedEmailsFieldName = 'Mark Activity As Complete / Activity Complete Button';
// Extract the input variables from Automations
const { emailToRemove, recordId } = input.config();
try {
// Get the specified table
const table = base.getTable(tableName);
// Get the specified record
const record = await table.selectRecordAsync(recordId);
// Get the current list of email addresses as a string in the specified cell
const currentEmails = record.getCellValue(updatedEmailsFieldName);
/* let currentEmails = [{ name: "u1@user.com", id: "recId1" },
{ name: "u2@user.com", id: "recId2" },
{ name: "u3@user.com", id: "recId3" },
{name: "u4@user.com", id:"recId4"}]
*/
// Log the current value for debugging
console.log('Current emails:', currentEmails);
// Ensure the field has a valid value
if (currentEmails !== null) {
// Remove the specified email from the array
const updatedEmailsArray = currentEmails.filter(obj => obj.name !== emailToRemove);
console.log(updatedEmailsArray);
// Update the record with the modified list of email addresses in the different cell
await table.updateRecordAsync(recordId, {
[updatedEmailsFieldName]: updatedEmailsArray,
});
console.log(`Email ${emailToRemove} removed successfully from the specified cell in record ${recordId}.`);
} else {
throw new Error(`Error accessing or updating the email addresses field for record ${recordId}.`);
}
} catch (error) {
console.error(error.message);
}