Skip to main content

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)



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

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???

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

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


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

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


I figured it out. Thanks augmented.

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. 

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. 


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

Reply


Terms & Conditions