How to transform a Multi-select dropdown into multiple rows
Hello, I am using an intake form to capture the preferences of the submitter in a "Choices" column, there are over 100 available options in that column.
I'd like to be able to group and sort these in a Report, however this is not possible to do with a Multi-Select Dropdown value.
As such, I'd like to transform the submission into multiple rows. For example, if someone enters:
Name: John Doe
Choices: A,B,C,D,E,Q
I'd like it to be transformed into:
Name Column: John Doe, Choice Column: A
Name Column: John Doe, Choice Column: B
Name Column: John Doe, Choice Column: C
Name Column: John Doe, Choice Column: D
Name Column: John Doe, Choice Column: E
Name Column: John Doe, Choice Column: Q
I've been seeing articles on the forums using "HAS' and Copy Row automations, but with over 100 choices, it seems like it could mean 100 automations. Is there a better way?
Answers
-
Does it need to be transformed into rows or could it be into columns - so still one row for John but a column for each possible choice with those columns having yes or no in?
-
You have encountered a limitation of Smartsheet, and I wish there was a way to make this happen.
You could create a sheet that has a row for each possible selection for each person, and then filter that sheet to only show records where the person selected that choice, but in my experience, these sheets quickly run into Smartsheet formula limitations.
But KPH also has a workable Idea, which is a column for each possible answer choice. Then you use a cross-sheet reference to populate the columns.
-
@KPH i don’t think columns would work, as the goal is to be able to use a report which groups rows.
@James Keating, thanks for insight. In a perfect world, Smartsheet reports would allow grouping based on a multi-select column, showing repeat rows in each section.
-
If you are comfortable using the API you can grab the new line and the write out a line for each entry in the choices column.
/marc
-
I hope you're well and safe!
Yes, it's possible, but it's pretty advanced.
I recently I developed a solution for a client that creates a select number of assignment rows with specific details from a defined start date that consultants use to report their work hours on a row per date.
Do you have a budget to bring in a consultant (like me) to help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks @Andrée Starå, at this time a customized solution wouldn't be ideal, but thanks for reaching out!
-
Happy to help!
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!