The biggest problem with your script is that the finds completely change what records are in your found set. That then keeps the loop from enabling you to loop through all the records.
I think a better approach is to use ! in a find request to find all records with dupilcate values, sort the records by this same field to group them by the duplicate entries, then loop through this list to find and delete the duplicates.
Could you give an example of using !, i've never seen that being used before
If you enter find mode and put ! as the sole character in a field, then perform the find, FileMaker will find all records where duplicates exist in that field.
So if I have:
and perform a find with ! in this name field, I'll get:
I can then sort the records to get:
and then I can loop through the records and either delete the duplicates or mark them for later deletion.
I'm trying that just now, the problem is the records I'm searching through all have several fields each, I'm only looking for records that have exactly the same field contents bar the PK_ID.
I've created a CalcField that takes the data from all the required fields, I thought I could search for exact matches in that field but it's proving more difficult than I thought.
Can you perhaps describe in more detail why adding the loop to my script stops the find from working?
Using a ! to search in the calc field should work as long as you do not have any return characters in that field. There's a known bug that identifies duplicates on any one return separated item if another record has that one item in the field:
For More Information see: Find Duplicates (!) matches by duplicate paragraphs
This is one of many acknowledged bugs that can be found in the Known Bug List here in the Report an Issue section of the forum.
It can also be downloaded as a database file from: http://www.4shared.com/file/8orL8apk/FMP_Bugs.html
For your script that you've written, if you have FileMaker Advanced, enable the script debugger and use it to step through your script and watch how your found set and current record changes as the script executes.
If you don't have advanced, you should get it. In the mean time, insert a pause/resume step right after the loop step and run your script. Click continue or press enter to continue the script and watch how your found set changes.
Ok, using the !, I can see how it finds and sorts the records now so I can see all the duplicates grouped together.
Perhaps I'm looking at this in the wrong way, but if I can omit 1 of each of the grouped duplicates and leave 1, that would solve the problem. Is there any way of doing this?
It's annoying when you can see how to do it manually but trying to get a script to do the same steps.
Try this script:
Enter Find Mode  //clear pause check box
Set field [YourTable::cCalcField ; "!"]
Set Error Capture [on]
Sort Records [Restore ; No dialog ] //sort on cCalcField to group duplicates
Go To Record/Request/Page [First]
Exit Loop If [Get (FoundCount ) = 0 ]
If [ $PrevValue = YourTable::cCalcField ]
Delete Record [no dialog]
Set Variable [$PrevValue; Value: YourTable::cCalcField]
Show All Records
Sort [Restore ; no dialog] //optional, but a good way to see that the script worked