Automation to update multi-select list based on empty or non-empty linked fields

[Access_to_Airta]

I have a table with several "linked to another record" fields and a multi-select list with one option for each linked field. I would like the list to be updated automatically based on the following logic:

1. If the linked field is empty, the option related to that field shall not be selected in the multi-select.
2. If the linked field is not empty, the option related to that field shall be selected in the multi-select.

It looks quite easy to do with a script, but laborious with an automation because of the way automations update lists. They do not add or remove options based on what has changed but instead is asking me to tell what options shall be selected and which ones not. This forces me to start my removing all the previously selected options in the multi-select list, and then define the automation based on all the potential combinations of empty and non-empty linked fields. With a limit of 25 conditional actions in an automation, I can have up to 5 linked fields and related options in the multi-select list as they will result in 25 combinations to factor.

I was wondering if someone has a better approach to do that in an automation than factoring all the combinations or even better, if someone has ever developed a script that does that.

Thanks you

[TheTimeSavingCo]

Hm I think if I were you I'd just have a formula field to handle this, and get an automation to trigger whenever the formula field gets updated, and to paste the value of the formula field into the multi-select

e.g. if your logic is the following
 - Linked field 1 = not empty, multi select option "Linked_Field_1_Filled" added
 - Linked field 2 = not empty, multi select option "Linked_Field_2_Filled" added

Then I'd have a formula field that would look like:

IF(
  {Linked Field 1}, "Linked_Field_1_Filled"
) & "," & 
IF(
  {Linked Field 2}, "Linked_Field_2_Filled"
) & "," 

And here's an example of copy pasting the formula field values into a multi-select

 

[Access_to_Airta]

Thanks Adam. Having a formula field in the way you expressed was the first approach I implemented. Then I realised that I needed a multi-select list because I am integrating airtable with something else at the front-end and that something else does not allow me to have a dropdown list to select options for filtering records if the options are expressed in a formula.

[TheTimeSavingCo]

Ah okay, sorry, I'm a bit confused.  When you say "multi-select list", are you referring to a multiple select field?

[Access_to_Airta]

Yes 🙂

[TheTimeSavingCo]

Hm okay.  After we create the formula field we're using an automation to paste the value in the formula field into the multiple select field though, right?

[sam-lsf-lst]

Hey Adam, I think I have a similar issue. I have 4 currency fields (A, B, C, D), then a multi-select field with options A, B, C, D. If any of the currency fields are >$0, I want the multi-select field to contain the corresponding field name(s).
e.g. if a record has $100 in currency field A and $200 in currency field C, I want the multi-select field to contain "A, C". 

I was able to use a "update record" automation to do this, but I can only get it to work for 1 field - currency field A is >$0 so the multi-select returns "A", but when I add another automation for currency field B >$0 --> multi-select returns "B", it overwrites the one for A. How can I get the multi-select field to return more than 1 option based on conditions?