Tech Tip: Using the DISTINCT VALUES Command to Find Duplicate PK Values

PRODUCT: 4D | VERSION: 15.x | PLATFORM: Mac & Win

Published On: July 19, 2017

A use for the DISTINCT VALUES command is to use it to help locate and/or validate data when a duplicate primary keys field error is generated. Running the command on the primary key field will show if any duplicates were found in the command.

Below is a utility method, which will ease the process by reducing the array down to the duplicated values for easy of error handling and repairs.

// Util_Duplicate_Exist
//
// Description:
//   Searches a field for duplicate values returning how many distinct
//   values are duplicated and in arrays what values and how many
//   were found.
//
// Parameters:
//   $1 - Pointer to table
//   $2 - Pointer to field
//   $3 - Pointer to Array to contain Field's Values (Typing is not checked)
//   $4 - Optional Pointer to Longint Array to contain count of duplicates
//
// Output:
//   $0 - Count of how many found distinct values had duplicate values
// ------------------------------------------------------------------------------

C_LONGINT($0;$res_l)
C_POINTER($1;$tblPtr)
C_POINTER($2;$fldPtr)
C_POINTER($3;$arrValuePtr)
C_POINTER($4;$arrCountPtr)

If(Count parameters>2)
  ARRAY LONGINT($arrLong;0)
  C_LONGINT($pos_l)
  C_LONGINT($size_l)
  C_LONGINT($res_l)

  $tblPtr:=$1
  $fieldPtr:=$2
  $arrPtr:=$3

  ALL RECORDS($tblPtr->)
  DISTINCT VALUES($fieldPtr->;$arrPtr->;$arrLong)

  MULTI SORT ARRAY($arrLong;<;$arrPtr->)
  $pos_l:=Find in array($arrLong;1)
  $size_l:=Size of array($arrLong)
  $res_l:=$pos_l-1
  DELETE FROM ARRAY($arrLong;$pos_l;$size_l-$pos_l+1)
  DELETE FROM ARRAY($arrPtr->;$pos_l;$size_l-$pos_l+1)

  If(Count parameters>3)
    $arrCountPtr:=$4
    COPY ARRAY($arrLong;$arrCountPtr->)
  End if

  $0:=$res_l
End if

An example of using the method:

ARRAY LONGINT($test;0)
C_LONGINT($duplicates_l)

$duplicates_l:=Util_Duplicate_Exist (->[Test];->[Test]PK_Field;->$test)

If ($duplicates_l>0)
ALERT("Duplicates Found")
TRACE
End if