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")


 Else


  ` 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



DIFFERENCE("allRecords";"Duplicates";"ResultSet")



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




 ` Remove the set from memory

CLEAR SET("Duplicates")

CLEAR SET("allRecords")

CLEAR SET("ResultSet")