Calcing a field to purge duplicates

[Ron Lindeboom]

How do you purge duplicate values with a field? For example: We are holding a tradeshow in Los Angeles next month and there are duplicate registrations, email addresses, etc.

First, in the short run, I need to send out a thank you letter with some of the last minute show add-ons, etc., and I don't want to send out more than one to the attendees. So a simple purge duplicates on the email field would be appreciated. Know of one???

Secondly, the MySQL database that the guy set up for us allows duplicate records and so I'd really like to purge duplicate records without having to manually go through them all one by one by hand, there will be 3,000 or more good ones by show time, February 18-20.

Any ideas???

Your help would be appreciated.

Ron Lindeboom

creativecow.net

[jasonwood]

If you put an exclaimation mark in a field in find mode, it finds all records in which that field is not unique.

So you could do something like this:

LOOP

Enter find mode

Set field, email, "!"

Perform find

#this will produce a big long list

set field, globalTemporaryEmail, email

#this remembers the first email address

enter find mode

set field, email, globalTemporaryEmail

perform find

#now you see all the records with that same email address

delete record (or maybe you just want to clear the field)

#this deletes the first one (there may still be duplicates of that address, but you'll catch that later.

END LOOP

[andygaunt]

Hi,

Create a relationship from eMail Address to eMail Address fields ( making a self relationship, lets call this SelfJoin)

Create a field RecordID (or use an existing serial number field)

Then, create a calculated field (lets call it cPrimaryRecord, result of a number)

Case(RecordID = SelfJoin::RecordID, 1,0)

This will tag the first record with a 1, then you can search for the 0's and delete, or just search for the 1's and then send out your email.

A lot easier than scripting, and quicker too.

[jasonwood]

This will tag the first record with a 1

What stops it from tagging both duplicates?

[andygaunt]

Did you try it?

You can also do If(Min(SelfJoin::RecordID) = RecordID,1,0)

However, this one does not tag a record with anything if it does not have duplicates, if it does, it tags the first record with a 1 and the remainder with a 0.

[jasonwood]

Did you try it?

no, I just don't understand how it works.

It seems to me that both of the duplicates would have a valid self relationship to eachother, so wouldn't they both get tagged?

[Ron Lindeboom]

Very cool technique. Worked like a charm. Took me a few minutes to gain the simplicity of mind to grasp it, but once I did -- flawless.

Very, very cool little trick, Andy -- you earn Boomie's eternal gratitude and a free pass to our media professionals conference and expo if you want one. (We have a killer Final Cut Pro track if you use it at all.)

The best always,

Ron Lindeboom

creativecow.net

[djgogi]

no, I just don't understand how it works.

The reason is that in case of one to many relationship (and self join could be of that type), when refering to some related field you are ponting to the field of first related record.

dj