Jump to content

can FM compare and import only missing records


This topic is 7820 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 B)?

Thx!

Link to comment
Share on other sites

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 B)?

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

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

Link to comment
Share on other sites

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 B)-)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 7820 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.