Help

Re: Formula for listing multiple select options in different fields

Solved
Jump to Solution
5489 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Paul_Downey
5 - Automation Enthusiast
5 - Automation Enthusiast

I’ve created a base for managing a complex calendar at my institution. I’ve created simple table and calendar views for collaborators, but I also have a table view that is strictly for exporting csv files so I can upload the events to our website since there are strict rules for formatting, etc.

All of the events have to have categories, some of which have up to three. I’ve created a multiple select field (“Category”) so people can select the appropriate categories. But when I export to csv, there can only be one category per column. I would rather keep a single “Category” multiple select so I don’t have three separate fields that people have to figure out. Is there a way to write a formula for this?

So the logic would be this:

  • I would create three fields (Category 1, Category 2, and Category 3).
  • Category 1 would find the first option in the Multiple Select field “Category.”
  • Category 2 would find the second option in “Category”
  • Category 3 would find the third option in “Category”
1 Solution

Accepted Solutions
JonathanBowen
13 - Mars
13 - Mars

Hi @Paul_Downey - it can be done, have a look at this base (copy to your workspace to see the formulas)

Screenshot 2019-08-21 at 20.16.44.png

You have to stringify the multi-select array, then find the position of the commas, then, based on the comma values, work out the multi-select options to pick out of the array.

You could probably combine this down into fewer fields, maybe even a single field, but the formulas would get pretty complicated and prone to error. I would recommend splitting this down into a number of fields as I’ve done, then hiding the fields you don’t need to see.

JB

See Solution in Thread

10 Replies 10
JonathanBowen
13 - Mars
13 - Mars

Hi @Paul_Downey - it can be done, have a look at this base (copy to your workspace to see the formulas)

Screenshot 2019-08-21 at 20.16.44.png

You have to stringify the multi-select array, then find the position of the commas, then, based on the comma values, work out the multi-select options to pick out of the array.

You could probably combine this down into fewer fields, maybe even a single field, but the formulas would get pretty complicated and prone to error. I would recommend splitting this down into a number of fields as I’ve done, then hiding the fields you don’t need to see.

JB

Wow! That worked perfectly. Thanks so much for taking the time to make that base!

Hi Jonathan,

How would you edit this to accommodate more than three multi-select options?

Hi @Lydia_Karlson - yes, this can be easily extended for more options.

Have a look at the base now, I’ve extended this out to “3 Commas, 4 Options”. If you want to go to 4 Commas/5 Options or higher you can duplicate the fields adjusting the formulas as you go. So for a 4/5 set up:

  • Copy Third Comma and adjust the fields it references (essentially move each field onto the “next” field)
  • Copy Third Option and adjust the fields (same again)
  • The Final Option column is a bit different so just rename this as Fifth Option and adjust the fields within.

JB

Yann_Salvo
4 - Data Explorer
4 - Data Explorer

Hi can i have access to the base JB please ?

Daeha_Ko
4 - Data Explorer
4 - Data Explorer

Hi @JonathanBowen could I have access to the base please? Thank you.

Hi @Daeha_Ko, @Yann_Salvo - I’m afraid I don’t have access to that base anymore. I also think there might be better ways to do this now - the solution above was suggested before scripts and automations, so I think there’s better options with these tools now. I don’t have time over the next few days, but I’ll give some thought to a script based solution (which could be turned into an automation too).

Hi. Were you ever able to find a solution to this?

Hi, were you able to create a script-based solution to this problem?

Thanks