JOIN/COLLECT when referencing a Dropdown (Multi Select)
I am trying to use a conditional IF based JOIN/COLLECT to generate a phrase in another column for notification purposes, but the column that the JOIN/COLLECT is referencing is a multi-select column and I can’t figure out a way to join multiple items from that column together.
The column of interest is a list of possible allergens that could be in a food product. Based on the number of allergens present the phrasing may change. For instance. If “wheat” is chosen as an allergen It would say “Contains: wheat”, If “wheat and milk (lactose)” are chosen as allergens it would say “Contains: wheat and lactose” and then a final option if more than two are chosen the final result would include commas in addition to the “and”. I am trying to avoid including the allergen selections in separate columns, but currently if I try to JOIN/COLLECT 2 or more selections on the multi drop down it repeats both selections. Example: “Contains: Milk (Lactose) Wheat and Milk (Lactose) Wheat”
Below are screenshots of the columns and formula currently being trialed.
Best Answer
-
The below should do the trick
=IF(COUNTM(Allergens@row) > 0, "Contains: " + SUBSTITUTE(SUBSTITUTE(Allergens@row, CHAR(10), " and ", COUNTM(Allergens@row) - 1), CHAR(10), ", "), "")
Answers
-
Hi @Katy Hall ,
You won't get the "and" but try:
=IF(ISBLANK([number of allergens]@row), " ", "Contains: " + JOIN(DISTINCT(COLLECT(allergens@row)),", ")
Any better?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Thanks Mark, unfortunately that is returning an #invalid operation but will see if this leads me down a new path with this new perspective!
-
The below should do the trick
=IF(COUNTM(Allergens@row) > 0, "Contains: " + SUBSTITUTE(SUBSTITUTE(Allergens@row, CHAR(10), " and ", COUNTM(Allergens@row) - 1), CHAR(10), ", "), "")
-
@Leibel S thank you so much! This worked perfectly!!
-
Well done @Leibel S !
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!