Having trouble breaking this down since I’m not sure what field types and example data you’re dealing with.
Could you try writing out the relevant table and field names as outlined below?
Table Name
Field (field type)
Example data
People
Name (single line text)
Bob Jones
Age (number)
42
Hi Zollie,
TABLE NAME = People
Guest Code = Multiple Select
Donor, Scholar, Staff, etc.
Satus S1 = Multiple Select
Yes, No, Hasn’t Replied, etc.
First = Single Line Text
Susan
Last = Single Line Text
Smith
With First = Single Line Text
John
With Last = Single Line Text
Jones
Hope this helps clarify what I’m dealing with!
John
Hi Zollie,
TABLE NAME = People
Guest Code = Multiple Select
Donor, Scholar, Staff, etc.
Satus S1 = Multiple Select
Yes, No, Hasn’t Replied, etc.
First = Single Line Text
Susan
Last = Single Line Text
Smith
With First = Single Line Text
John
With Last = Single Line Text
Jones
Hope this helps clarify what I’m dealing with!
John
This might work for you, @John_Dlouhy. Might need some minor tweaks if you have odd cases of Last names with no First names, etc.
IF(
AND({Guest Code}=‘DONOR’,{Status S1}=‘Yes’),
IF(First, First) &
IF(Last, " " & Last) &
IF({With First}, ", " & {With First}) &
IF({With Last}, " " & {With Last})
)
Thanks so much, Jeremy! I did try this, but unfortunately the system didn’t like it. I have no instances of Last names with no First names (i.e. there are not empty First fields and no empty Last fields). How would I tweak accordingly?
Currently I’m using the following:
IF(OR({Guest Code}=‘DONOR’,{Status S1}=‘Yes’),First & " " & Last & " " & {With First} & " " & {With Last})
It’s returning results–but with nothing separating the names (e.g. Sam Smith Jane Jones).
I was hoping for a solution that would add a comma or and between the names when With First and With Last are populated (e.g. Sam Smith and Jane Jones) but would omit the comma or and when With First and With Last are empty.
Doable?
Thanks very, very much!
John
Thanks so much, Jeremy! I did try this, but unfortunately the system didn’t like it. I have no instances of Last names with no First names (i.e. there are not empty First fields and no empty Last fields). How would I tweak accordingly?
Currently I’m using the following:
IF(OR({Guest Code}=‘DONOR’,{Status S1}=‘Yes’),First & " " & Last & " " & {With First} & " " & {With Last})
It’s returning results–but with nothing separating the names (e.g. Sam Smith Jane Jones).
I was hoping for a solution that would add a comma or and between the names when With First and With Last are populated (e.g. Sam Smith and Jane Jones) but would omit the comma or and when With First and With Last are empty.
Doable?
Thanks very, very much!
John
Jeremy’s solution is what you’re looking for. You probably just have a typo of some sort.
Try this:
IF(AND({Guest Code}=‘DONOR’,{Status S1}=‘Yes’),IF({First}, {First}) &IF({Last}, " " & {Last}) &IF({With First}, ", " & {With First}) &IF({With Last}, " " & {With Last}))
It’s just his solution without indentations and some curly braces around First and Last. You’ll also want to check that field names in the formula are correct (those are anything between curly braces { } ).
If you still can’t get it, the quick and dirty solution is to add a comma in the quotes between Last and With First (", "). Like this:
IF(OR({Guest Code}=‘DONOR’,{Status S1}=‘Yes’),First & " " & Last & ", " & {With First} & " " & {With Last})
But as you’ve noted, if a donor doesn’t have a guest, with this solution, you’ll end up with:
"Bobby Jones, "
That’s happening because you’re not using IF blocks to check if Bobby has a guest.
Jeremy’s solution is what you’re looking for. You probably just have a typo of some sort.
Try this:
IF(AND({Guest Code}=‘DONOR’,{Status S1}=‘Yes’),IF({First}, {First}) &IF({Last}, " " & {Last}) &IF({With First}, ", " & {With First}) &IF({With Last}, " " & {With Last}))
It’s just his solution without indentations and some curly braces around First and Last. You’ll also want to check that field names in the formula are correct (those are anything between curly braces { } ).
If you still can’t get it, the quick and dirty solution is to add a comma in the quotes between Last and With First (", "). Like this:
IF(OR({Guest Code}=‘DONOR’,{Status S1}=‘Yes’),First & " " & Last & ", " & {With First} & " " & {With Last})
But as you’ve noted, if a donor doesn’t have a guest, with this solution, you’ll end up with:
"Bobby Jones, "
That’s happening because you’re not using IF blocks to check if Bobby has a guest.
I tried this as well, but no luck. I’ll review for typos, but I’m not seeing anything that looks incorrect.
I tried this as well, but no luck. I’ll review for typos, but I’m not seeing anything that looks incorrect.
It’s the quotes that are likely getting you. I apologize for that – I left some “smart” (curly) quotes in my formula on accident, and Airtable’s formula editor doesn’t like those.
Here’s a revised version of my formula:
IF(
AND({Guest Code}='DONOR',{Status S1}='Yes'),
IF(First, First) &
IF(Last, " " & Last) &
IF({With First}, ", " & {With First}) &
IF({With Last}, " " & {With Last})
)
I’ve corrected the quotes – give that a try.
It’s the quotes that are likely getting you. I apologize for that – I left some “smart” (curly) quotes in my formula on accident, and Airtable’s formula editor doesn’t like those.
Here’s a revised version of my formula:
IF(
AND({Guest Code}='DONOR',{Status S1}='Yes'),
IF(First, First) &
IF(Last, " " & Last) &
IF({With First}, ", " & {With First}) &
IF({With Last}, " " & {With Last})
)
I’ve corrected the quotes – give that a try.
P E R F E C T I O N !!!
G E N I U S !!!
Saved my day in so many ways—it’s truly appreciated, Jeremy!
