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.

Find more posts tagged with

Accepted answers

Leibel S

@Katy Hall

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), ", "), "")

All comments

Mark Cronk

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

Katy Hall

Thanks Mark, unfortunately that is returning an #invalid operation but will see if this leads me down a new path with this new perspective!

Leibel S

@Katy Hall

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), ", "), "")

Katy Hall

@Leibel S thank you so much! This worked perfectly!!

Mark Cronk

Well done @Leibel S !

Mark

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.