3 Replies Latest reply on Aug 13, 2010 12:20 PM by SireeshaVaranasi

    Self-Join to Identify duplicate records not working

    SireeshaVaranasi

      Summary

      Self-Join to Identify duplicate records not working

      Product

      FileMaker Pro

      Version

      10

      Operating system version

      Windows XP SP3

      Description of the issue

      Hi,

      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,
      Sireesha.

      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.