"merging" two copies of the same database

[titanium]

Hello,

Im looking for some advise and direction here...

I have a database setup and running, but we are a unique situation ware we have to have multiple copies of the DB running completely separately at various times on multiple laptops. (no internet connection so FM Server is out)

This is a customer database and each customer has systems and each system has inspections. At no time is the same customer, or any or their systems, modified by two copies of the file... so one person would modify customer A, the other customer B.(no worries about both editing customer A...ever)

What i want to do is script a process to (weekly) import all created and modified records from each laptop into the main computer, then re distribute the database with all the new records and changes made.

I have been able to export all the information that has been changed and created, but this doesn't really help. Ive read about importing directly from the modified file without doing an actual export, but it all seems to not really apply to what im trying to achieve (unless im relly missing something). Effectively im trying to do a one way sync of the data.

Currently i have scripted to export all data required (and working great) but when i do an import i can create the customer and the systems etc, but the systems dont get attached to the customer. I use unique IDs for everything so never are two IDs able to be the same..im sure this will help in the long run.

Im ultimately wondering if anyone has tried this sort of process before and has any ideas on a better what to go about this before i start prying into why the importing isnt working right.

Any thoughts would be greatly helpful. Im thinking that im just missing steps in my script and perhaps need to modify a few fields in the DB, but i can go more into that if i am in fact on the right track...

Thanks for your input!

[David McQueen]

I have done this. Here are the considerations as best I remember.Hopefully they point out where you are having trouble.

1. In your exports, it will help you to export in FileMaker format. Then when you do an add new records and update based on key fields the program should have the least problem.

In a multi table solution, you are probably better off to have a scripted import from the "Mother Ship" to the remote files. Then you can systematically go through all tables dealing with all fields as they occur. You may have to rename the remote files as they come in for updating. I seem to remember having problems having a file import from "itself" meaning another file of the same name. Or alternatively have the remote files with different names right off the getgo.

Then the process would be import to mother ship in the form of add new records and update changes. Then when all are done, reverse the process and import back into the remote files to send back out - nuke all records in the remote file and do a full import into the remote file.

2. You talk of exporting only some fields. Export everything except calculated fields. Do it in every table that changes.

3. You talk of having unique keys.Realize that your keys must not only be unique within any copy of the program but must be unique over all copies of the program. What I did was use text based serial numbers with a text prefix. All keys generated in copy #1 were prefixed with "A". Copy #2 "B" .. etc.

As clients and systems may be generated at the mother ship,this may not be an issue in those tables, but you are generating child records out in the field and this would be an issue.

So there is not just one remote file. the file for each person is different in how it generates keys.

Hope this helps a bit.

Edited November 29, 2009 by Guest
Subcribed to post

[titanium]

Thanks for the reply. Sounds like im on the right track...

I export all created records, then all modified records and save them as filemaker files and rename them at the same time... So, i now have a series of filemaker files with relavent names (ie: customers created, customers modified etc...)

Now, my import is set to open a file, and import the records. If the file is created records, then it creates new records on import, if its modified then it updates matching sets based on customer ID (which is the common ground across all systems etc)

My unique IDs are created using the mac address of the computer and time date stamp so on every machine the ids are unique, so thats good also.

So, my problems come in with the importing script. I can create a customer, and create the new systems, but the systems dont attach to the customer.

My ID fields are set to automatically create, but i changed them to "Allow ti be changed" so that on import (in theory) the ID will be written as the ID that is imported rather than the ID automatically created on record creation at time of import.

hummmm...well, ill keep playing with the script..I have attached a picture of the import and export scripts... the import script is the one messing with me now.

cheers,

- - - - - - -

After much more looking and thinking, its "simple"...whats happening (as i expected) is that the customer IDs are being created new on import rather than importing the ID from the record of the exported file...Any thoughts on how to import, create a new record, but use the ID of the record being imported? I cant wrap my brain around how to script this...what if i have 20 records rather than 1, what if i have 50!!!?

Thanks for your thoughts!

Edited November 30, 2009 by Guest
Additional thoughts

[David McQueen]

In your import there is a check box for update calculations etc. I believe that unchecked prevents this. Whichever it is, do not allow autoupdating during the import. That should get you on your way.

The other thing I thosught of later, when I was doing this, Ialso had a script that went in after and reset serial numbers so that there was no gap. Basically it found all of the records that were produced by that particular machine, went to the last record and grabbed the number portion of the serial number and then used a set next serial step to reset the increment setting it at the prefix for that machine & number to text of the next serial.

[titanium]

Thanks very much for your input! While i have a lot of testing to do to be sure everything is copying correctly, your recommendations have worked great. Thanks again...truly appreciated.