Using Concatenate with Multi-selects Leaving an ex...

[GrahamW]

My brain is broken (or my formula is)…

We have 4 departments (Production, Creative, Marketing, BD). When a prospective client is in our radar we run their product through a review process to get a sense from a variety of perspectives, the quality of their work.

We submit the materials into an interface and the head of each department assigns a member from their team to the review. Once all 4 heads of assigned and marked their department ‘ready’ an automation kicks in and sends out an email… Sometimes there is 1 person assigned from each department… sometimes there are 2 or more…

So my sheet has (for each department)

• 1 field for the user assignment
• 1 field that looks up the emails (and formats it in the manner expected “graham@email.com, joe@email.com”

finally though, I have a formula feild that attempts to combine all those emails into one string of comma separated emails to that it can be inserted into the to: field of my outbound, automated email.

Problem is… that doesnt work…

When there is more than one person assigned from a department, the comma between departments doesnt exist…

What should look like : “graham@email.com, joe@email.com, david@email.com, charles@email.com, arnold@email.com, steve@email.com”

… instead looks like “graham@email.com, joe@email.comdavid@email.com, charles@email.comarnold@email.com, steve@email.com”

I can’t figure it out. Maybe my use of concatenate is misguided?

Here is the formula

CONCATENATE(IF({Marketing Reviewer(s) Email(s)}=0," “,{Marketing Reviewer(s) Email(s)} & “,”),IF({Biz Dev Reviewer(s) Email(s)}=0,” “,{Biz Dev Reviewer(s) Email(s)}& “,”),IF({Creative Reviewer(s) Email(s)}=0,” “,{Creative Reviewer(s) Email(s)}& “,”),IF({Production Reviewer(s) Email(s)}=0,” ",{Production Reviewer(s) Email(s)}& “,”))

Thanks in advance for any help

[GrahamW]

Posting a Follow up to this…

Going back to this… For each department we have

• Field A = a look up field to assign the person to the review.
• Field B = a look up field that then combines the emails for those people(and formats it in the manner expected “graham@email.com, joe@email.com”
• Field C = a third field that combines all those emails from all departments into one into one string of comma separated emails…

Well field C is broken…

If I have this formula in field C:

IF({Field B}=0," ",{FieldB}& “,”)

Field B could look like: graham@email.com, joe@email.com
But in field C it looks like “graham@email.comjoe@email.com”

Why???

[augmented · Accepted solution]

Hi Graham. You need to use ARRAYJOIN on your lookup fields before cat’ing them together. Check it out.

[GrahamW]

Im sorry, Augmented. Im not following. Is there a video or article you can point me at?

[GrahamW]

I figured it out. Thanks augmented.

[Selimafifi]

Hi @GrahamW 

what formula do you use that is able to find emails in one cell separated by commas. in other words, i have my calendar auto sync, it populates a cell with attendees of events, but then i need to look up these email to sync with contacts, but unfortunately the lookup field can't recognize different emails separated by commas. 

[GrahamW]

Hope this helps! 

The attendees you have going, I assume are on a different table that also lists their emails? 

I would think you need three fields to accomplish what you are trying to do 

1. Name: The name of the attendee as a linked field
2. Attendees Email: a look up field to their email address (on the other table)
3. ArrayJoin: a formula field with the formula ARRAYJOIN({Attendees Email},",")