Mark duplicates in the same table

[Suha]

Can someone tell me what is wrong with this way to find duplicates. It worked with me for 1 table only and not working for others.

1. Define a self-join relationship. A self-join is a relationship is a relationship where both match fields are defined in the same table. 
   
   Use your chosen identifying field as the match field in both tables in the relationship. Drag that field on top of it's self.   Name the relationship occurrence "Same". When you define a self-joining relationship in the relationships graph, FileMaker Pro generates a second occurrence of the table upon which you are basing the self-join. FileMaker Pro does this to prevent the relationships from forming a cycle, which would make it impossible to evaluate. The primary record is the first matching record according to the sort order defined in the relationship.
    
2. 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 = Same::Counter ; "Unique" ; "Duplicate")
    
3. Choose Records menu > Show All Records.
4. 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.
    
5. Perform a find for Duplicate in the Check Duplicates field.
    

[Kris M]

duplicates.zip

[comment]

Can someone tell me what is wrong with this way to find duplicates. It worked with me for 1 table only and not working for others.

There is nothing wrong with it (although better ways do exist). If it works in one table, but not in another, then the problem is not with the method. Either you have made a mistake in the implementation, or the data is not what you think it is. How exactly did you determine that it is "not working"?

Hint: perform a find for ! in the field of interest. That will find all duplicate records (original and duplicate alike), and you can see if that matches your expectations.

Edited September 3, 2015 by comment

[Suha]

My fields have text as a unique value/ primary key in both tables. unfortunately, it is an old db that I can not change much in it and I have to deal with what in it. In the first table it tells where are the duplicates and unique values but in the second everything is unique. There are duplicates with 20 records and contains exact data.

[Wim Decorte]

Not sure what you are saying here... in your approach you add a relationship, some fields, but then in your last post you say that you "can not change much"

What Comment is saying is that your approach makes each entry look like unique but it clearly is not.  Because clearly you can see duplicates.  So the data you are seeing is not the data that is there.  Could be trailing returns, leading or trailing invisible characters,...