1 Reply Latest reply on Feb 4, 2009 9:41 AM by TSGal

    Newbie: How do I detect duplicate names and email addresses in a list of users using self join?

    JeffInTokyo

      Title

      Newbie: How do I detect duplicate names and email addresses in a list of users using self join?

      Post

      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.

      Thanks.

      Jeff