Tech Tip: Find records with duplicate values in a field

PRODUCT: 4D | VERSION: 14.3 | PLATFORM: Mac & Win

Published On: April 29, 2015

Below is a utility method to return all duplicate values within a field. The duplicate records will become the new current selection.

// Method: GET_DUPLICATE_VALUES
// $1 - Pointer to the field
// ----------------------------------------------------
ARRAY TEXT($arrSelection;0)
ARRAY TEXT($resArr;0)
C_POINTER($1;$tbl_ptr;$field_ptr)
C_LONGINT($elem;$i;$arrSize)

If(Count parameters>=1)
  $field_ptr:=$1
  $tbl_ptr:=Table(Table($field_ptr))

  ALL RECORDS($tbl_ptr->)
  SELECTION TO ARRAY($field_ptr->;$arrSelection)
  $i:=1
  $arrSize:=Size of array($arrSelection)
  While ($i<=$arrSize)
    $elem:=Find in array($arrSelection;$arrSelection{$i};$i+1)
    If ($elem#-1)
      APPEND TO ARRAY($resArr;$arrSelection{$i})
      While ($elem#-1)
        APPEND TO ARRAY($resArr;$arrSelection{$elem})
        DELETE FROM ARRAY($arrSelection;$elem)
        $arrSize:=$arrSize-1
        $elem:=Find in array($arrSelection;$arrSelection{$i};$elem)
      End while
    End if
    $i:=$i+1
  End while
  QUERY WITH ARRAY($field_ptr->;$resArr)
End if

Below is an example of using this method to find the duplicate values in Table_1 Field_1.

GET_DUPLICATE_VALUES(->[Table_1];->[Table_1]Field_1)

Commented by Jelle Helsen on May 6, 2015 at 8:10 AM

You can do this with 1 sql query: select field_1, count(*) from table_1 group by field_1 having count(*)>1 into :$somearray query with array([table_1]field_1;$somearray)