Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 25, 2020 11:57 PM
Hello. I have a Status field which is “Multiple Select”. I am trying to build an automation that automatically adds “new” to the Status field when a new record is created. However, the Status field may start empty or it may start filled with other options from the “Multiple Select”.
I can make an automation that works when the field starts empty (just assign “new”), but this will erase any other options if the field starts filled. If the field starts filled, I can assign “new”,{Status}, but this gives an error when Status starts empty ({Status},“new” also does not work). The error is ‘Failed to construct the value for “Fields”.’ So I cannot account for both at the same time. However, my table will have both cases for new records. Am I missing something about how to concatenate the already selected options with my new option?
The work around I have found is to use an additional formula field that either fills “new” or concatenates "new, " to a non-empty field. However I would rather not have this extra field and this seems simple enough that I’m hoping that I am just missing something. Please let me know if you know how to fix this. Thank you!
Sep 26, 2020 07:17 AM
Welcome to the community, @Marcus_Lee!
You shouldn’t be getting an error message when trying to update an empty multiple select field. That should work just fine.
However, you DID stumble upon one of the current limitations of Airtable’s automations: it doesn’t APPEND options to a multiple select field, but rather it completely OVERWRITES your multiple select field.
The workaround to this problem is also what you’ve discovered: if you create a formula field that results in values separated by commas, you can set your multiple select field to that formula, and Airtable will interpret that as different multiple select values.
To simply APPEND options at this point in time, you would need to either write your own custom JavaScript, or turn to an external automation platform like Integromat (which is my personal favorite no-code automation platform).
Sep 26, 2020 08:12 AM
Correction: If you insert the original value from the trigger record, add a comma, and then type whatever is supposed to be appended, Airtable Automations does in fact append multiselect field values. If you’re updating a record which isn’t the trigger record you could pull the original multiselect options using whatever script was used to get that record’s ID into the Update Record step using an output variable.
Sep 26, 2020 08:21 AM
Ooh, this is a hot tip! I did not know this!! I never even tried that!!
Thanks so much, @Kamille_Parks!! :grinning_face_with_big_eyes: :raised_hands: :star2:
@Marcus_Lee, do what @Kamille_Parks said! :stuck_out_tongue_closed_eyes:
Sep 27, 2020 05:37 PM
Thanks for the suggestions, @Kamille_Parks and @ScottWorld ! @Kamille_Parks, your suggestion seems to work if I have some entries in the Multiple Select Field already. But if the Multiple Select Field starts of empty, then using the field value with a comma and the new text fails. Is there something that might work for the empty field and filled field at the same time? Thanks!
Sep 27, 2020 05:58 PM
There are a couple solutions including: use a Script Action before the Update Record action to do some error catching, or using two different Automations (one for when the field is empty, one where the field is filled).
Sep 27, 2020 06:04 PM
Alternatively, you could always go back to your original solution, which is the extra formula field. You can always hide the Formula field so you don’t need to see it.
Sep 27, 2020 06:31 PM
Thanks both for your help! I will try some of these suggestions. @ScottWorld and @Kamille_Parks
Oct 29, 2020 10:31 AM
Hi there!
I’m not sure if you’re still looking, but I found what I think is a better solution to this problem. (I’ve been frustrated by it myself for a while!)
When setting up your automation, find the multiple select field you want to append the new option to. In my example, this is just a generic “Tags” field. Click the little blue plus and choose the record that triggered your automation. Find the same multiple select field and select “Continue.”
Now find “Make a new list of…” and select “<>id”. Then just type in a comma and whatever item you want appended (or multiple items separated by commas, presumably). Now, whenever the automation triggers, it will duplicate the existing selected options and add your new option as well! No need for an extra formula field or anything :slightly_smiling_face:
See attached screenshot if more clarity is needed.
EDIT: One caveat I just discovered is that this will add an empty option AND your appended option if the field is empty to begin with.
Oct 29, 2020 10:35 AM
The replies above you explain this process and the fact that it doesn’t work for currently empty fields.