Creating a field that warns of duplicate entries

[Philip Jenks]

Hi

I'm entering customer names in a database, and want to make sure that I don't enter duplicates. From a FirstName and LastName fields, I've created a calculated field called Full Name which just puts the two together. It looks like you can't define a calculation field to be unique (or can you?), so i thought I'd try and create a warning sign that comes up when a duplicate Full Name occurs. I've created a container field called Duplicate Name, and the idea was to define it so that when the Full Name field is a duplicate, "This is a duplicate!" is displayed in the Duplicate Name field. Only trouble is, I don;t know how to write that definition. Anyone got any ideas? And is this a barmy way to be doing it anyway? Grateful for any advice, thanks.

[Søren Dyhr]

Count( over a selfjoin on the field, can be set to switch in the image via a case( or If( statement.

--sd

[MoonShadow]

Well it doesn't need to be a calculation in vs. 7. You can use a standard text field with Auto-Enter (calculation, unclick 'Do Not Replace ...') and apply validation to that. Calculation would be simple:

FirstName & " " & LastName

However, this won't necessarily catch all duplicates. The same person can be entered as Bill Smith, Bill S. Smith, Bill Smith, Jr. Users will even try Mr. Smith, Bill Smith III, and Bill Smith 3rd. Users can be a tricky bunch.

Just keep in mind that it's hard to restrict them on something so wide open to variation. One option is to validate FirstName with WordCount(FirstName) = 1 and LastName via calculation WordCount(LastName) = 1. Then your FullName calculation will at least have a shot of dup-checking for you. But what if you have multiple Bill Smiths? And hyphenated names will fail validation and would need to be trapped. Many don't like FM's field validations. But I find it works quite well if applied in the proper situation. I do not believe that attempting to restrict unique name entries is a very good idea.

If you are planning to use FullName as a match field for critical relationships (and aren't they all) and that is why you want the FullName unique, you'll run into all kinds of problems. There are many ideas around for identifying 'unique people' - based upon PatternCount(), exploded permutations custom functions and other FIND SIMILAR techniques, usually combining part of phone or zip. All of these techniques can then present the User with a portal of possible matches.

It really takes human eyeballs to determine if two people might be the same based upon many factors. What Soren is describing is a self-relationship on both fields or on = FullName, then use calculation (text) with: If(Count(selfjoin::any nonemptyfield) > 1; "Possible Duplicate"). But it doesn't have to be a container and it won't protect from the above issues regardless.

[Philip Jenks]

Moonshadow, Soren

Thanks very much for your help. Extremely kind of you. I need to think about your advice, but between you I am sure you have solved the problem. Thanks again. Philip