Formula Assistance Needed

[John_Dlouhy]

Greetings Airtable Community!

I am hoping a more experienced user can provide guidance on a formula.

I currently have a formula field:

IF(AND({Guest Code}=‘DONOR’,{Status S1}=‘Yes’),First&" "&Last)

This is returning the correct data (e.g. Robert Jones); however, I need to expand the formula to also include the guest of First Last (e.g. Robert Jones, Sally Smith). The guest field names are With First and With Last.

Any assistance would be greatly appreciated. I’m up against a deadline, so it’s time sensitive.

Cheers and thanks,

John

[Zollie]

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

[John_Dlouhy]

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

[Jeremy_Oglesby]

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

[John_Dlouhy]

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

[Zollie]

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.

[John_Dlouhy]

I tried this as well, but no luck. I’ll review for typos, but I’m not seeing anything that looks incorrect.

[Jeremy_Oglesby · Accepted solution]

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.

[John_Dlouhy]

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!