duplicates not all found

[Bruce105]

Hi all, I've generated a somewhat large database from various excel files - I set up all the excel files before hand in the same font, text style etc as the database. I then import the data. One of the fields contains the info (addresses) in the concatenate column in the excel files. This "address" field is the one I need to work with, and I'm trying to find all duplicate addresses. I use the ! in the "address" field....and get a result of about 1/2 of what I expected. I can't seem make Filemaker pro "find" all the duplicates no matter what I do. I even had filemaker pro open the excel files and create a temporary database from which I could import from (instead of excel)..no luck - got the same results. Any ideas? Really appreciate all of your help. Bruce

[HBMarlowe]

Is it possible that some of your expected duplicates are not exactly the same? An extra space or comma, inconsistent abbreviation (Ave. vs Ave), etc. will not be revealed as duplicates. You might want to use the Trim function to remove leading or trailing spaces.

[Bruce105]

Many thanks...I checked a few known instances of 3 or 4 exact looking copies, but filemaker will only find two of them - the others were generated from different excel files, and imported into the database....I tried everything I know of to make sure all imports were the same format...I'm using filemaker pro version #8...and I probably need to upgrade....I'm not familiar with "Trim" - will look for it - appreciate all your help, Bruce

[sujat]

I found this in the Help section of FMP under Identifying duplicate values using a self-join relationship

 

Identifying duplicate values using a self-join relationship

 

This procedure identifies "extra" instances of duplicated records. You specify the criteria that determine which is the primary record.

 

This procedure uses a self-join relationship and a calculation field referencing the relationship to determine which records are duplicates.

 

To find duplicate records except the first instance:

 

1.

If you plan to delete the duplicate records that you find, make a backup copy of the file.

 

 

For more information, see Saving and copying files.

 

2.

Identify a field that determines a unique entity in your file.

 

 

For example, in a Contacts database, the Last Name field is probably not a good choice, because you might have several people with the same last name. Social Security Number is a better choice. You can also create a calculation field (returning a text result) that combines data in several fields to make a unique identifier. An example formula is First Name & Last Name & Phone Number.

 

3.

Define a self-join relationship.

 

 

Use your chosen identifying field as the match field in both tables in the relationship. For more information, see About self-joining relationships.

 

The primary record is the first matching record according to the sort order defined in the relationship.

 

4.

Define two fields:

 

Counter, a text field with an auto-entered serial number (select Serial number and accept the default values for Next and Increment by).

 

 

Check Duplicates, a calculation field with a text result, with the formula:

 

 

 

If(Counter = table1::Counter, "Unique", "Duplicate")

 

5.

Choose Records menu > Show All Records.

 

6.

Click the new Counter field, choose Records menu > Replace Field Contents, and Replace with serial numbers. Again, accept the default values. Select Update serial number in Entry Options, and click Replace.

 

 

This will assign a serial number to all existing records in your database. Serial numbers will automatically be entered in new records.

 

7.

Perform a find for Duplicate in the Check Duplicates field.

 

 

The first record in any series of duplicates now holds the value "Unique" in the Check Duplicates field, and all duplicate records within the same series are marked "Duplicate".

 

Important Records with no value in the match field will be flagged as duplicates. Once set up as above, this system will identify duplicate records automatically as they are created.

 

I tested it with a few records and it is marking the first instance as Unique and all duplicate recrods as Duplicate.