Custom Script within Automation

[MovementPlus]

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

[dilipborad]

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.

[MovementPlus]

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!

[dilipborad]

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.

[MovementPlus]

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?

[MovementPlus]

Hey @dilipborad, just posting this incase you missed my last message. Would greatly appreciate your guidance in relation to my last post.

[dilipborad]

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