Skip to main content

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’


)

)

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

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.


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"
)
)
)

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.


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.




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.




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'
)
))

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'
)
))

Oooo!


This is helpful!

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


Oooo!


This is helpful!

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



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.


Reply