can FM compare and import only missing records

[cbesey]

Seems like this should be an easy one, but I'm stumped.

I'm trying to import only records that exist in fmp file A into fmp file B (records were lost in a crash some time ago, I need to take them from an old backup). There's no easy way to find the records in A so as to do it as a simple import into B. Is there a way that FM will examine the two fmp files and ONLY import into B those that are in A but don't exist in B (while also not touching all of the other records in ?

Thx!

[kennedy]

Huh?

I'm trying to import only records that exist in fmp file A into fmp file B

If they don't exist in A, then how would FMP import them anyway?

Me thinks you did not say what you mean.

Is there a way that FM will examine the two fmp files and ONLY import into B those that are in A but don't exist in B (while also not touching all of the other records in ?

Isn't that what the default import "Add new records" does?

Have you tried it? (Make sure you backup both files first.)

[cbesey]

The records are in A and I need to get them into B, but don't have a good way to figure out which records in A I'm looking for.

I thought Add New Records would add all of the records from A, not only the ones in A that aren't in B. I'm doublechecking it now (89000 records to go, pant, pant, pant -)

[cbesey]

Yup, just check it - Add New Records imports everything in A, not just the records in A that don't exist in B. Am I going to have to write a script to examine every record in A to see if it's in B, then set a flag in A if it's not in B? That would take forever to run, and I need to do this with about six different versions of the A file. Yikes.

[BobWeaver]

Importing will only import the current found set from the import file. So, if you have a means to select only the records that you want to import, no problem.

You may have to do a search for records newer than a certain date, or if you have some unique ID field in both files, you can set up a relationship between the two files based on the unique record ID field, then use a script to loop through the import file testing for a valid relationship (ie, the record is a duplicate) and if valid, omit the record. This will leave you with a found set containing only non-duplicates. There may be other simpler ways of accomplishing this depending on your particular situation.

[Vaughan]

Is there some identifer to tell which records exist? There should be otherwise you won't know either!

Create a global field in this database and use it as a multi-key field. Put all the identifiers in this field separated by paragraphs.

Create a relationship based on this field and the other database, then use the "Go to Related Record [show]" to find all the existing records, then the "Show Omitted" to find the others, then import.

[BobWeaver]

But, be careful you don't go over the 64000 character limit on the multi-key field. Could be a problem on a file with 89000+ records.

[Vaughan]

Yeah... good point. I hit that recently myself.

[CobaltSky]

Alternatively, you might like to check out the import option for "Update matching records in current found set", which has an additional checkbox option to "Add remaining records" on it.

With this option, you will get to add any records that are in A but are not in B, but you will also have any data in B on records that are also in A overwritten by the data in A.

If the data update is not what you want, and if the number of records prevents you from using the method suggested by Vaughan, then I suggest you:

1. Create a relationship within A which matches a unique key field in A to a corresponding field in B.

2. Add a calculating field in A with the formula: Case(Count(RelationshipToB::KeyField), 0, 1)

3. Set up a script in file A which performs a find to locate all the records in A which have a 1 in the new field created at 2 (above).

4. Create a script in B which starts of with Perform Script [sub-scripts, External: ] to call the script in file A which is referred to in 3 (above) and then immediately imports the found set from file A.

Since only the records in file A which are not in File B will acquire a 1 as the result of the calculation described at step 2 above, only the records which are in A but not in B will be found by the script at step 3 and imported into B at step 4.

[cbesey]

Just wanted to say thanks to everyone. Lots of great suggestions. I'm new to the forum and am psyched to find it full of knowledgable folks.

Since I needed to address a few other problems as well, my eventual solution was to run a script that set a flag field in B - identifying noncontiguous primary keys and duplicate ones. This'll let me find my missing records in old backups more easily and clean up the dups.

Chris