Skip to main content

Formula Help for multiple IFs?

  • March 24, 2022
  • 12 replies
  • 61 views
M
Forum|alt.badge.img+2

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

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • 6009 replies
    • March 24, 2022
    REGEX_REPLACE(
    CONCATENATE(
        IF({Review 1} = "Yes", {Email 1} & ", "),
        IF({Review 2} = "Yes", {Email 2} & ", "),
        IF({Review 3} = "Yes", {Email 3})
    ),
    ", $",
    ""
)
    M
    Forum|alt.badge.img+2
    • Mark_Comish
    • Author
    • Known Participant
    • 13 replies
    • March 24, 2022
    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.

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • 6009 replies
    • March 24, 2022

    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.

    M
    Forum|alt.badge.img+2
    • Mark_Comish
    • Author
    • Known Participant
    • 13 replies
    • March 24, 2022

    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.

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • 6009 replies
    • March 24, 2022

    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?

    M
    Forum|alt.badge.img+2
    • Mark_Comish
    • Author
    • Known Participant
    • 13 replies
    • March 25, 2022

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


    T
    Forum|alt.badge.img+5
    • Tom_Blanchard
    • New Participant
    • 3 replies
    • March 25, 2022


    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

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • 6009 replies
    • March 25, 2022


    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.

    M
    Forum|alt.badge.img+2
    • Mark_Comish
    • Author
    • Known Participant
    • 13 replies
    • March 25, 2022

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

    M
    Forum|alt.badge.img+2
    • Mark_Comish
    • Author
    • Known Participant
    • 13 replies
    • March 25, 2022

    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.

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • 6009 replies
    • March 25, 2022

    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})
    ),
    ",$",
    ""
)
    M
    Forum|alt.badge.img+2
    • Mark_Comish
    • Author
    • Known Participant
    • 13 replies
    • March 25, 2022

    Amazing! Thank you so much for all your help!


    Terms & ConditionsAccessibility statement