How to remove duplicate records w/o deleting the original record 1

[th201]

How can I remove duplicate records from FM without deleting one of the duplicate records? I want to remove the dupes but still want the original or one of the records to not be deleted in the database. Any advice would be greatly helpful.

 

[JeanW]

In which version ?

Look also to your structure,sometimes it's better to prevent making dupes.
There are little tricks to alert the user a dupe is 'in the make'.

HTH

 

[JeanW]

There are many ways to go for this, but here's one rough approach:

Step 1: Create a self- join relationship based on the field you wish to check for duplicates in. In this example, we are using the name field to check for duplicates.

Step 2: If you want to keep the oldest record, create a date field that auto enters the creation date. In the relationship window, choose sort related records, and sort by the creation date field.

Step 3: Create a calculation field that is equal to Status(CurrentRecordID).

Step 4: Now you are ready to define the duplicate “flag” field. The first part of the calculation should check if the field you are checking for duplicates is empty:
If(IsEmpty(Name), "",

The next part of the calculation checks if the record id of the current record is equal to the
record id of the related record based on our self- join relationship. If they are the same, it
is not a duplicate, otherwise they are a duplicate.
If(cRecordID=Duplicate::cRecordID,"","Duplicate"))

The final calculation will appear as: If(IsEmpty(Name), "",If(cRecordID=Duplicate::cRecordID,"","Duplicate"))

You’re done! You now can see which records are duplicates.

From here you can take over to delete the flagged records...

HTH

 

[th201]

Thanks for the example. Will it also work if all records were imported at the same time, meaning they all have the same creation date?

 

[JeanW]

In that case I would take a record number or record ID and check for the lowest value, which is the one to keep.

Adding a time to the datefield is no use because some records could be created in the same second....

It just depends on what the structure is, how you use the application. Look for an item that is unique in all circumstances and where you can work with...

HTH