Remove Duplicate Records
I know I can find duplicate records using the "!" operator.
But how do I remove the duplicates (while keeping one record for each duplicate)?
I thought about the clone/import thing before I posted here, because I was looking for a more elegant solution.
I looked through this script one more time, and had an epiphany:
Something I often forgot about newer versions of FileMaker (been using since at least version 3) - after performing a sort, you have to insert a go to first record step.
After inserting that script step, it works perfectly.
Thank you very much for your help.
For those that search for this issue here is my final script:
Perform Find [FieldWithDuplicateValue=!]
Sort Records [FieldWithDuplicateValue]
Go to Record [First]
If [ GetNthRecord ( Table::FieldWithDuplicateValue ; Get ( RecordNumber ) - 1 ) = Table::FieldwithDuplicateValue // you have a duplicate]
Set Field [Table::DuplicateFlag ; 1 ]
Go to Record/request/page [ next ; exit after last ]
Enter Find Mode
Set Field [Table:: DuplicateFlag ; 1 ]
Set Error Capture [on]
Delete All Records
script. run a loop> enter find mode> search for duplicates the way you were doing it "!"> if statement that if there is more than one record > go to the last record and delete it.
If there are more than one duplicate, run the script again.
Perform your find with the ! operator. Sort the records on the same field so that duplicates are grouped together.
THen enter your loop and loop through your records. You can delete duplicates in the loop or you can use set field to "mark" them and then, after the loop is completed, find and delete all "marked" records.
Your loop can run something like this:
Loop If [ GetNthRecord ( Table::FieldWithDuplicateValue ; Get ( RecordNumber ) - 1 ) = Table::FieldwithDuplicateValue // you have a duplicate] Set Field [Table::Flag ; 1 ] End If Go to Record/request/page [ next ; exit after last ]End LoopEnter Find ModeSet Field [Table::Flag ; 1 ]Set Error Capture [on]Perform FindDelete All Records
listen to phil :)
I tried Phil's method.
I added a Perform Find FieldWithDuplciateValue = ! and Sort by FieldWithDuplciateValue step at the beginning of the script and removed the "Delete All Records" step so that I could verify the results before committing.
So then I performed the script and it returned 38 of 663 records marked "1"
As a verification, I created another layout with a sub-summary part, broken by the FieldWithDuplciateValue and placed the "Flag" field on the layout as well to verify that it marked all but one occurrence of a duplicate record.
I did a manual search for FieldWithDuplciateValue = !, and that found 303 records.
So already, something isn't working.
First, I'd put a pause after the find and sort steps and then check to see if the correct records were found.
If it appears to be finding all the duplicates, I'd then either use FileMaker Advanced's Script debugger or insert a pause inside the loop and watch the script step through the records to see if anything doesn't appear to work correctly.
Here's another trick you can try:
Save a clone of your database.
Open the clone and use a validation rule for the field that can containe duplicates to specify Unique values, validate always.
Now import the records from your original file into the clone.
During the import, the duplicates will be automatically excluded.
Good catch. Should have spotted that one myself. Maybe its because I didn't get my usual caffiene fix this morning...
Retrieving data ...