Hi, I have been trying to get something to work and I am probably going
about it the wrong way. I have 2 filemaker pro 7 databases. A proportion of
the records in both databases have 3 fields common. In other words if you
were just looking at these 3 fields, then a proportion of the records in one
database would have an identical pair in the other.
The field structure in each database is identical.
I want to automatically compare both databases and delete the pairs (one
from each database). This would leave me with 2 database of records that did
not have a pair in the other database. I can't see my way through the
documentation to see if this is possible.
Any help VERY gratefully received.
Simon

Simon,

Are you saying that you have two separate FileMaker Pro 7 files -- identical
in structure, but not in the records they contain -- that are overlapping
each other with some information?

Some questions first:

- Is there some reason why the files are separate?

- Which file should retain the record when a duplicate is found?

- What should happen when information in those three fields is identical,
but the information in the other fields for those two records is not?

Bill

"Simon Cook" <simon (AT) cygnusassociates (DOT) co.uk> wrote

Firstly, thank you for taking the time to reply,


On 11/10/05 14:52, in article aZSdnfyAUvMgXtbeRVn-jA (AT) comcast (DOT) com, "Bill
Marriott" <wjm (AT) wjm (DOT) org> wrote:

Yes
I separated the files because I thought it would be easier to compare them
if they were separate. Each record has an 'original' version, and should
have a later 'twin' (all records have a date and time attached) what I want
to do is 'weed out' the ones that are already twinned and see whats left.
The two files (or two types of records!) could be called therefore
"original' data and 'later twin'. I would write the whole reason why for
this but I don't think it would be quick, for our company we have a
particular reason we want to do this. Actually more than 3 of the 8 fields
would be identical, but I thought checking just these three would be 'belt
and braces' enough.
I thought this through a little more, probably the best thing to do is to
delete the original record from its file (if its still in a file), and copy
the 'later twin' to a new file for archival. This would leave the 2 files to
receive more imported 'originals' and 'later twins' for comparison.
This wouldn't matter, if the 3 field rule is fulfilled we are happy.

Thanks again, sincerely,
Simon

Huh?

Let's say that you had a field, N, in both files (A & B), that would be
identical in the case of duplicate records. You want:

If Record[N] is in A, but not in B, then do nothing

If Record[N] is in B, but not in A, then do nothing

If Record[N] is in A *and* in B then delete it from A and create a copy of
Record[N] in a new file, C, leaving B unchanged?

Do I understand you correctly? Is A always the original file? (If not, how
do I know which is the original record?)

Bill


"Simon Cook" <simon (AT) cygnusassociates (DOT) co.uk> wrote

I understand you to be saying that if there are record pairs between
the two files then you want to get rid of both these records.
The easiest way to do this is to create a calculation field "One" in
each file which simply equals 1.
Next create the field "Mark For Deletion" in each file.
Create a link (relationship) between the 2 files so that
File1::Field1 = File2::Field1
File1::Field2 = File2::Field2
File1::Field3 = File2::Field3

In a layout in File1, place the field "One" from file 2 using the above
mentioned relationship.
Do a search for the number 1 in that field; only duplicate records will
be found because the related record must exist in order for the field
to contain a 1.
Here's the catch, DON'T delete the records, mark the flag for deletion
but before you do, repeat the same procedure in the other file.

Regarding the need to match 3 fields in each table, in most relational
database you would create a new field in both tables (say keyfield) and
calculate its contents to be the combination of the three fields you
need to match
eg keyfield = field1 & "." & field2 & "." & field3 for both tables.
Then you end up with a single field that can be linked between the two
tables.
This should also be possible with FM7/8 since you can create a
Calculation field to automatically generate the keyfield contents and
use Define Database / Relationship to link the two keyfields.
The tip below for creating a flag field is very important to manage the
deletions.

Michael Paine


FP wrote:

Yes that's right, I had trouble trying to explain it properly, but I think
that's it. 'A' is the original file, yes.
Actually, thinking about it a bit more just now, copying or deleting files
doesn't matter and probably isn't the best idea. Just marking them in somne
way would be fine, the marked records could be archived, deleted as needed
very simply after this as required.
The important thing is to mark the 'original' and the 'later twin' in the
case where they match. We are waiting for and checking for this 'later
twin'. Every 'original' record should have a 'later twin' eventually, and
the purpose of this (which I should have mentioned earlier probably) find
'original' that don't get a 'later twin'.
Many thanks again, please feel free to email me direct, should you wish to.
Simon


On 11/10/05 17:45, in article UfidncZWZZq0cdbeRVn-uA (AT) comcast (DOT) com, "Bill
Marriott" <wjm (AT) wjm (DOT) org> wrote:

Simon,

FP and Michael Paine made some great suggestions in the thread. The first
step to handling these is to match the records in the two files up. I think
both approaches have their benefits. The general outline is this:

1) Yes, FileMaker 7 can do this.
2) The two files need to be related to each other, either by a "derived"
serial number like Michael suggests, or by the multi-criteria relationship
described by FP.
3) Once they are related, you can use a calculation to mark records which
are duplicated.

My only additional suggestion is that using files to organize data is a very
"pre-7" way of doing things. If you would like to provide much more detail,
feel free to email me and I'll suggest a way to optimize the process.

Bill


"Simon Cook" <simon (AT) cygnusassociates (DOT) co.uk> wrote