Inherited text file that hurts my head!!!

[Ralphie]

I have imported a text file using FMPRO 5.5, and I now have an FMPRO database. The database has 6 defined fields. I know how to search all records for duplicate data in the same field, and I know how to concatenate fields using a calculation field to check all records for duplicate data in multiple fields. However, my problem is that the text file I inherited was not originally set up in a very organized manner, so I have data from one specific field that is sometimes duplicated in a different specific field in different records . Since the database has over 15,000 records, I need to make a script or automated procedure to find all records with these duplications.

My thought was to create a script that stores the value (in this case, text) of the first specific field in the first database record, then compares the stored field value to the second specific field in each record in the database and finds the duplicates. The process would repeat by jumping to the first specific field in the next record, storing the value, and comparing it to the second specific field in all the other records, and so on, until all the first specific fields in all the records were compared to all the second specific fields in the database.

To do the above, I would need to repeatedly point to a specific field within a specific record and store the value, then retrieve the value and compare it to the second specific field in all the other records. Is there a way in FMPRO to do this, or can you think of a better way to accomplish what I am trying to do? I would appreciate any help the forum could give me.

Thanks in advance

Ralphie

[andygaunt]

Hi,

The text in the first field, is this all that exists in that field ( eg CAT)

If so, can you use a patterncount calculation to see if it exists in the second field then mark the record.

If ( PatternCount("Text", Second Field), 1, "")

If you are using literal text, or:

If ( PatternCount(First field, Second Field), 1, "")

Then you could do a search for the 1.

Let us know

[Ralphie]

Thanks! I'll give that a try and let you know.

[Kundinger]

Hello,

Another possible solution would be to export all data to another file... where each piece of data is in a separate record... then check for duplicates. To do this properly, you need to do some prep work...

A. Add a RECNUM field to your existing file... give each record a unique number

B. Add a FIELDNAME field to your existing file

C. Create a new file... "CLEANUP"

D. Add three fields... "RECNUM", "FIELDNAME", "DATA"

E. Enter the first fieldname (existing file) into the FIELDNAME of your existing file

F. Use the REPLACE command to duplicate this to all your records... be sure to SHOW ALL

G. EXPORT all your records to the "CLEANUP" file...

-- from Existing file: "RECNUM", "FIELDNAME", "firstfield data"

-- to CLEANUP file: "RECNUM", "FIELDNAME", "DATA"

H. Repeat steps E. F. & G. for every other field in your existing file

I. You will now have a CLEANUP file with your data

-- "RECNUM", "FIELDNAME", "DATA"

-- 100001 Names Hello

-- 100002 Names Allen

-- 100001 Notes Allen

-- 100002 Notes Hello

J. Perform a FIND to locate all the duplicates

K. Clean up your data

L. Import the data back into the original exisitng file

I have used this process to 'clean' data from old systems to be moved to new systems.

This will work... just take your time and be accurate with each step... Good Luck!!!

Bob Kundinger

PS - let me know if you need assistance... I can send you a sample.

[Ralphie]

Bob,

Thanks so much for the input. I was able to get the job done! Thanks to all who read and take the time to post on this board. With this board...no one should have problems getting anything done in Filemaker!

Thanks Again!