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.
- 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.
- 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.
- 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.
