Finding and deleting duplicates

[steveroberts]

Hello,

I've exported a text file from my calendar app and have imported the data into FM 6 so that I can weed out duplicates.

There are 15,000 records, and each record has 9 fields (i.e. Date, start time, end time, event, notes, etc.), some of the fields are blank.

I've culled the list down to display about 6,000 records, which are either duplicates or triplicates of an adjacent record.

I need to get rid of the duplicates in the group that currently is displayed.

I'd appreciate any help in steping through the process to clean up the duplicates!

If you need more info, please ask. I am a FMP novice and may have left out something important.

Thanks in advance for any assistance.

SR

[Genx]

Make an additional calculation field to equal:

Date & " " & Start Time & " " & End Time etc. etc.

Then simply enter find mode, go to your new calc field and search "!" -- will locate duplicates

[steveroberts]

Thanks, GenX.

I'm concerned about deleting duplicates. Will the generated list contain only actual duplicates (in other words only 2nd or 3rd copies of the same record)?

I need a way to delete the multiple records while leaving one copy of the record intact.

Please advise.

Thanks!

SR

[Genx]

Okay, follow this link: http://fmforums.com/forum/showtopic.php?tid/179030/post/216003/hl/duplicate/

The scenario, is almost the same, except, instead of using variables, you will be using global fields (one for each value you want to compare), and instead of sorting by one field, you will sort by all.

Still confused?

Post again. :P

[steveroberts]

Genx. Thanks for this info.

A couple of questions:

1) Do I still set up the field as you described in the first post? Or is the script below to be used instead of your first post?

2) To be clear, is the info below to be placed in a calculation field or is it a script?

3) Does this run on the full list or only on the list containing duplicates, or does it matter?

4) So everywhere I see the term "variable" below, I should change it to read: "global field"? Not sure I understand this part.

Thank you very much.

Something like this should work:

Sort Records [ No Dialog; by field ]

Set Variable [ $value ; field ]

Go to Record/Request/Page [ Exit after last; Next ]

Loop

If [ field = $value ]

Delete Record [ No Dialog ]

Exit Loop If [ get(recordnumber) = get(foundcount) ]

Else

Set Variable [ $value ; field ]

Go to Record/Request/Page [ Exit after last; Next ]

End If

End Loop

[Genx]

Welll hmmm...

Okay, this is meant to be a replacement of the first post, however, if you create the field as i suggested, it will reduce your need for multiple globals... so i suggest you still create it, sort by it, and compare using that calculation field.

2) It's a script

3) Full list

4) Not exactly, simply replace Set Variable, with set field[GlobalFieldHere ; field], and then when calling, replace the variable value with your GlobalFieldHere field?

[Genx]

PS, always back up your files before you try scripts like this.

[steveroberts]

Ok, I've created the field you suggested, I called it "calculation". That worked fine.

Now, I'm onto the script as suggested.

I'm stuck at the "If[field = $value]" step.

Which field goes here? Calculation?

What is "$value"?

Not sure what you mean by the "global field." Is that the "calculation" field I created?

Thanks for your patience!

SR

Genx. Thanks for this info.

A couple of questions:

1) Do I still set up the field as you described in the first post? Or is the script below to be used instead of your first post?

2) To be clear, is the info below to be placed in a calculation field or is it a script?

3) Does this run on the full list or only on the list containing duplicates, or does it matter?

4) So everywhere I see the term "variable" below, I should change it to read: "global field"? Not sure I understand this part.

Thank you very much.

Something like this should work:

Sort Records [ No Dialog; by field ]

Set Variable [ $value ; field ]

Go to Record/Request/Page [ Exit after last; Next ]

Loop

If [ field = $value ]

Delete Record [ No Dialog ]

Exit Loop If [ get(recordnumber) = get(foundcount) ]

Else

Set Variable [ $value ; field ]

Go to Record/Request/Page [ Exit after last; Next ]

End If

End Loop

Genx. Thanks for this info.

A couple of questions:

1) Do I still set up the field as you described in the first post? Or is the script below to be used instead of your first post?

2) To be clear, is the info below to be placed in a calculation field or is it a script?

3) Does this run on the full list or only on the list containing duplicates, or does it matter?

4) So everywhere I see the term "variable" below, I should change it to read: "global field"? Not sure I understand this part.

Thank you very much.

Something like this should work:

Sort Records [ No Dialog; by field ]

Set Variable [ $value ; field ]

Go to Record/Request/Page [ Exit after last; Next ]

Loop

If [ field = $value ]

Delete Record [ No Dialog ]

Exit Loop If [ get(recordnumber) = get(foundcount) ]

Else

Set Variable [ $value ; field ]

Go to Record/Request/Page [ Exit after last; Next ]

End If

End Loop

[Genx]

Nope... okay, we are using a global field here to store a temporary value -- it is completely substituting the variable because it doesn't exist in FM 6...

In this case, we use the global to store the temporary value of the calc field in the current record so we can go to the next record and compare the two values...

So where it says "Set Variable [$value ; field]

we instead use:

Set Field[OurGLOBALFIELD (simply a text field with global storage turned on) - i'd offer more about how to use global storage in 6, but i simply havent worked with it ; your calc field]

Then where it says: If[ field = $value]

We instead use If[ OurGLOBALFIELD = your calc field ]

Hope this makes more sense.

[steveroberts]

Problem now is solved. Thank you very much for your assistance!

SR