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?

Find more posts tagged with

Accepted answers

All comments

KPH

Hi @Sunil Kadikar

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?

James Keuning

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.

Sunil Kadikar

@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.

marc4

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

Andrée Starå

Hi @Sunil Kadikar

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!

Sunil Kadikar

Thanks @Andrée Starå, at this time a customized solution wouldn't be ideal, but thanks for reaching out!

Andrée Starå

@Sunil Kadikar

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.