Skip to main content

Formula Help for multiple IFs?

  • March 24, 2022
  • 12 replies
  • 104 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!

    This topic has been closed for replies.

    12 replies

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • 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
      • 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
      • 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
      • 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
      • 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
      • 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
      • 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
      • 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
      • 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
      • 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
      • 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
      • March 25, 2022

      Amazing! Thank you so much for all your help!

        Terms & ConditionsAccessibility statement