Best practice for managing couples with different surnames in the same customer record?

[Rowan_Pettett]

Hi, I’m setting up a very basic mail merge database for a real estate agent, and I’m struggling to figure out the simplest way to deal with (fairly common) instances where a property has two or more owners with different surnames.

How do I design the relevant fields so that a mail merge can format both the envelope and Dear *** in the letter just as elegantly for a John Smith and Betty Johnston customer record as for John and Betty Smith?

[Hannah_Wiginton]

Hey @Rowan_Pettett

Welcome to the Airtable community. Sounds like an interesting project!

Here’s what you need to do:

Add an Addressee and a Salutation field in your Contacts table -

You may already have this with Full Name and First Name fields.

Then, you need to link this table to another table.

In this example:

Contacts table
Listings table (real estate)

• Link the Contacts table to the Listings table
• Add a Lookup field to the Listings table that looks up the Full Name and First Name
• Add a Formula field with this formula -

ARRAYJOIN({YourAddresseeLookupField}, " and ")

This formula will join multiple record addressees, add a couple of spaces, and the word “and”

Link both Contacts to a Listing record

You can do this same setup using the First Name field to combine the first names.

[Hannah_Wiginton]

And just realizing I didn’t answer the part combining couple’s names with only 1 last name!

Let me think about that one!

[Rowan_Pettett]

Thanks so much Hannah! At least I can make a start with what you’ve suggested.

[Williams_Innova]

Hi @Rowan_Pettett and @Hannah_Wiginton,

If you have separate fields for first and last names, could use use an “if” statement to check? I’ve set up a base where I used this formula to look for the same or different surnames and it seems to work, and also let you know if there isn’t a surname.

A link to the base and the formula is below. I can also help set it up if you need.

Airtable

Airtable | Everyone's app platform

Airtable is a low-code platform for building collaborative apps. Customize your workflow, collaborate, and achieve ambitious outcomes. Get started for free.

IF(COUNTA(ARRAYUNIQUE({Last Name}))=2,
ARRAYJOIN({Names}," and “),
IF(AND(COUNTA(ARRAYUNIQUE({Last Name}))=1,COUNTA({First Name})=2),
ARRAYJOIN({First Name},” and “)&” “&{Array Unique (Last Name)},
IF(AND(COUNTA(ARRAYUNIQUE({Last Name}))=1,COUNTA({First Name})=1),
{First Name}&” "&{Last Name},
“ :warning: Check Names :warning: ”
)
)
)

Thanks,
Chris

Williams Innovations

Information Ecosystem | Williams Innovations

Williams Innovations offers consulting and management services for Airtable, information and communication structures, process mapping, and content development.

[Hannah_Wiginton]

Awesome, @Williams_Innovations! That looks nice.