Two People, Same Address

One idea that comes to mind is to have a separate “Addresses” table.

When you create a “Person” record, you would have one Field(column) for “Address(es)”, and it would be a linked record field pointing to the “Addresses” table. You would create an “Address” record for the person, rather than filling in their address information in the “Person” record itself. Then, when a person has an address that already exists (somebody else has the same address), you link that person to the same address.

This also opens up the possibility of people having more than one address, since you can link more than one address record to a person, if you so chose.

There may be other ways to accomplish this as well - this is just what came to mind.

Thanks Jeremy! I started playing around with that separate table method to see if that could work. I just figured out how to combine the first names in one column using ARRAYJOIN({First name(s)}," &; "), keeping last name in a separate column, which will work fine for exporting to InDesign’s Data Marge.

The issue here now then lies when people have different last names (i.e. Tom Walker & Christine Heinzen in below screen shot). I could hand write those addresses since there aren’t that many of them, but it would be great if we could do it all within AirTable.

Try pasting this into your formula field called {First & Last Name}

IF(
   SEARCH(
      ",",
      {Last Name(s)}
   ),
   LEFT(
      {First Name/s},
      SEARCH(
         "&",
         {First Name/s}
      ) - 2
   ) & 
   " " & 
   LEFT(
      {Last Name(s)},
      SEARCH(
         ",",
         {Last Name(s)}
      ) - 1
   ) & 
   " & " & 
   RIGHT(
      {First Name/s},
      LEN({First Name/s}) - SEARCH(
         "&",
         {First Name/s}
      ) - 1
   ) & 
   " " & 
   RIGHT(
      {Last Name(s)},
      LEN({Last Name(s)}) - SEARCH(
         ",",
         {Last Name(s)}
      ) - 1
   ),
   {First Name/s} & " " & {Last Name(s)}
)

IF(
   SEARCH(
      ",",
      {Last Name(s)}
   ),
   LEFT(
      {First Name/s},
      SEARCH(
         "&",
         {First Name/s}
      ) - 2
   ) & 
   " " & 
   LEFT(
      {Last Name(s)},
      SEARCH(
         ",",
         {Last Name(s)}
      ) - 1
   ) & 
   " & " & 
   RIGHT(
      {First Name/s},
      LEN({First Name/s}) - SEARCH(
         "&",
         {First Name/s}
      ) - 1
   ) & 
   " " & 
   RIGHT(
      {Last Name(s)},
      LEN({Last Name(s)}) - SEARCH(
         ",",
         {Last Name(s)}
      ) - 1
   ),
   {First Name/s} & " " & {Last Name(s)}
)

Thanks, I tried pasting that in but it only resulted in the “#ERROR” output. I’ll play around with this a bit more and post back here if I can figure something else out.

Thanks for your help!

Hmm… I have it working in my test base, and I’m pretty sure I just copied and pasted the formula from my base. Maybe I messed something up in trying to format it all pretty for you, so here’s just a raw copy-paste from my base:

IF(SEARCH(",",{Last Name(s)}),LEFT({First Name/s},SEARCH("&",{First Name/s})-2) & " " & LEFT({Last Name(s)},SEARCH(",",{Last Name(s)})-1) & " & " & RIGHT({First Name/s},LEN({First Name/s})-SEARCH("&",{First Name/s})-1) & " " & RIGHT({Last Name(s)},LEN({Last Name(s)})-SEARCH(",",{Last Name(s)})-1),{First Name/s} & " " & {Last Name(s)})

That exact formula works in my test base, so it should work in yours too, assuming the field names match. Double check that all the field names match up to how you have your field names formatted - case sensitivity and all.