Formula for listing multiple select options in different fields

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”

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

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

1 Like

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