Special Duplicate Search

[J. Williams]

I don't know if I can explain this, but here goes. I would like to find duplicates that aren't officially duplicates... specifically, I need to find records where the first 5 alpha/numberic characters in a certain field match and the sixth digit is a match defined as 1=4, like this:

123451

123454

3C5671

3C5674

Basically we discovered that thousands of records were entered twice, so now we need to isolate them, reconcile the inventory and correct the database.

Is there anyway to accomplish this?

[Bill Cuthbert]

I have used variations of the following script to find and tag duplicate field entries e.g. to create customer analysis reports. I have an example FM db if you want me to email a copy to you - please let me know.

You will require access to add fields and scripts to the db:-

Create 3 new fields:

Calculation field = "calc" = LEFT(certain field;5) - gets rid of 6th character

text field = "double"

global text = "temp"

Script to find duplicates in "calc" field, mark each double record in the "double" field then find them :

Script lines:

Show All Records

Sort [ Sort Order: calc (Ascending) ] [ Restore sort order, No dialog ]

Go to Record/Request/Page [ First ]

Set Field [ temp, calc ]

Set Field [ double, "no" ]

Loop Go to Record/Request/Page [ Next, Exit after last ]

If [ temp = calc ]

Set Field [ double, "yes" ]

End If

If [ temp <> calc ]

Set Field [ temp, calc ]

Set Field [ double, "no" ]

End If

End Loop

Enter Find Mode

Set Field [ double, "yes" ]

Perform Find [ Replace Found Set ]

[Lee Smith]

Hi J,

Are these suppose to be 123451 = 3C5671 and 123454 = 3C5674, or are these examples of the field data, but not really duplicates .

I don't see the patterns you refer to in what you posted. It may be possible to use a calculation on one, or the other data, to provide a usable duplicate match check, however, you obviously have other fields that are telling you these are duplicates, so I would use them instead.

Here are a few examples files, free for the downloading, that may help you set up a reliable duplicate calculation field:

Deleting Duplicates - 4 methods By: John Mark Osborne URL:

http://www.databasepros.com/resources.html

Duplicate Flag 2 By: Ralph Learmont URL:

http://www.databasepros.com/resources.html

Tag Duplicates By: Xavier Lescalier URL:

http://www.fmfiles.com/tnt8.html

Custom Record Selection System By: Ray J. Cologon URL:

http://www.nightwing.com.au/FileMaker/

Find Duplicates By: Andy Gaunt URL:

http://www.fusiondzign.com/downloads/downloads.htm

In addition, there has been many threads about this subject here at the Forums, perhaps doing a search for "Find", "Finding", "Duplicate", etc. would provide helpful information to you.

And there has been several Articles written and available at Tech Support. Always a good source for information. Here is the URL for the search page:

http://www.filemaker.com/support/techinfo.html

HTH

Lee

[J. Williams]

Hello there, to answer your question...

12345[color:"red"]1 = 12345[color:"red"]4

3C567[color:"red"]1 = 3C567[color:"red"]4

The sixth character denotes project phase - with the number 1 being begininng and the number 4 being end (no phase numbers in between), but they are one and the actually one and the same.

I basically would like to be able to find only those records where there's a 12345[color:"red"]1 AND a 12345[color:"red"]4.

Thanks for being patient, I'm not a very experienced user, and am happy to read the references provided and try any/all suggestions received.

Are these suppose to be 123451 = 3C5671 and 123454 = 3C5674...

[-Queue-]

Create a calculation text field of

Case( PatternCount( "14", Right( field, 1 ) ), Left( field, 5 ) & Case( Right( field, 1 ) = "1", "4", "1" ) )

and a self-relationship from this field to your original field. Create a calculation number field of

not IsEmpty(relationship::serial)

Do a search for 1 in this field and you'll have all "1" and "4" record pairs.