Comparing Two Files to find Differences

[JackiePiro]

I have two files of about 35,000 names and addresses, one before two months of "updates" the other "current" but due to computer problems some names (records) were lost, and some fields within records were lost (empty), about 2,000 records lost or corrupted. I want to run a "compare" to spit out a list of the lost/corrupted records to look at and determine which were truly lost and which were legitimately deleted. Is there a way to do this in Filemaker? I work with databases a lot but am not a database expert, I have never tried this. Any advice would be appreciated. THANKS - Jackie P.

[tlsparker]

Here is one idea:

Create a new calculated field in each database called, for example, MatchField. Define it as a concatenation of all the fields in the databases you would like to compare:

MatchField = Name&Address&City&ZipCode&etc...(Make sure the calculation result is text.

Define this field in both databases.

Now, define a relationship based on a match between the MatchField in each file.

In the first file, create a calculation field called MatchChecker. Define it as Count(RelationshipName::MatchField). Any exact matches will return 1. Exact duplicates will return a number greater than 1. Nonmatches will return blank. You can perform a find on this MatchChecker field to determine which records don't match up. Enter Find mode, type in 1, check omit, and you get all your non-matches.

I can send a couple of sample files if that would help.