Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Formula Help for multiple IFs?

252 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

Amazing! Thank you so much for all your help!

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

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

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.

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

Untitled

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

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.

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

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.

REGEX_REPLACE(
    CONCATENATE(
        IF({Review 1} = "Yes", {Email 1} & ", "),
        IF({Review 2} = "Yes", {Email 2} & ", "),
        IF({Review 3} = "Yes", {Email 3})
    ),
    ", $",
    ""
)