Deleting duplicate records

[px2creative]

I try doing the self join relationship method described in help, but I get a message in the calculation box either that it cannot find the table or I am not entering data correctly. I want to delete duplicate records but not manually because there are many of them. Please help

[acantho]

we'd need more information to be able to help you.

You seem to be on the right road but without more information we can't figure out where the problem is.

I myself am trying to put something together but am running into a problem with my calculation. I'll eventually figure it out I'm sure.

[mr_vodka]

Create a calc field (cDupKey) to concat all the fields that you use as criteria to check what at a duplicate is. Then a script similar to this.

Sort Records [ No Dialog; cDupKey ]

Go to Record/Request/Page [ First ]

Set Variable [ $DupCheck; cDupKey ]

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

Loop

If [ cDupKey = $DupCheck ]

Delete Record [ No Dialog ]

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

Else

Set Variable [ $DupCheck; cDupKey ]

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

End If

End Loop

[m-sky]

I tried the self-join technique to identify duplicates, too, but, following the instructions very carefully, I'm only able to get "duplicate" as the identifier for all records. There seems to be something missing in the explanation.

I'd like to use this as a simple automatic way to identify a duplicate during data entry without having to run a script. Presumably one could display the field value "duplicate" in a bright color and leave the unique value empty.

Has anyone successfully recreated this technique without a script?

[Lee Smith]

Take a look at this Link, and the file I mentioned and linked.

Lee

[m-sky]

? am I missing something? I don't see a file linked to your response on that page, and you seem to be talking about an active search, not a passive relationship. Repeat: I don't want to run a script, I simply wish to see a flag when I have a duplicate, and the technique in the Help file suggests this is possible.

in troubleshooting, I displayed both related fields; the latter fails (which results in "duplicate") because it reports . Perhaps because my key field is a concatenation of values from 3 tables?

[LaRetta]

We can't help you without specifics about your setup.

[m-sky]

[i created a dummy file with limited fields and technique actually works well]

OK - so a LITTLE more detail: I have a media library database. To keep it simple (=cheap for the client) it has one main table for most of the data, a media/category table to generate media-specific categories through the relationship; a subjects table of unique subjects; a join table to allow a separate portal to display related subjects; and a self-join, as in the FileMaker help example.

The unique ID field is in the main data table and is a calculation which is a concatenation of an id for the record, and id for category/medium, and an id for unique subject. The unique ID field is the key field for the self-join.

The self-join related unique ID displays the error , thus the CkDup calculation field always displays "duplicate" because the calc is If (count = self_join::count, "unique", "duplicate), where count is a serial number field, auto-incremented.

I'd just like to know what I'd look for to troubleshoot the error - is it the two additional relationships? is it because of a dynamic calc?

[m-sky]

just so I'm clear, I've also attached a working sample of the FileMaker Help concept that works.

dupe_test.fp7.zip

[acantho]

The file you left seems to indicate unique and duplicates

Is this a correct file or one you need help with?