Check for duplicates when adding records

App Development
1

We have a large employee/subcontractor list that is accessible by several people. We run into problems when someone adds a name in for someone who is already in the list. It would be very helpful to be able to check if a Person Name existed in the table before the new record was created.

1 Like
2

You can use validation rules in your form. Try setting it to {Person Name} is not unique and display a message stating so. You can also check against additional fields.

3

Excellent! I guess this should be moved from the suggestion box to How Do I.

Thanks, Tim!

4

You’re welcome! I’ll move the post.

5

@Henry,

Tim has given you the key part of the answer: Set up a validation rule in the form’s settings. If that’s enough right now for you, then stop reading. :slight_smile:

But there’s a problem…

… that you will run into sooner or later: Non-unique values do not always mean duplicates. I work mainly on data management systems for law firms doing large-client litigation. It’s very common in my work for entire families to be parties to the litigation, and in Texas that often means that I have three people named “Carlos Garcia” in the system and they’re all distinct: maybe father and son and sometimes just another non-related person with the same name.

Now Tadabase does not have a simple “override validation” response – and that may be a good thing, because if it did, too many users would just click it and move on. But that means you have to devise your own way of letting users override the validation – users sometimes DO need to be able to enter a second person named “Carlos Garcia” if they know for a fact that this second one is different from the one previously entered. If you don’t give them a good way to do this, users will find a bad way. I’ve seen users, confronted with a non-unique validation challenge, change the name to “Carlos Garcia #2”. Makes me want to weep.

Confirming that non-unique value is not duplicate value

1: Test for other parts of the name

Tim noted that you can add other fields to the validation test, and that’s definitely a good idea with names. In addition to First Name, Middle Name and Last Name, I always have a field named “Jr Etc”. That allows me to enter dad as “Carlos Garcia” and to enter the son as “Carlos Garcia Jr” (after the fields are combined). Of course only want to do this if #2 really is a Jr (or III or whatever). Middle Name or Middle Initial will also often help you out.

2: Test for other distinguishing values

Similar to preceding, but this time check validation on the SSN, DOB, or Email address. But these tests are tricky in my experience because sometimes the user who’s entering the duplicate HAS the SSN or DOB info that wasn’t available to the person who first entered this person into the database last month.

3: Provide a ‘Confirmed Unique’ flag for users to tick

But what if you really have (say) two completely different people both named “Carlos Garcia” – and no other way to distinguish them? In that case, you will probably want to add a field named something like “Confirmed Unique” that can be edited to permit validation to succeed despite a non-unique value.

There are eleventy-seven other ways you could approach this, and I can think of one or two that are simpler than this, but here’s how I did it in Tadabase:

  1. Created field in my Customers table named Name Unique? This is a Selection field with three options:
    • Name Presumed Unique
    • Name Confirmed Unique
    • Name Confirmed Duplicate
  2. Configured field so “Name Presumed Unique” is the default.
  3. Over in the form validation rules area of the Add Record form, I created this validation, requiring both of these conditions to be met:
    • Name is unique AND
    • Name Unique? = “Name Presumed Unique” (i.e. the default value)
  4. And I added a message to display if validation fails. (See below.)

If validation fails, user will see something like the screenshot below. If they know the name is unique, they can change the Name Unique? value to “Name Confirmed Unique”. NOTE that the best you can hope for is that the users will take the trouble to find out whether they’re entering a new person or not. You have to give them the tools, but it helps a lot to have conscientious users who are willing to use them.

image
image1330×1303 50.4 KB

Addenda

If you’re wondering why I have a third option “Name Confirmed Duplicate” for the Name Unique? field, well, it’s so I can use that on the Edit Record Form to mark records that are found and confirmed to be duplicates during a data audit. Eventually we’ll want to filter for those records, export them, and take them out back and bury them.

For extra credit, you might also want to capture the user’s name and the date/time when they change the Name Unique? field value to “Name Confirmed Unique”. :slight_smile:

Finally, if you aren’t dizzy yet: Non-unique values aren’t always duplicates and the reverse is also true: unique values aren’t always truly different. Eventually you’ll see things like this:

  • Philip Smith
  • Phillip Smith

Referring to the same person (same address, same phone and email, etc.). And you’ll wish you’d gone into the restaurant business.

Trust but Verify

The moral of the story is Trust but verify. Try to hire conscientious people to enter data in your databases. But have a responsible person audit the data regularly.

Good luck.

William

3 Likes
6

I am just adding this video to demonstrate how to use Record Matching with Import Templates to check for duplicates while importing.

Please Note:warning:: Be aware that if you match using one field and other field values within the records are different, it will override the values with the new import.

1 Like