Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 21, 2019 08:19 AM
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:
Solved! Go to Solution.
Aug 21, 2019 12:17 PM
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
Aug 21, 2019 12:17 PM
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
Aug 21, 2019 01:58 PM
Wow! That worked perfectly. Thanks so much for taking the time to make that base!
Dec 10, 2019 07:30 PM
Hi Jonathan,
How would you edit this to accommodate more than three multi-select options?
Dec 12, 2019 03:26 AM
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:
JB
Aug 26, 2021 04:44 PM
Hi can i have access to the base JB please ?
Sep 21, 2021 01:02 PM
Hi @JonathanBowen could I have access to the base please? Thank you.
Sep 22, 2021 07:09 AM
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).
May 02, 2022 02:07 PM
Hi. Were you ever able to find a solution to this?
Aug 25, 2022 06:05 PM
Hi, were you able to create a script-based solution to this problem?
Thanks