Eliminating Duplicates

[Elliot Jolesch]

I am converting an Excel spreadsheet to FM Pro 7. There are about 21000 records but lots of duplicates. There are only two fields, Job ID, and Job Description. I need to eliminate all of the duplicate Job ID's but can't figure out how to do it. I have done a find for duplicates under Job ID, but it finds all of the duplicates, and if I delete found, it deletes all of the records. I need it to keep one record and delete the dups. Can someone offer any suggests on how to do this, other than manually.

TIA

Elliot

[Fitch]

Easiest way is to create a looping script that omits the first duplicate:

Marking Duplicate Records the Easy Way

Finding a Specified Number of Duplicates

Deleting Duplicate Records Using a Self-Join Relationship

[Elliot Jolesch]

Thanks.

That did the trick

Elliot

[wmugrad28]

Hello,

I'm having trouble with this as well. I tried using the article http://www.filemaker.com/ti/108612.html and I am having trouble getting it to working correctly.

I have a table that temporarily holds data called temp_CE_PM. It only has one field in it that is used called CE_PM_FORKEYID_PM. The data is always PM followed by a number (ex. PM108). I have a script that puts this data in this table and right now it has three values in it (PM108, PM112, and PM112). The values could be any number and may be unique or have many duplicates. I'm trying to find a script that will leave me with just one of each value. So for my example the final values would be PM108 and PM112.

Here is my script:

Go to Layout ["temp_CE_PM_2" (temp_CE_PM_2)]

Loop

Loop

Exit Loop If [Count(temp_CE_PM_2::CE_PM_FORKEYID_PM)=1]

Go to Portal Row [select;First]

Delete Portal Row []

End Loop

Go to Record/Request/Page [Next; Exit After Last]

End Loop

I created the self join relationship relating the two CE_PM_FORKEYID_PM fields. My two TO are called temp_CE_PM and temp_CE_PM_2. One thing that I don't understand about the self join is that when viewed before this script is run is that my portal in the second TO shows the first record as PM108, the second record as PM112 and PM112 (on a second line), and the third record as PM112 and PM112 (on a second line). After the script the three records look exactly the same and there are still three records!

Any ideas?

Greg

PS: As another example of the number thing I could have values: PM90, PM91, PM91, PM91, PM100, PM100, and PM121. I would like the script to leave the records/values of: PM90, PM91, PM100, and PM121.

[-Queue-]

It might be easier to use Go to Related Record [show only related; "yourRelationship"] to isolate the related records and perform the loop on the actual records instead of the portal. Without seeing your file, I can't understand why you would have the double-line issue.

[wmugrad28]

I tried changing some of the portal references without any luck. I've attached a sample of what I had/have.

Greg

Feb8Test2.ZIP

[-Queue-]

Since your self-relationship is based on CE_PM_FORKEYID_PM. A portal displaying related records naturally has the same number number of rows as there are matching CE_PM_FORKEYID_PM records. You would want it this way, anyway, in order for your loop to work. If there were only one row showing and there were duplicates, the loop would exit each time.

I think, however, that there is an easier way to do this. Create a serial field with an auto-enter number result. Run a replace on the existing records to update the field and then change the next auto-enter serial value in the field's definition so that you don't end up with duplicate serials when the next record is created. Then, create a calculation number field of serial <> temp_CE_PM 2::serial and perform a find for 1 in this field in your temp_CE_PM layout. All found records will be duplicates and can be deleted.

The reason this works is that relationship::field refers to the first related record's field, according the relationship's sort order. If there is no sort order defined, then it's based on creation order. So, relationship::field always refers to the same record's id--regardless of whether you're on the original or a duplicate record, it will refer to the original record's field. Testing whether the related serial does not equal the current record's serial then produces a zero for the original (because the statement is false) and 1 for each duplicate (because it is true).

Oh, I would also encourage you to remove the 'delete related records' option from your relationship definition. This can cause serious problems in a self-relationship, if you are not careful with it.

[wmugrad28]

Run a replace on the existing records to update the field and then change the next auto-enter serial value in the field's definition so that you don't end up with duplicate serials when the next record is created. Then, create a calculation number field of serial <> temp_CE_PM 2::serial and perform a find for 1 in this field in your temp_CE_PM layout. All found records will be duplicates and can be deleted.

I'm sorry... do you speak English? Just kidding... however, I really don't understand what you just said.

Can you help me by typing the script steps? It sounds like this script would be using several functions I haven't used before (like Replace Field Contents and others).

Thank you so much for your help so far.

Greg

[-Queue-]

Sure.

Put your serial field on the layout, enter Browse Mode, show all records, and put your cursor in serial. Then select Records -> Replace Field Contents, 'Replace with serial numbers', 'Initial value' 1, 'Increment by' 1, 'Update serial number in entry options'. This last option is one I hadn't noticed before. Selecting it should eliminate the need to change the field definition after you run the Replace.

You don't need to script this since it will only be done once.

Now, remove the serial field from the layout and create a calculation with a number result of

serial <> temp_CE_PM 2::serial

Put this calc on your layout, enter Find Mode, enter 1 in the field, and click Find. The found set should consist of only duplicates.

[wmugrad28]

Thank you!!!

At this point I'm not sure how it is working without studying it, but it works! Later, I'll read into it further so I can use these features again.

Greg

[wmugrad28]

I've got another need for this type of technique, but I'm not sure how to pull it off. Let's say I have 30 related records and they each have their own unique ID. A number of these 30 can have very similar looking data; however, there is a field "Number_and_Text" that I would like to do something special with. Let's say that some sample data from the 30 records (for the field) is:

Record 1 value: Repair

Record 2 value: 0305

Record 3 value: 0348

Record 4 value: 0305

Record 5 value: Repair

Record 6 value: 1002...

These values are part of a join parent table. I'd like to be able to put the values into groups (Repair, 0305, 0348, 1002) and then find ALL grandchildren which are parts (a few tables away) for that group.

Note: The number of groups will likely vary each time I try and find the grandchildren.

Can this be done without doing manual entry? I would like a seperate screen for each group though. I think it is possible to at least print this as a report with each of the groups parts on a separate page, but even better would be to view it using relationships and then print all groups automatically. I'm stumped!

Greg

[comment]

You might want to read this .

[wmugrad28]

Thanks! I'll check it out.

Greg