Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 26, 2021 11:38 AM
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!
Solved! Go to Solution.
Oct 26, 2021 11:58 AM
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.
Oct 26, 2021 11:58 AM
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.
Oct 26, 2021 02:01 PM
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.
Oct 27, 2021 06:56 PM
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.)
Oct 27, 2021 09:07 PM
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.
Oct 28, 2021 06:24 AM
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.
May 28, 2023 01:45 PM
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