Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Primary field is person's name - how to handle people with the same name?

Topic Labels: Base design
Solved
Jump to Solution
4399 6
cancel
Showing results for 
Search instead for 
Did you mean: 
cori_parrish1
6 - Interface Innovator
6 - Interface Innovator

i’m creating a database where one of the tables is “people.” currently i have it set up so that the primary field concatenates the person’s first and last names to become a full name field. i also have this linked to another table in the base so that people in the table can register for events by finding their record in the people table to associate with their registration.

however, it just occurred to me that i haven’t accounted for the fact that people in our database might have the same name, which would make the primary field not unique. i don’t really want to add their record ID to the primary field formula, which is the easiest solution i can think of, because then people would see that when they register for an event & find themselves in the list.

i suppose i could create a more complicated way to handle RSVPs where it comes into another table & then matches with existing people somehow (i know this is possible, but haven’t learned how to do it yet).

is there an easier way that i’m not seeing? thanks!

1 Solution

Accepted Solutions
WilliamPorter
9 - Sun
9 - Sun

The primary field in the table of an Airtable base does not have to be unique. You could create a thousand records that have “John Smith” in the primary field of every one of them. Not a problem. Why isn’t it a problem? Because the primary field is not the key value in Airtable. That’s a large part of Airtable’s genius: It hides the keys from you completely.

The primary field is simply a title for the record, a “Name” for the record. The presumption is that the record itself represents something unique. So let’s say – thinking of Sherlock Holmes and the Red-Headed League but with names – let’s say you form a club called the “John Smith League”, in which every member must be named John Smith. The name field could contain “John Smith” for everybody. Or you could add middle names or initials, but that would probably create duplicates too. Doesn’t matter. What does matter is that each “John Smith” represented by a record actually be a different person. So you could distinguish the records by other means: addresses, phone numbers, government IDs (subject to pertinent laws regarding use of those numbers) and so on.

A lot of us make our primary fields formula fields, so I might have “John Smith 27 Main St #252, San Antonio TX 78209”. NOTE even that might not be sufficient: Possible identically named father and son live at the same address!

I prefer not to make the primary field a formula field when I can avoid doing so, and I can usually avoid it in the main tables in a base, the ones where I enter the most records. Some other tables might be “utility” tables where I need a formula to be able to link to another table.

.

ADDED FEW MINUTES LATER. You might be asking yourself, “If there are a bunch of records whose primary field value is ‘John Smith’, how do my users and I know that they’re actually different people and not duplicate records?” Good question. This is actually a big and important subject in its own right. Basic tips.

  1. Be sure that the table includes other fields besides Name that can reasonably be expected to “disambiguate” or distinguish one John Smith from another. As I suggested above, using address might not do it (if father and like-named son live together). But adding a DOB field would be a big help. Phone numbers can help. And so on. This way, if users search for “John Smith” and find two records, they see fairly quickly that they’re not the same person and pick the one they want.
  2. And if the two “John Smith” records aren’t obviously for different people? It can be useful to have two utility fields named something like “Suspected Duplicate” (maybe a checkbox) and “Suspect Duplicate Memo” where persons who notice potential problems can write down what they see, so it can be investigated.
  3. Airtable doesn’t have good validation features that prevent duplicates but even platforms (like, say, FileMaker) that do, can have problems with accidental duplication of values. I built a big lawsuit database in FileMaker where the property addresses were critical. I had extensive validation trying to prevent any given property address being entered twice. But the folks doing data entry were minimum wage clerks who’d been hired to enter lots of data fast and they were careless. So we’d get “123 Main Street” and “123 Main St”, or “4738 Martin Luther King Blvd” and “4738 MLK Blvd”, or “14 Adams St” and “14 Addams” and so on. You have to train the folks doing data entry to be careful, and then you have to audit your data regularly looking for just such problems.

See Solution in Thread

6 Replies 6
WilliamPorter
9 - Sun
9 - Sun

The primary field in the table of an Airtable base does not have to be unique. You could create a thousand records that have “John Smith” in the primary field of every one of them. Not a problem. Why isn’t it a problem? Because the primary field is not the key value in Airtable. That’s a large part of Airtable’s genius: It hides the keys from you completely.

The primary field is simply a title for the record, a “Name” for the record. The presumption is that the record itself represents something unique. So let’s say – thinking of Sherlock Holmes and the Red-Headed League but with names – let’s say you form a club called the “John Smith League”, in which every member must be named John Smith. The name field could contain “John Smith” for everybody. Or you could add middle names or initials, but that would probably create duplicates too. Doesn’t matter. What does matter is that each “John Smith” represented by a record actually be a different person. So you could distinguish the records by other means: addresses, phone numbers, government IDs (subject to pertinent laws regarding use of those numbers) and so on.

A lot of us make our primary fields formula fields, so I might have “John Smith 27 Main St #252, San Antonio TX 78209”. NOTE even that might not be sufficient: Possible identically named father and son live at the same address!

I prefer not to make the primary field a formula field when I can avoid doing so, and I can usually avoid it in the main tables in a base, the ones where I enter the most records. Some other tables might be “utility” tables where I need a formula to be able to link to another table.

.

ADDED FEW MINUTES LATER. You might be asking yourself, “If there are a bunch of records whose primary field value is ‘John Smith’, how do my users and I know that they’re actually different people and not duplicate records?” Good question. This is actually a big and important subject in its own right. Basic tips.

  1. Be sure that the table includes other fields besides Name that can reasonably be expected to “disambiguate” or distinguish one John Smith from another. As I suggested above, using address might not do it (if father and like-named son live together). But adding a DOB field would be a big help. Phone numbers can help. And so on. This way, if users search for “John Smith” and find two records, they see fairly quickly that they’re not the same person and pick the one they want.
  2. And if the two “John Smith” records aren’t obviously for different people? It can be useful to have two utility fields named something like “Suspected Duplicate” (maybe a checkbox) and “Suspect Duplicate Memo” where persons who notice potential problems can write down what they see, so it can be investigated.
  3. Airtable doesn’t have good validation features that prevent duplicates but even platforms (like, say, FileMaker) that do, can have problems with accidental duplication of values. I built a big lawsuit database in FileMaker where the property addresses were critical. I had extensive validation trying to prevent any given property address being entered twice. But the folks doing data entry were minimum wage clerks who’d been hired to enter lots of data fast and they were careless. So we’d get “123 Main Street” and “123 Main St”, or “4738 Martin Luther King Blvd” and “4738 MLK Blvd”, or “14 Adams St” and “14 Addams” and so on. You have to train the folks doing data entry to be careful, and then you have to audit your data regularly looking for just such problems.

Welcome to the Airtable community!

Are your users Airtable collaborators on the base, or are they external users who are filling out a form? If they are external users filling out a form, they will not see the additional information when picking a linked record for the form. It will be impossible for form users to differentiate between records with identical primary field values.

Usually when I need to uniquely identify a user, I prefer to do it via email address versus name.

On the other hand, this may not be an issue if you do not actually have users with the same name. You can tell if all names are unique by setting the summary bar for that field to display % unique.

Just for the record, this doesn’t always work, either. In the moderately large data sets that I work with, it’s uncommon but by no means unheard of for more than one person to share an email address. Another problem with email addresses is that they can change.

The sad truth is, there’s virtually NO single datum that can be used as a guarantee of uniqueness at least when we’re talking about human beings. SSN? Nope. Phone number? Nope. DNA sample? Maybe, if we could, you know, just make taking the sample practical. (I used to believe that identical twins had identical DNA but apparently that’s not true.)

Yup. Email addresses are not always unique. But they tend to be more unique that names. And they are more meaningful than arbitrarily assigned numeric IDs. And when they are not unique, the people sharing the email address know each other and are aware that they share the address.

Love the deep geeky conversation. I fear this statement will be true and unchanged in my lifetime. Although, at Stream It, we are using Nano ID for many reasons including performance.

@WilliamPorter 

thank you both for your answers. I guess my question here is pros and cons of email vs. Full name as Primary fields. At first i had emails, which is very useful as i tend to link my calendar instances using "attendees" field which then links with contact email address. But the cons was whenever i searched i want to search for a contact, i can't do so using their name. (in forms etc, linking manually in other tables etc). Now i switched the primary field to full name, but then i am unable to auto link my calendar instance using Attendees field. So my question here is: what is best practice? is there a way of keeping email, but being able to find contacts using their name rather when searching? alternatively, is there a way of linking record to table using a defined field than automatic? thanks