Avoid duplication of records - help please!

[Ginette D'Silva]

Hello all,

I'm a bit of a novice at building databases from scratch. Although I have took the available courses in my town, I still struggle with calculations and scripts. Right now, I'm working on a contact database and I want to avoid having the same contact entered more than once. I'm guessing it is a calculation set in the define field. What I would like is for a warning signal to come up when a new record is created and a combination of first name and last name is entered that matches a contact that is already in.

I would really appreciate some help in figuring out how to go about this. Thank you so much!

[Ender]

Although you could use a validation calc in the Validation section of those field definition, I don't really care for such validations. There are a couple problems:

1. Sometimes a duplicate name is just another person with the same name. If your validation is strict it might erroneously stop the entering of the second person's name. Users might resort to putting numbers or other garbage in the fields to override the validation.

2. The validation may not catch a duplicate contact if A. their name has changed, or B. the spelling is incorrect or inconsistent ("Mike" and "Michael", etc.).

3. If a message comes up during data-entry that the name is already in there, it's coming after the second record has been created and it doesn't really give the user the opportunity A. examine the other record to see if the new one is indeed a duplicate, or B. to delete the duplicate and jump to the other record.

In my opinion, it's better to give the user the opportunity to Find the contact first, using a variety of fields, then allow the creation of the new record. It's not fool-proof, but it helps.

[Ginette D'Silva]

Thank you Ender for such a quick response!

I see what you mean. This might be why I haven't been able to find something to help me on this yet. Perhaps it is not a popular way of entering records.

What if only a warning would come up that would be easy to override? Perhaps a script?

[Ender]

Well, validations CAN optionally be overriden, but that doesn't solve all of those issues.

A validation script is another option. You can use a self-join based on the combination of the fields required to be unique. Add another criteria where ID <> ID to eliminate the possiblity of the new record showing in the self-join. With this relationship, if there are related records, then there's at least one record that has the same values in those fields. You can then have your script either give a message, or show those records in a portal and let the user decide what to do. You'll want to include some mechanism to remove the new record if the user decides to use the original instead. Another option is to use a set of corresponding globally stored fields, have the self-join start from those, and then only create a new record if the validation script finds no duplicates.

[Ginette D'Silva]

Happy Wednesday to you Ender!

I had decided to take on your advice of not bothering about validating records to avoid duplication and just encourage those who will enter records in the database to check if there is already a record for that contact.

However now I'm curious about what you have said in your previous post:

Another option is to use a set of corresponding globally stored fields, have the self-join start from those, and then only create a new record if the validation script finds no duplicates.

Would you mind expanding on this? Remember, I'm a bit of a novice at this stuff :

[Ender]

In versions prior to FM7, you'll need to concatonate the fields that are going to be part of this AND condition. You'll need a calc for both the parent side and for the child side match key:

ParentKey (calculation, text result) = gNameFirst & " " & gNameLast

MatchKey (calculation, text result) = NameFirst & " " & Name Last

The relationship is then

YourFile::ParentKey = SelfJoin by Name::MatchKey

If I were doing things this way, I might also do a separate self-join on the Phone#:

YourFile::gPhone = SelfJoin by Phone::Phone

The users would type the Name and Phone info in the globals and hit a button that executes a creation script that looks something like:

If [ SelfJoin by Name::RecordID ]

Show Message [ "There is already a record with this Name.", "View Them", "Create it Anyway", "Cancel" ]

...

Else

If [ SelfJoin by Phone::RecordID ]

Show Message [ "There is already a record with this Phone#.", "View Them", "Create it Anyway", "Cancel" ]

...

End If

End If