Formula Assistance Needed

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

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

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.

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

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!