Hi,

I work for a business and we are setting up our database. We created all of our records but somehow duplicates for every record were made. Now it seems that the best way to eliminate this problem is to create a script based on each record's Unique ID.

Unfortunately we did not create any Unique ID's when we built the database. I went in and created a new field to be Auto-Enter with a Serial number but it did not generate serial numbers for the current records (the field is there, but it's empty).

How can I have this be retroactive for all records in the database? Or, is there an easier way to get rid of multiple records that doesn't involve deleting them one-by-one (we have thousands)? Answers to both of these questions would be much appreciated. Thank you.

All quite do-able but will require a little care. And of course - make backup copies before making changes. Regarding the duplicates - there is the age old question of what exactly is a duplicate? They are real exact duplicates? Or similar but variations in spelling? Or perhaps one record has more recent information - which one? Deletion of duplicates can be scripted but first you need to make sure you know what you will be looking for.

Adding serial numbers after the fact is super easy. Just go to your first record (show all records first) and go into the field your using and go to the records menu and choose replace field contents. This allows you to replace the contents of this field in every record of the found set. (this will save your life many a time) You'll get a dialog that gives you the option of replace with a serial number with a given start number and increment. Be sure to check the box to update auto entry. That will set the number for the next new record created by your auto entry setting.

Now cleaning up the dups is another issue that may be better solved before doing this. The fact is you already have a unique key, the record ID. All records in filemaker have a record ID (not the same as record number) The question is were the records duplicated one at a time or in mass.

If you create a new calc field and use Get(RecordID) to set it to the record ID you can sort by that to get the original creation order. (filemaker pretty much sorts by Record ID by default but this way you can see it)

If they were created in mass (imported the data twice etc) the list will show your whole DB then start over. all you need to do then is do a find for record IDs higher then the last record of the first copy.

If they were done one at a time you'll see each record followed by its duplicate. Thats a little trickier. But all you have to do is make another calc field to indicate if the record ID is odd or even. then do a find for evens (or odds doesn't matter) and delete all those.

once things are hunkey dory just delete the extra fields.

Finding and deleting duplicates after they are entered can be a problem. Take a look at this file by Database Pros Link

Also, do a search of this forum for your key words of [color:blue]+finding +duplicate +Records or [color:blue]+deleting +duplicate +Records for other Threads on this topic.

Lee