Skip to main content

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

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


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

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


Hi Jonathan,



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

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

Hi can i have access to the base JB please ?

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 @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 @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 able to create a script-based solution to this problem?



Thanks

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


Hi Jonathan,
I would love to read the link you offer but it's not working anymore... 😉
Is there a chance you put it back?

Reply


Terms & Conditions