AnsweredAssumed Answered

Deleting duplicate records from found set

Question asked by JohnWolcott on Oct 19, 2011
Latest reply on Oct 20, 2011 by JohnWolcott

Title

Deleting duplicate records from found set

Post

   I suspect this is a fairly common problem, but I couldln't find a script to do it in either the knowledge base or on this forum so I created two different scripts to do the same thing.  I'd like advice on which is better, which hopefully will include some tips on building better scripts.  Version 1 uses a global field and Version 2 uses a variable for the field that contains duplicate values, in this case a field called "Names".  Is there a preference?  Version 1 is 12 lines long which is one line shorter, but Version 2 seems a bit cleaner or more elegant.  I tested both scripts on a found set of 7,500 records, and both took 1:32 so there doesn't seem to be any difference in execution times.
  The database consists of grades for students for each term for the last 5 years (54,001 records).  I need to know how many students were enrolled each term. Since a student may take more than one class in a term, when I seacrh for all students in a given term, I have many duplicates.  The scripts get rid of the duplicates so I can print out a list of names of students enrolled in each term.

Version 1

Set Error Capture [On]
Sort Records [Restore; No dialog]  (this sorts on the field "Name")
Go to Record/Request/Page [First]
Set Field [Grades::g_name; Grades::Name]
Loop
  Go to Record/Request/Page [Next; Exit after last]
  Loop
    Exit Loop If [Grades::g_name=/ Grades::Name]  (not sure how to show "Not Equal" other than =/)
    Omit Record
  End Loop
  Set Field [Grades::g_name; Grades::Name]
End Loop

Version 2

Set Error Capture [On]
Sort Records [Restore; No dialog]  (this sorts on the field "Name")
Go to Record/Request/Page [First]
Set Variable [$_name; Grades::Name]
Go to Record/Request/Page [Next; Exit after last]  (Just in case there is only one record found)]
Loop
  If [$_name = Grades::Name]
    Omit Record
  Else
    Set Variable [$_name; Grades::Name]
    Go to Record/Request/Page [Next; Exit after last]
  End If
End Loop

Outcomes