JOIN(COLLECT()), Return Multiple Values/Contacts

Hi there!

I'm trying to write a Join Collect Formula that will return the associated departments with a project based on the Owners noted on the project.

The Departments column is a multi-select drop-down, and the Owners column is a Contact List contact.

My reference sheet shows the Department and associated contact(s) that would be connected to that project.

Based on what I saw in the forums, I wrote something like this:

=JOIN(COLLECT({YE Departments}, {YE Department Contact}, HAS(Owner@row, @cell)), ";")

In another post, it looks like there's a way to pull multiple dropdown "tools" based on the name. I'm trying to replicate that, except pulling departments based on all the names in the owner field. Is there something I'm missing?

Find more posts tagged with

Join

collect

Accepted answers

Paul Newcome

Ok. That's the issue. You are trying to compare a multi-select to a multi-select which doesn't work. Your source sheet should have each individual in their own row.

All comments

Paul Newcome

Try switching your HAS function around so that @cell is the first piece and the cell reference is second.

rtaylor

Hi,

I tried swapping the has function arguments and that did not solve the problem. I added an INDEX COLLECTION function, and now it half-works. I also adjusted my formula like so and obtained these results:

=JOIN(INDEX(COLLECT({YE Departments}, {YE Department Contact}, HAS(Owner@row, @cell)), 0), ",")

Here's what happens:

has first.png

On the other hand, using this formula, swapping the @cell and Owner shows these results:

=JOIN(INDEX(COLLECT({YE Departments}, {YE Department Contact}, HAS(@cell, Owner@row)), 0), ",")

has second.png

Paul Newcome

Your second attempt was what I was actually suggesting to start with. Try that without the INDEX function.

rtaylor

Here are the results with this formula:

=JOIN((COLLECT({YE Departments}, {YE Department Contact}, HAS(@cell, Owner@row))), ",")

Paul Newcome

How exactly is your source sheet structured? Do you have a screenshot of that?

rtaylor

Here's my source sheet

Paul Newcome

Ok. That's the issue. You are trying to compare a multi-select to a multi-select which doesn't work. Your source sheet should have each individual in their own row.

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.