Delete Duplicates

[tallboy755]

Here is the senior... I run a photography studio. I mostly photograph high school seniors. I have a database with about 7050 seniors in it, 5058 of which are unique. Each record has a date and time that the senior is to be photographed. The duplicates are about 2000 students that are entered from a previous shoot. I need a script that will delete the duplicate record(s) with the older dates ...

Example ::

John Doe 7/2/03

John Doe 5/23/03

John Doe 3/18/03

I want only the john doe with the 7/2/03 date to remain in my file...

I am an amature when it comes to this stuff ... please help me!

...::: Also just another note to add - I don't know how many others on this site (if any) are in the same line of work as me. But if you are, please contact me and i will give you a copy of my program :::...

[Fitch]

This is a simplified script, which uses a global text field, gCompare:

Show All Records

Sort (by name)

..Set field (gCompare, "")

Loop

..If(gCompare = name)

....Delete record

..Else

....Set field (gCompare, name)

....Go to record(next, exit after last)

..End If

Exit Loop If (Status (CurrentFoundCount) = 0 )

End Loop

[tallboy755]

I am a little lost here .... whne you say ..If(gCompare = name).... what is the "name" supposed to be? also ... how does it keep only the most recent date ... like if i have the same record in my dbase twice ... One has a date for 5/9/03 and the other for 7/2/03 ... i want it to KEEP the 7/2/03

[tallboy755]

ok I got it fugured out ... however ... there does seem to be one small problem ... if the final record in the database is a duplicate ... it deleted all copies of it .... ?:?:?:?:

[Ugo DI LUCA]

AndyGaunt's method here.

1.Create a Selfjoin on the Name Field.

2.Sort the relationship by dates descending

3. Calculation

Case(Record_ID =:Selfjoin:Record_ID),1,0).

Make a find for the "0", so you can purge the duplicates to keep the good ones.

[Ugo DI LUCA]

In addition. I assumed the dates weren't entered in the order the records were created. If this is not the case, then you won't even need to sort the relationship, but you'd use

Case(Record_Id = Max(Selfjoin::Record_Id),1,0) with same effect.

[Fitch]

1. If you want to use my script, add another line:

...

Exit Loop If (Status (CurrentFoundCount) = Status (CurrentRecord) )

Exit Loop If (Status (CurrentFoundCount) = 0 )

End Loop

2. You may have already figured out, sort by name and date descending

3. You may have figured out, I used "name" above as a placeholder for whatever criteria you're actually using, which could be a calc such as (NameFirst & NameLast & Phone) for example.

4. I sure hope you figured out: backup your file before you go testing this stuff!!!

[tallboy755]

Fitch said:

This is a simplified script, which uses a global text field, gCompare:

Show All Records

Sort (by name)

..Set field (gCompare, "")

Loop

..If(gCompare = name)

....Delete record

..Else

....Set field (gCompare, name)

....Go to record(next, exit after last)

..End If

Exit Loop If (Status (CurrentFoundCount) = 0 )

End Loop

Is there a faster alternative than this??? i am aging while waiting for this thing to go through 9000 records every day!!!

[Fitch]

Every day? Hmmm... you might want to consider just keeping one record for each student, and keep their "shoot" records in a related file.

For now, to answer your question: before you run the script, or as the first step in the script, FIND the duplicate records. That way the script only has to check the found set instead of the whole file.