Merge files

[macman424]

I am wondering what the easiest way to do the following is via a script, or combination of commands...

I have 1 database that will keep all records, and there will be multiple copies of the database on different laptops. I would like to take the records on the mobile dbs and add them to the main db. Unfortunately, the mobile laptops may not have access to a network so multiple users will not work. Essentially, I am looking for a merge function to combine all the files. There are several related tables and portals in the file and all fields/layouts are identical.

Thanks.

[Fitch]

You may want to look at SyncDek. It's not cheap, but it could save you a ton of development time. Synchronizing multiple laptops is not a simple proposition.

[macman424]

Unfortunately, I do not the money for a costly solution. I am looking for something that can be done with the built-in import commands. I have successfully been able to do this manually, so it should be able to be done automatically. Perhaps applescript is an easier option?

Edited September 16, 2006 by Guest

[Ender]

This CAN be done within FileMaker (I don't think Applescript would solve the logistics or figuring out which records need updating, etc.). The trouble is figuring out the algorithm and scripting, which can get fairly complex. And although we can give you the general idea, the specifics are probably not something we can do for you.

You basically need to track modification dates for each table, along with information in the client tables about when each record was last synched. From that, you can figure out which records in the master tables are older or newer than the records in the client tables. The hard part is then deciding rules about how to update the master records. For example, do you always want to replace any data in the master tables that's been modified in the client tables? Do you only want to update some of the fields (this would be much more complex)? What happens if two different clients modify the same record in their respective files, how do you decide which to keep?

There's also some complexity involved in managing the serial numbers. It can be handled, but you have to think everything through and do a lot of testing.

[macman424]

Luckily, there shouldn't be the need to modify any of the records, simply add new records, which will always be different for each user. It should be a matter of setting up the import settings for each table.

Is there a way to set up multiple 'import records' with one user-defined file?

[Ender]

Sorry, if your solution has multiple client files, I think you'll need to have your client select each source file for the import.

[Fenton]

There is a way to import multiple files. The basic routine is to have a dedicated folder for the "new" files, then move each to a "done" folder after importing. Depending on your platform you might use different tools. It is fairly easy to do with AppleScript; Troi File or equivalent also works; on a PC, command-line or VB also, but requires more knowledge.

The basic commands you need are: list files in a folder (AppleScript: list files without invisibles), rename file, and move file. FileMaker can do the rest.

It is easier if you can upgrade to FileMaker 8. It has a Script Variable which can be dynamically set to a filepath for each import. Otherwise you have to do the ol' "switcheroo" on each file's name; change it to a fixed name for the FileMaker import, then switch it back as you move it.

But that's only going to handle the files. You've still got to do the "synchronization" between the FileMaker records. If you can say, "No master record can be edited while its counterpart is out on a laptop," and you enforce that with a lock; or, as you say, it is absolutely only "new" records, then you've got a good chance. Otherwise it's going to be tedious, to say the least.

FileMaker does not have a field-level modification timestamp, only record-level. It is possible to build one for each field, but you'd have to do it for each relevant field; and compare each, for each record (not to mention, if 2 people edit the same record who wins?). Gak!