Skip to main content

Hello wonderful AT folks,

I'm trying to solve an issue in automations and am looking for help.

I have a system where we need multiple users to review a document and then fill out a quick form to indicate their findings. The document record contains a multiselect field of all the users who need to perform this. I'm trying to create another multiselect user field of all the users who still need to complete it.

What I have so far: an automation duplicates the users who "need to perform this" over to the "users who still need to do this" field.

Next, when a review is entered in a linked table, a complex SUBSTITUTE formula removes that reviewer's name from the list of remaining reviewers. An automation then copies that formula result onto the "still needed" field in the Documents table. And that works!

But, when the last reviewer is done, the automation refuses to copy the blank cell onto the multiselect field and errors out instead.

So, I tried adding conditional logic. If there's anything in the formula result, then copy that over to Documents. If the result is empty, update the field to [empty box]. And that's erroring out as well.

I know that scripting would be the ideal solution here, but I'm not a scripter...if anyone has any insights on User multiselect fields and why they can't be set to empty in an Automation, I'd be forever grateful.

Hm, I just tested an automation to update a multiple select field with nothing:

And the preview appeared to do what you need:


Could you share a screenshot of your automation setup and the error message?

 

This could likely be done simpler and without the complicated formula 🙂
My idea is to have 2 fields:
- "Review Required'
- "Review Completed" (a user could add their name to this field with a interface button or form)

When either of them get updated, they run an automation with this script.
Important: make sure you also set the 'recordId' input variable.

 

 

// Import the input object to receive the configuration values.

let inputConfig = input.config();

let table = base.getTable('tbXXXXXXXXX'); // Replace with table ID



// Fetch the record being updated based on the automation trigger.

let recordId = inputConfig.recordId;

let record = await table.selectRecordAsync(recordId);



// Retrieve the Review Required and Review Completed fields.

let reviewRequired = record.getCellValue('fldXXXXXX'); // Replace with field ID for "Review Required"

let reviewCompleted = record.getCellValue('fldXXXXXX'); // Replace with field ID for "Review Completed"



// If Review Required and Review Completed are not null, update Review Required.

if (reviewRequired && reviewCompleted) {

// Convert the list of names to a set for quick lookup.

let completedNamesSet = new Set(reviewCompleted.map(person => person.name));



// Filter out the names that have completed the review.

let updatedReviewRequired = reviewRequired.filter(person => !completedNamesSet.has(person.name));



// Update the 'Review Required' field with the remaining names.

await table.updateRecordAsync(recordId, {

'Review Required': updatedReviewRequired

});

}

 

 

This script checks the names in "Review Completed", and if the name exists in "Review Required" then it gets removed.

Here is an example Base: https://airtable.com/appl2PBcfH8MKKWWx/shrLqJHFiZNW9FPLG

Reply


Terms & Conditions