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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.