Using FM offline and then resyncing with database

[Mavhack]

I have been set a challenge to build a FM database, that can replace a Goldmine Software situation.

The database needs to be available on all the client machines when they are not connected to the net, or central network. BUT when they connect back up, they can resync the data back to the central database, so all the other client machines can access it. Presumably this has got to be a case of just the changed information is uploaded back to the central database, but I can not find a function under FM that will allow this. Ideas??

[Reed]

If you don't want to do much work, and have $ to spend, syncdek.com

[Fenton]

I would second Reed's suggestion. I haven't used SyncDek, though I've spoken with its founder, Jay Gonzales, who is a nice guy.

What I have done is build such a solution myself, using FileMaker, with additional help from Troi File plug-in and AppleScript (you could do it with one or the other; but I used both; long story).

The solution began in version 6, but was converted to 7, both on the client's request, and because 7 lets you see and edit the File References. Without seeing them you can get it working, but you're basically flying blind with regard to references. I imagine you could use MetadataMagic with 6 to see these also.

7 also allows some of the files to be combined into one file, which helps; but imports still happen table by table; so there's still many of those.

On the client machine (this cannot really be done on the host machine while the files are being served) you have the "template" files that you use to populate data going out, with references back to the host files, as well as relationships between themselves. Then you have the files when they come back.

At all times you must protect the files from dangerous operations, like moving or overwriting open files. The safest way is to rename and move files with external tools (plug-in, AppleScript). There will be several imports, with different options (matching records, etc.).

You have to decide on a scheme for deciding what records to update. I was only doing this on a record-by-record basis, not a field-by-field (which SyncDek can do; I would not really want to do that with only FileMaker).

My conclusion is that this can be done in FileMaker, but it is difficult. I will think twice before taking it on again. You need to be fairly skilled. Unless your hourly rate is very low, you will not save a lot of money doing it yourself rather than buying SyncDek. One factor however is that if you do it yourself you can distribute it to hundreds of out-workers at the same cost. SyncDek has per/head pricing, but it goes down with more licenses.

[Mavhack]

Have now had a look at syncdek, price wise it puts it out of the budget. Anybody got any other ideas?:

[SteveB]

I'm shortly going to have to do this between 2 runtimes or a runtime and a Palm. It involves at least 2 major pieces: the first is setting up a technique to track changes. Nightwing (Ray's great website) has a downloadable example (actually 2: one for FM7 and one for earlier versions) that does this. Also, do a search for 'Audit Trail', as Ugo has uploaded another technique.

The other half is merging the changed data with however many target machines you have. Since my runtimes are strictly Windows, I think I've decided on WinBatch to do the merging. Check www.Vizmotion.com as they have developed a synch application that works between a desktop and a Palm. While this wont work for what you're doing, their documentation explains in more detail how they actually perform the synch operations. Its a good roadmap for the procedure. It's in one of their whitepapers; look for the AddressBook sychning. They create CSV files that are then used to merge the changed data.

Steve

[Steve T.]

Howdy, howdy!

I've never had to do this and have not explored the concept, but I guess it depends on how many users and records there are, eh? Plus, I guess you can't have unique serial number unless each remote db uses its own custom system like USER17-1001 or something. Still, assuming there are no conflict problems like that...

Why not have each remote user's db track the last date/time they SYNC'd (global fields) and then use scripts to...

- export modifed REMOTEDB records to remotedata.txt

- export modified MAINDB records to maindata.txt

- import remotedata.txt to MAINDB

- import maindata.txt to REMOTEDB

( = export, export, import, import)

Like I said, I haven't thought this through but might this be good enough?

--ST