Newbie: How do I detect duplicate names and email addresses in a list of users using self join?
I am a relative newbie using Filemaker Pro7. I have a list of Users, with the following fields: email address, full name, and full name in Japanese. If a duplicate record of any of these 3 fields are entered, (or imported), I would like to flag the duplicate entry as a possible error. (Of course 2 users with the same full name may or may not be duplicates, that is up to the operator to decide).
To detect duplicates, I have created 3 self join tables, 1 to match email address (Users Self Join), 1 to mach full name (Users Self Join 2), and 1 to match Japanese full name (not shown). (FM would not left get away with just 1 self join).
To determine a duplicate email address, I have a calculation field with the following calculation:
If ( IsEmpty ( Email Address ); 0; Count ( Users Self Join::Email Address ) - 1)
Any number greater than 0 will imply that there are multiple Entries with same Email address.
I have a similar calculation for detecting a duplicate full name.
If ( IsEmpty ( Full Name No Space); 0; Count ( Users Self Join 2::Full Name No Space) - 1)
I have a similar calculation for full name in Japanese (not shown).
For whatever reasons, the detection of email address is working like I expect. But the detection of full name and Japanese full name are not working at all.
Looking at the example in the help contents, I see they recommend keeping a Serial Number counter. But I dont see what benefit that brings. I just want to find duplicate names and email addresses, not duplicate serial numbers.
Any help would be appreciated.