Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Formula Help for multiple IFs?

373 12
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

12 Replies 12
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.

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.

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

Untitled

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.

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})
),
“, $”,
“”
)

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.

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!