Unable to use a potentially empty collaborator field in automation

Chris_Buehnerke · ‎Sep 25, 2020

I’m running into an issue in automations in multiple ways.

Ultimately I’m trying to have an email sent to a list of collaborators that are listed in various collaborator fields when a certain condition is met. One of those fields will always be filled but not all of them. When all three are filled, I don’t have any issues but when just one is empty, the automation fails to construct that element.

I tried creating an automation that would essentially copy any collaborators from the other two fields into the third but that also has issues when any one of them are blank.

The idea is a student behavioral tracker. One staff submits the issue (the “Submitter” collaborator field always filled with one staff member), other staff members may be “involved” (the “Additional Staff Involved” collaborator field which could be empty), and still others could be notified but are not involved (the “Staff to notify” collaborator field). I’d like all collaborators in any one of the fields to be notified and not need the submitter to add everyone to the notification field. I tried copying the submitter and additional staff fields into staff to notify but it throws an error if the staff to notify field is already blank (I added that as an input in case there are existing choices here).

Any ideas for a workaround? Is this a bug? I don’t have any issues creating automations with other blank fields, so far just this. (I did also run into this issue with a multi select trying to have it create a list of the names included, but when it was blank…threw an error.) Perhaps the list building feature doesn’t account for empty fields? Airtable devs, is this a feature coming down the line? Shouldn’t it throw an empty array, and if it is already, can the automation gracefully handle that empty array?

kuovonne · ‎Sep 25, 2020

Have you considered creating a formula field that combines all the email addresses together?

Chris_Buehnerke · ‎Sep 26, 2020

I did but can’t figure out how to extract the collaborators’ email addresses using a formula. You can access it through an automation but not a formula.

kuovonne · ‎Sep 26, 2020

If you don’t want to use an automation/scripting to get the collaborator’s email addresses, and if you have a small well-defined set of collaborators that doesn’t change, you can use a formula field to hardcode the conversion from collaborator names to email addresses.

IF( 
  FIND("collaborator1 name" & ", ",  ARRAYJOIN({Collaborator}, ", ") & ", "),
   "collaborator1 email, "
) &
IF( 
  FIND("collaborator2 name" & ", ", ARRAYJOIN({Collaborator}, ", ") & ", "), 
  "collaborator2 email, "
)
...

The extra commas and spaces are concatenated to avoid situations where one person’s name is a subset of someone else’s names. If there are commas in someone’s name you may need to adjust that part of the formula.

This formula will also produce a string with a trailing comma and space. It’s relatively simple to remove those two characters, but it requires doubling the length of the formula.

This could get unwieldly quickly and is prone to breaking if the possible collaborators change.

Chris_Buehnerke · ‎Oct 01, 2020

Good solution. I have about 20 collaborators to manage. They don’t change much but that’d be one heck of a formula.

kuovonne · ‎Oct 01, 2020

Yes, it would be a rather long formula, but the content is very regular. I recommend typing it up in an external editor and then copy/pasting it into Airtable.

I stopped worrying about formula length after seeing some formulas on this community with 300+ lines.