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.
Thank you for your post.
It sounds like you have everything set up properly.
Can you give me a Full Name example that is not working? I will try to duplicate it here.