Skip to main content

I have 3 “Yes or No” single select fields and 3 email fields. If the answer is yes I want the corresponding email to be added and a comma to separate the emails. I can’t seem to figure the correct formula out:


Review 1 (Single Select Field) (Yes or No)

Review 2 (Single Select Field)(Yes or No)

Review 3 (Single Select Field)(Yes or No)


Email 1 (Lookup Field) (test1@test.com)

Email 2 (Lookup Field) (test2@test.com)

Email 3 (Lookup Field) (test3@test.com)


If Review 1 is YES add Email1 and a comma (if NO add nothing)

and

If Review 2 is YES add Email2 and a comma (if NO add nothing)

and

If Review 3 is YES add Email3 (if NO add nothing but would not want the comma from previous YES)


Any help would be appreciated!

REGEX_REPLACE(

    CONCATENATE(

        IF({Review 1} = "Yes", {Email 1} & ", "),

        IF({Review 2} = "Yes", {Email 2} & ", "),

        IF({Review 3} = "Yes", {Email 3})

    ),

    ", $",

    ""

)




REGEX_REPLACE(

    CONCATENATE(

        IF({Review 1} = "Yes", {Email 1} & ", "),

        IF({Review 2} = "Yes", {Email 2} & ", "),

        IF({Review 3} = "Yes", {Email 3})

    ),

    ", $",

    ""

)

Thank you so much! The one issue is if Review 1, 2 or 3 are NO it is still putting the email address in the formula field.

Thank you so much! The one issue is if Review 1, 2 or 3 are NO it is still putting the email address in the formula field.


Oops. I was thinking that they were checkboxes. I updated the formula.

Oops. I was thinking that they were checkboxes. I updated the formula.


I appreciate it! One last thing. If say Review 1 is YES and Review 2 and Review 3 are NO it leaves a comma at the end or if Review 1 is NO and Review 2 is YES and Review 3 is NO it leaves a comma at the end.

I appreciate it! One last thing. If say Review 1 is YES and Review 2 and Review 3 are NO it leaves a comma at the end or if Review 1 is NO and Review 2 is YES and Review 3 is NO it leaves a comma at the end.



It does not leave a comma at the end in my base. Can you please share a screen capture?


It does not leave a comma at the end in my base. Can you please share a screen capture?





Even if there’s a comma at the end, it should still recognize the email as a valid entry when composing. BTW, most email programs, including outlook, require a " ; " between addresses and won’t working with a comma



Can you also include a screen shot of the configuration of the formula field? It looks like there also is no space after the comma. The formula that I provided would have had a space after each comma.

Can you also include a screen shot of the configuration of the formula field? It looks like there also is no space after the comma. The formula that I provided would have had a space after each comma.


I removed the space after the comma because the automation in AirTable (Outlook Email) would not work with the space added:


REGEX_REPLACE(

CONCATENATE(

IF({CCO to Review}= “YES”, {CCO Email} & “,”),

IF({Designee to Review}= “YES”, {Approver Email} & “,”),

IF({Alternate to Review}= “YES”, {Alternate Email})

),

“, $”,

“”

)

Even if there’s a comma at the end, it should still recognize the email as a valid entry when composing. BTW, most email programs, including outlook, require a " ; " between addresses and won’t working with a comma


You are correct that it works with the comma on the end. I did try the ; instead of the comma but the email automation in AT would not work unless it was a comma with no spaces.

I removed the space after the comma because the automation in AirTable (Outlook Email) would not work with the space added:


REGEX_REPLACE(

CONCATENATE(

IF({CCO to Review}= “YES”, {CCO Email} & “,”),

IF({Designee to Review}= “YES”, {Approver Email} & “,”),

IF({Alternate to Review}= “YES”, {Alternate Email})

),

“, $”,

“”

)



Ah ha, this is the issue. If you do not want the space, you also have to remove the space from the REGEX_REPLACE as well. Thank you for the screen captures. It made it much easier to diagnose the problem.


REGEX_REPLACE(

    CONCATENATE(

        IF({CCO to Review}= “YES”, {CCO Email} & ","),

        IF({Designee to Review}= “YES”, {Approver Email} & ","),

        IF({Alternate to Review}= “YES”, {Alternate Email})

    ),

    ",$",

    ""

)

Amazing! Thank you so much for all your help!

Reply


Terms & Conditions