Re: Combining results of IF()

HS_789 · ‎Jul 28, 2022

Hi All,

Having trouble figuring out how to use IF, AND, concatenate for this situation:

If {Field 1} then return “Mary”, and
If {Field 2} then add “Bob” to the end of “Mary”, and then
If {Field 3} then add “Jake” to the end of “Mary” and “Bob”

It continues for all combinations of Field 1,2, or 3. This was one attempt:

IF(
{Field 1}, ‘Mary’
IF(
AND(
{Field1},
{Field 2}
),
‘Mary and Bob’

)
)

Brennan_Ward · ‎Jul 28, 2022

Hi there! Welcome to the Airtable Community!

If I understand correctly, I think it would just be separating them out, and returning blanks if the field is empty.

Try this:

IF({Field 1}, "Mary","")
&
IF({Field 2}, "Bob","")
&
IF({Field 3}, "Jake","")

If you are wanting to do combinations, (aka only showing Jake and Bob if BOTH Field 2 and Field 3 are present), you would want to do something like this.

IF({Field 1}, "Mary","")
&
IF(AND{Field 2},{Field 3}), "Bob & Jake","")

Jennifer_Davids · ‎Aug 02, 2022

I have spent a full day on this and I can not get anything to work! I have numerous date fields, {Date 1 } {Date 2 } {Date 3 } {Date 4 }. I want them to populate into another field as such:
Date 1: {Date 1}
Date 2: {Date 2 }
Date 3: {Date 3 }
Date 4: {Date 4 }
My problem is that sometimes not all these date fields are used and so I get an #error# in return for those records. How can I list the dates BUT skip the dates that are empty?
I have tried :
IF({Binding Date Fx},“Binding Date:”&{Binding Date Fx},“BLANK”)&“\n”&IF({Earnest $ Fx}, “Earnest Money Due:”&{Earnest $ Fx},“BLANK”)&“\n”&IF({DD Ends Fx}, “Due Diligence (Inspection Period) Ends:”&{DD Ends Fx},“BLANK”)&“\n”&IF({Financing Fx}, “Financing Cont. Ends:”&{Financing Fx},“BLANK”)&“\n”&IF({Appraisal Ends Fx}, “Appraisal Cont. Ends:”&{Appraisal Ends Fx},“BLANK”)&“\n”&IF({Closing Date Fx}, “Closing Date:”&{Closing Date Fx},“BLANK”)&“\n”&IF({Possession Date Fx}, “Possession Date:”&{Possession Date Fx},“BLANK”) BUT I still get #ERROR#. Any help would be greatly appreciated.

Ben_Young1 · ‎Aug 08, 2022

Hey @HS_789!

This post is kinda old, but noticed it and wanted to give it a shot:

IF(
    {Field 1},
    "Mary"
)
&
IF(
    AND(
        {Field 1},
        {Field 2},
        {Field 3}
    ),
    ", Bob",
     IF(
        AND(
            {Field 1},
            {Field 2}
        ),
        " and Bob",
        IF(
            {Field 2},
            "Bob"
        )
     )
)
&
IF(
    AND(
        {Field 1},
        {Field 2},
        {Field 3}
    ),
    ", and Jake",
    IF(
        AND(
            OR(
                AND(
                    {Field 1},
                    {Field 3}
                ),
                AND(
                    {Field 2},
                    {Field 3}
                )
            )
        ),
        " and Jake",
        IF(
            {Field 3},
            "Jake"
        )
    )
)

Ben_Young1 · ‎Aug 08, 2022

Hey @Jennifer_Davidson!
I think this might actually justify its own thread, but I’ll reply here anyways since I wanted to make sure you got an answer on this.

The first approach to your formula is this:

IF(
    {Binding Date Fx},
    "Binding Date: " & DATETIME_FORMAT({Binding Date Fx}, 'L')
)
& '\n' &
IF(
    {Earnest $ Fx},
    "Earnest Money Due: " & DATETIME_FORMAT({Earnest $ Fx}, 'L')
)
& '\n' &
IF(
    {DD Ends Fx},
    "Due Diligence (Inspection Period) Ends: " & DATETIME_FORMAT({DD Ends Fx}, 'L')
)
& "\n" &
IF(
    {Financing Fx},
    "Financing cont. Ends: " & DATETIME_FORMAT({Financing Fx}, 'L')
)
& '\n' &
IF(
    {Appraisal Ends Fx},
    "Appraisal Cont. Ends: " & DATETIME_FORMAT({Appraisal Ends Fx}, 'L')
)
& "\n" &
IF(
    {Closing Date Fx},
    "Closing Date: " & DATETIME_FORMAT({Closing Date Fx}, 'L')
)
& "\n" &
IF(
    {Possession Date Fx},
    "Possession Date: " & DATETIME_FORMAT({Possession Date Fx}, 'L')
)

This formula will return this behavior:

Now, this solution works fine, but there is something that bugs me about it.

Every IF function is separated in the formula by a & "\n" & statement.
Since you had these in your original formula, you’ll know that these add a new line.

But since there’s a possibility that a date field is blank, then it will just leave a blank line between possible values. This leads to this type of behavior:

Again, this might not bother you, but if you want to keep it tight, here’s a formula version that will eliminate this behavior.

REGEX_REPLACE(
    IF(
        {Binding Date Fx},
        "Binding Date: " & DATETIME_FORMAT({Binding Date Fx}, 'L') & 'line'
    )
    &
    IF(
        {Earnest $ Fx},
        "Earnest Money Due: " & DATETIME_FORMAT({Earnest $ Fx}, 'L') & 'line'
    )
    &
    IF(
        {DD Ends Fx},
        "Due Diligence (Inspection Period) Ends: " & DATETIME_FORMAT({DD Ends Fx}, 'L') & 'line'
    )
    &
    IF(
        {Financing Fx},
        "Financing cont. Ends: " & DATETIME_FORMAT({Financing Fx}, 'L') & 'line'
    )
    &
    IF(
        {Appraisal Ends Fx},
        "Appraisal Cont. Ends: " & DATETIME_FORMAT({Appraisal Ends Fx}, 'L') & 'line'
    )
    &
    IF(
        {Closing Date Fx},
        "Closing Date: " & DATETIME_FORMAT({Closing Date Fx}, 'L') & 'line'
    )
    &
    IF(
        {Possession Date Fx},
        "Possession Date: " & DATETIME_FORMAT({Possession Date Fx}, 'L') & 'line'
    ),
    'line',
    '\n'
)

As you add and remove dates, it will adjust the formatting appropriately.

kuovonne · ‎Aug 09, 2022

Here is a slight variation on Ben’s formula. This style is slightly more compact. I use this pattern a lot. The use of CONCATENATE() removes the need for the & between IF functions. The '\n' new line characters are inserted directly instead of being replaced with a regex. The addition of TRIM() remove any trailing newline character.

TRIM(CONCATENATE(
    IF(
        {Binding Date Fx},
        "Binding Date: " & DATETIME_FORMAT({Binding Date Fx}, 'L') & '\n'
    ),
    IF(
        {Earnest $ Fx},
        "Earnest Money Due: " & DATETIME_FORMAT({Earnest $ Fx}, 'L') & '\n'
    ),
    IF(
        {DD Ends Fx},
        "Due Diligence (Inspection Period) Ends: " & DATETIME_FORMAT({DD Ends Fx}, 'L') & '\n'
    ),
    IF(
        {Financing Fx},
        "Financing cont. Ends: " & DATETIME_FORMAT({Financing Fx}, 'L') & '\n'
    ),
    IF(
        {Appraisal Ends Fx},
        "Appraisal Cont. Ends: " & DATETIME_FORMAT({Appraisal Ends Fx}, 'L') & '\n'
    ),
    IF(
        {Closing Date Fx},
        "Closing Date: " & DATETIME_FORMAT({Closing Date Fx}, 'L') & '\n'
    ),
    IF(
        {Possession Date Fx},
        "Possession Date: " & DATETIME_FORMAT({Possession Date Fx}, 'L') & '\n'
    )
))

Ben_Young1 · ‎Aug 09, 2022

Oooo!

This is helpful!
I didn’t know that the CONCATENATE function’s behavior also accounted for those newline characters.

kuovonne · ‎Aug 09, 2022

It doesn’t. CONCATENATE() does the same as &, but I find it a little cleaner in multi-line formulas.

I added the newline characters directly to each line using & '\n', instead of & 'line' combined with the REGEX.