AnsweredAssumed Answered

Self-Join to Identify duplicate records not working

Question asked by SireeshaVaranasi on Aug 13, 2010
Latest reply on Aug 13, 2010 by SireeshaVaranasi


Self-Join to Identify duplicate records not working


FileMaker Pro



Operating system version

Windows XP SP3

Description of the issue


I am trying to identify duplicate records in a  particular table.Steps to reproduce are below.

What am I doing wrong?Can someone please assist me?

Thank you,

Steps to reproduce the problem

I have a table called Prospects where there is a field called CRM_PRODUCT_TRAK_ID.If this field has duplicate values,I would like to keep the first record and delete the rest of the duplicates.

I created a self join called Find Duplicates by choosing the Prospect table and the matching field is CRM_PRODUCT_TRAK_ID in both tables.

I then created a field in Prospect table called Counter which is an Auto Serial Number field.I also created a field called CheckDuplicates which is calculated as follows

If(Counter = FindDuplicates::Counter;"Unique","Duplicate")

I exposed the Counter and CheckDuplicates field on my Layout and clicked on the Counter field and did Replace Field Contents->Serial NUmber.The Counter field got populated with serial numbers.However the Check Duplicates field is empty.I don't see any values in this field for any record.I can see clerly that there are two duplicate records but none of them are marked Unique or Duplicate.

Expected result

CheckDuplicates field should have a value of Unique or Duplicate.

Actual result

CheckDuplicates field is empty.