Can you use 'contains' function within Report Builder...
We are pulling in a field with our JIRA Connector that has values separated by commas.
Example of Data the column labeled Product:
- First row of data has: AK-HO
- Second row of data has AK-HO, AL-HO, AL-DP
- Third row of data has AK-HO, NY-HO, CA-HO
I need to report to tell me each each row that has AK-HO in it. However the 'What?" section of the report builder does not add the Contains function.
smartsheet says they are aware of this issue not being able to parse out the multi-select data that is imported from JIRA, and they are working on it, but no data on when it will be available or done. So, I'm trying to find a work around.
Best 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.
Answers
-
Hi Mark,
You would need to add so-called helper column(s).
Two options.
- Either parse out the data in the sheet for each option. (if possible)
- Have a formula checking a box for the specific options and use that in the report.
Make sense?
Would that work?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. 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.
-
@Andrée Starå
Thank you for your reply... I'm new to smartsheets and need to say, no sir it does not make sense... The data is coming in on the JIRA Connector from a multi-select field, so the data is received with commas between each entry.
Now, we currently have 44 unique elements that can be selected within that multi-select field, so I'm trying to create a listing of all the JIRA Tickets for each of those 44 unique elements, but parsing out is the issue right now, the Report Builder does not allow a 'Contains' statement, or if it does I do not know how to do it.
So, I'm unsure on what you are saying as to a helper column, as you can't add columns to the sheet that the JIRA Connector creates.
Does that help with what my issue is?
Thanks again for replying and to everyone that may be able to help,
Mark
-
Happy to help!
Thinking of it again! Reports actually has the option to use Contain for the What? section.
Would that work?
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.
-
-
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.
-
What type is the column?
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.
-
@Andrée Starå
The JIRA / SmartSheet Connector made it a Dropdown Multi-Select. Can I change that and not break the connector?
-
Will, I did a little testing and yes I'm able to change the column from multi-select to text and then do the contains part, so this is now fixed. Thank you!!!
-
Excellent!
You're more than welcome!
Glad you got it working!
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
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 494 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!