Hi Stephane, Not sure if this is what you are looking for but I have this (old) method. It works on the current selection.
One could try to adapt the idea to ORDA but ORDA does not (yet?) return the count of each value when doing a .distinct() https://doc.4d.com/4Dv19/4D/19.1/entitySelectiondistinct.305-5652725.en.html But with ORDA we could anyway rethink the problem and maybe find more elegant solutions. Best, Olivier // Uty_FindDuplicateRecords(->ap2_Fields;{->bDuplicatesFound}) // ---------------------------------------------------- // User name (OS): Olivier // Date and time: 12.03.14, 10:56:16 // ---------------------------------------------------- // Method: Uty_FindDuplicateRecords // Description // Finds duplicate records based on the current selection and on the fields passed, // e.g. more than one record with same [table]firstName AND same [table]familyName AND same [table]city // // Parameters: // $1, pointer to array containing pointers to fields (of one table) // $2, pointer to boolean variable to set to true if duplicates found - optional parameter // ---------------------------------------------------- C_POINTER($1;$p2ap2Fields;$p2Field;$p2Table;$p2ArrayDistinct;$p2bDuplicatesFound) C_LONGINT($lWhere;$lCountFields;$lFields;$lType;$lRecordsFound;$i;$n;$lHowMany) C_TEXT($tCurrentSelection;$tSetFound;$tSetAll) C_BOOLEAN($bDone) ARRAY LONGINT($al_FieldType;0) APPEND TO ARRAY($al_FieldType;Is alpha field) APPEND TO ARRAY($al_FieldType;Is text) APPEND TO ARRAY($al_FieldType;Is real) APPEND TO ARRAY($al_FieldType;Is longint) APPEND TO ARRAY($al_FieldType;Is integer) APPEND TO ARRAY($al_FieldType;Is date) //APPEND TO ARRAY($al_FieldType;Is time) // Query with array with time array crashes (4D 16.5) APPEND TO ARRAY($al_FieldType;Is boolean) $p2ap2Fields:=$1 If (Count parameters>=2) C_POINTER($2) $p2bDuplicatesFound:=$2 $p2bDuplicatesFound->:=False End if $lFields:=Size of array($p2ap2Fields->) For ($lCountFields;$lFields;1;-1) $p2Field:=$p2ap2Fields->{$lCountFields} $lType:=Type($p2Field->) $lWhere:=Find in array($al_FieldType;$lType) If ($lWhere=-1) DELETE FROM ARRAY($p2ap2Fields->;$lCountFields) End if End for $lFields:=Size of array($p2ap2Fields->) If ($lFields>0) $p2Field:=$p2ap2Fields->{1} $p2Table:=Table(Table($p2Field)) $lRecordsFound:=Records in selection($p2Table->) End if If ($lRecordsFound>0) $tCurrentSelection:=Generate UUID COPY NAMED SELECTION($p2Table->;$tCurrentSelection) ARRAY TEXT($at_DistinctText;0) ARRAY TEXT($at_DistinctText;0) ARRAY REAL($ar_DistinctReal;0) ARRAY LONGINT($al_DistinctLongint;0) ARRAY INTEGER($ai_DistinctInteger;0) ARRAY DATE($ad_DistinctDate;0) //ARRAY TIME($ah_DistinctTime;0) ARRAY BOOLEAN($ab_DistinctBoolean;0) ARRAY POINTER($ap2_Arrays;0) APPEND TO ARRAY($ap2_Arrays;->$at_DistinctText) APPEND TO ARRAY($ap2_Arrays;->$at_DistinctText) APPEND TO ARRAY($ap2_Arrays;->$ar_DistinctReal) APPEND TO ARRAY($ap2_Arrays;->$al_DistinctLongint) APPEND TO ARRAY($ap2_Arrays;->$ai_DistinctInteger) APPEND TO ARRAY($ap2_Arrays;->$ad_DistinctDate) //APPEND TO ARRAY($ap2_Arrays;->$ah_DistinctTime) APPEND TO ARRAY($ap2_Arrays;->$ab_DistinctBoolean) ARRAY LONGINT($al_DistinctLongint;0) Repeat For ($lCountFields;1;$lFields) $p2Field:=$p2ap2Fields->{$lCountFields} $lType:=Type($p2Field->) $lWhere:=Find in array($al_FieldType;$lType) If ($lWhere#-1) $p2ArrayDistinct:=$ap2_Arrays{$lWhere} //%W-518.10 DISTINCT VALUES($p2Field->;$p2ArrayDistinct->;$al_Occurences) //%W+518.10 SORT ARRAY($al_Occurences;$p2ArrayDistinct->;<) // "<" to sort in descending order $n:=Size of array($al_Occurences) For ($i;$n;1;-1) If ($al_Occurences{$i}=1) DELETE FROM ARRAY($al_Occurences;$i) DELETE FROM ARRAY($p2ArrayDistinct->;$i) Else $i:=-1 End if End for If (Size of array($al_Occurences)>0) QUERY SELECTION WITH ARRAY($p2Field->;$p2ArrayDistinct->) CLEAR VARIABLE($p2ArrayDistinct->) End if End if End for $bDone:=(Records in selection($p2Table->)=($lRecordsFound)) If (Not($bDone)) $lRecordsFound:=Records in selection($p2Table->) End if Until ($bDone) If ($lCountFields>1) ARRAY LONGINT($al_Positions;0) SET QUERY DESTINATION(Into variable;$lHowMany) SET QUERY LIMIT(2) For ($i;1;$lRecordsFound) GOTO SELECTED RECORD($p2Table->;$i) For ($lCountFields;1;$lFields) $p2Field:=$p2ap2Fields->{$lCountFields} If ($lCountFields=1) QUERY($p2Table->;$p2Field->=$p2Field->;*) Else QUERY($p2Table->; & ;$p2Field->=$p2Field->;*) End if If ($lCountFields=$lFields) QUERY($p2Table->) End if End for If ($lHowMany>1) APPEND TO ARRAY($al_Positions;$i) End if End for SET QUERY LIMIT(0) $tSetFound:=Generate UUID $tSetAll:=Generate UUID CREATE EMPTY SET($p2Table->;$tSetFound) CREATE EMPTY SET($p2Table->;$tSetAll) SET QUERY DESTINATION(Into set;$tSetFound) $lHowMany:=Size of array($al_Positions) For ($i;1;$lHowMany) GOTO SELECTED RECORD($p2Table->;$al_Positions{$i}) If (Not(Is in set($tSetAll))) For ($lCountFields;1;$lFields) $p2Field:=$p2ap2Fields->{$lCountFields} If ($lCountFields=1) QUERY($p2Table->;$p2Field->=$p2Field->;*) Else QUERY($p2Table->; & ;$p2Field->=$p2Field->;*) End if If ($lCountFields=$lFields) QUERY($p2Table->) End if End for If (Records in set($tSetFound)>1) UNION($tSetAll;$tSetFound;$tSetAll) End if End if End for SET QUERY DESTINATION(Into current selection) If (Records in set($tSetAll)>0) USE SET($tSetAll) If (Not(Is nil pointer($p2bDuplicatesFound))) $p2bDuplicatesFound->:=True End if Else USE NAMED SELECTION($tCurrentSelection) End if CLEAR SET($tSetAll) CLEAR SET($tSetFound) Else If (Not(Is nil pointer($p2bDuplicatesFound))) $p2bDuplicatesFound->:=True End if End if CLEAR NAMED SELECTION($tCurrentSelection) End if // end of method -----Ursprüngliche Nachricht----- Von: 4D_Tech <[email protected]> Im Auftrag von Stephane Potvin via 4D_Tech Gesendet: Donnerstag, 17. März 2022 03:25 An: [email protected] Cc: Stephane Potvin <[email protected]> Betreff: Finding duplicates Hello, I am looking for a simple a method to find duplicates. A method that would show only the duplicates if they exist or return a message if there are no such duplicates. I already looked in the 4D doc but found nothing that wakers the way I want. Thank you, Stephane ********************************************************************** 4D Internet Users Group (4D iNUG) New Forum: https://discuss.4D.com Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:[email protected] ********************************************************************** ********************************************************************** 4D Internet Users Group (4D iNUG) New Forum: https://discuss.4D.com Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:[email protected] **********************************************************************
