In concept...
Create an empty set (duplicates)
All records
sort selection by field to locate dups
place first value into a variable (to_Find)
next record
For each record
repeat
if (to_Find = [table]Searching_Field)
Add record to set 'Duplicates'
end if
next record
until (to_Find # [table]Searching_Field)
to_Find := [table]Searching_Field
end for
You can move the data in the field to search into an array and a second array
with record numbers
then use FIND IN ARRAY
create empty array Dup_Record_Nums
For Size of array (Data)
$Found := Find in array(Data; data{for_counter};for_counter+1)
if ($Found >0)
append to array(Dup_Record_Nums;Data-Rec_Nums{$Found}
end if
end for
A simple check on how many duplicates to expect:
Distinct Values
number_of_Dups = records in table - size of distinct array
Chip
> On Mar 18, 2022, at 8:52 PM, Stephane Potvin via 4D_Tech
> <[email protected]> wrote:
>
> In response to what do you mean by “duplicate"
>
> Here are two scenarios I am facing:
> 1. In upgrading a database from 15 to 19, I got an error message saying that
> there were duplicates in one of my indexed fields.
> I tried locating said duplicates, but doing so manually proved fruitless.
> I would like a method that would search that field to see if there are indeed
> duplicates.
> If duplicates are found, I would like them to appear in a list.
>
> 2. I inherited a lot of CDs from a late friend and I know many of them are
> duplicates of what I have in my collection.
> I would like the ability to specific a field on which to search for
> duplicates.
> I would like to search by the title of the CD. Other times I would like to be
> able to search by the manufacturer's catalogue number. ie. I want to be able
> to be able to specify (in the method is ok) the field(s) on which to do the
> search for duplicates.
>
> I hope this makes sense.
>
> Thanks.
>
>> On Mar 17, 2022, at 15:00, [email protected]
>> <[email protected]> wrote:
>>
>> Send 4D_Tech mailing list submissions to
>> [email protected]
>>
>> To subscribe or unsubscribe via the World Wide Web, visit
>> https://lists.4d.com/mailman/listinfo/4d_tech
>> or, via email, send a message with subject or body 'help' to
>> [email protected]
>>
>> You can reach the person managing the list at
>> [email protected]
>>
>> When replying, please edit your Subject line so it is more specific
>> than "Re: Contents of 4D_Tech digest..."
>>
>>
>> Today's Topics:
>>
>> 1. Re: [SPAM] Image from print form (Keisuke Miyako)
>> 2. Finding duplicates (Stephane Potvin)
>> 3. Re: Finding duplicates (Keisuke Miyako)
>> 4. AW: Finding duplicates ([email protected])
>>
>>
>> ----------------------------------------------------------------------
>>
>> Message: 1
>> Date: Thu, 17 Mar 2022 01:52:51 +0000
>> From: Keisuke Miyako <[email protected]>
>> To: 4D iNug Technical <[email protected]>
>> Subject: Re: [SPAM] Image from print form
>> Message-ID: <[email protected]>
>> Content-Type: text/plain; charset="us-ascii"
>>
>> this command was added in v12.2
>>
>> https://doc.4d.com/4Dv19R3/4D/19-R3/FORM-SCREENSHOT.301-5583051.en.html
>>
>> but you must take into account that a screenshot is for displaying on screen.
>> your listbox, text, shape primitives with all show as bitmap with low
>> resolution.
>>
>> I use 4D V16 on windows.
>>
>> I need to create an image from a 4d form that contains titles and more
>> than one list box.
>>
>> Instead, of creating a pdf document, I need to create an image.
>>
>>
>>
>> ------------------------------
>>
>> Message: 2
>> Date: Wed, 16 Mar 2022 22:24:56 -0400
>> From: Stephane Potvin <[email protected]>
>> To: [email protected]
>> Subject: Finding duplicates
>> Message-ID: <[email protected]>
>> Content-Type: text/plain; charset=us-ascii
>>
>> 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
>>
>> ------------------------------
>>
>> Message: 3
>> Date: Thu, 17 Mar 2022 02:51:15 +0000
>> From: Keisuke Miyako <[email protected]>
>> To: 4D iNug Technical <[email protected]>
>> Subject: Re: Finding duplicates
>> Message-ID: <[email protected]>
>> Content-Type: text/plain; charset="us-ascii"
>>
>> I think you need to be more specific:
>>
>> what do you mean by "duplicate"
>> what do you mean by "work the way I want"
>>
>> 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.
>>
>>
>> ------------------------------
>>
>> Message: 4
>> Date: Thu, 17 Mar 2022 08:40:13 +0100
>> From: <[email protected]>
>> To: "'4D iNug Technical'" <[email protected]>
>> Cc: "'Stephane Potvin'" <[email protected]>
>> Subject: AW: Finding duplicates
>> Message-ID: <[email protected]>
>> Content-Type: text/plain; charset="utf-8"
>>
>> 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]
>> **********************************************************************
>>
>>
>>
>> ------------------------------
>>
>> Subject: Digest Footer
>>
>> **********************************************************************
>> 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]
>> **********************************************************************
>>
>> ------------------------------
>>
>> End of 4D_Tech Digest, Vol 178, Issue 4
>> ***************************************
>
> **********************************************************************
> 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]
**********************************************************************
