Log In    |    Knowledge Base    |    4D Home
Tech Tip: How to filter duplicate records
Published On: May 16, 2002

Compatibility: 6.5.x , 6.7.x, and 6.8.x

One way to filter all duplicate records and select only distinct records is to work with sets. Below you will find a sample method which does this. All you need to do is replace the fields and table names. Also, if you are filtering for a selection of records, do not include the all records command.

CREATE EMPTY SET([Table 1];"Duplicates")

 ` Create an empty set for duplicate records

ALL RECORDS([Table 1])

 `Use All Records only if you want to filter all records

 `Do not use if you want to filter a selection

CREATE SET([Table 1];"AllRecords")

 ` Select all records

 ` Sort the records by $Field1 $Field2, and $Field3 so

 ` that the duplicates will be next to each other

ORDER BY([Table 1];[Table 1]Field1;>;[Table 1]Field2;>;[Table 1]Field3;>)

 ` Initialize variables that hold the fields from the previous record

$Field1:=[Table 1]Field1

$Field2:=[Table 1]Field2

$Field3:=[Table 1]Field3

 ` Go to second record to compare to first

NEXT RECORD([Table 1])

For ($i;2;Records in selection([Table 1]))

 ` Loop through records starting at 2

 ` If the $Field1, $Field2, and $Field3 are the same as the

 ` previous record then it is a duplicate record.

 If (([Table 1]Field1=$Field1) & ([Table 1]Field2=$Field2) & ([Table 1]Field3=$Field3))

  ` Add current record (the duplicate) to set

  ADD TO SET([Table 1];"Duplicates")


  ` Save this record’s $Field1, $Field2, and $Field3

  ` for comparison with the next record

 $Field1:=[Table 1]Field1

 $Field2:=[Table 1]Field2

 $Field3:=[Table 1]Field3

 End if

  ` Move to the next record

 NEXT RECORD([Table 1])

End for


USE SET("ResultSet") `Use only records that are not duplicates

 ` Remove the set from memory

CLEAR SET("Duplicates")

CLEAR SET("allRecords")

CLEAR SET("ResultSet")