synchronizing databases

[domb]

Hello !

I have a Filemaker database which is alimented by 2 different persons on 2 different computers. I would like to know if it is possible to "synchronize" these 2 different versions of the database (e.g. once per day or week) so that they update each other with the new entries and/or the changes written by each persons.

If this is possible, please tell me how !

Thanks for your suggestions/help.

Polopo

[LiveOak]

It all depends upon what you mean by synchronization. For example, if both your users were to make different address changes to the same record in your file and they both thought they had the correct new address, how is FM going to resolve the conflict with some magic synchronization routine? The is the big myth on synchronization. It is only possible if you have VERY narrowly defined rules for what you mean by "synchronization".

-bd

[CobaltSky]

bd is correct in saying that there is no watertight solution for synchronisation.

The best you can hope to achieve is to synchronise all the records for which there is no conflict, and then produce a report of records which have been updated in both files since the last synchronisation run - for resolution by human intervention. Depending on your procedures, you may be lucky enough to have relatively few records in this last category requiring follow-up - but when you do, your database integrity will hinge on the success and speed of resolving such conflicts - if they are allowed to 'fester' the problem will escalate as further changes are made over the top of unresolved ones. All up, a multi-user solution is a better and cleaner solution if it can be managed.

Where a multi-user option is out of the question, you will need a procedure which flags records as having been updated, and identifies which workstation originated the update. You'll need a method by which the files allocate unique IDs to records without possibility of overlap between the two systems. Both these things may be achieved by the use of a calculation field based on the Status(CurrentUsername) function, with the latter being driven by a lookup (based on a self-join relationship) to conditionally set a flag field each time a record is modified.

You'll also need a relationship between the two files which matches the unique ID field.

Then you will need a series of scripts in each file. The first will cycle through the records in the first file, checking each record against its counterpart in the other file, and marking it (in a special 'SyncStatus' field) as:

A: - not modified in either file

B: - modified in file 1 (the current file) but not in file 2

C: - modified in file 2 but not in file 1

D: - modified in both 1 and 2

E: - not present file 2

After proceeding through this initial run, it should begin again from the top dealing with each set of records in turn:

A: no action required,

B: copy field contents of 1 over related contents of 2

C: copy field contents of 2 over related contents of 1

D: Print a report of records modified in both files (possibly highlighting conflicting data)

E: call a subscript in file 2.

The subscript in file 2 then does two things:

F: Creates a new record and populates it with the data in the first record maked E in file 1 then repeats this process until all the records marked 'E' in file 1 have been imported.

G: Cycles through the records to mark them all according to whether or not they are present in file 1 and marks them G if they are not

H: calls a further sub-script in file 1

The sub-script in file 1 then creates a new record and populates it with the data in the first record marked G in file 2 then repeats this process until all the records marked 'G' in file 2 have been imported.

Ideally the scripts should then stop until user intervention has dealt with any conflicts (marked D for disaster-waiting-to-happen ) and then a further script should be run in each file re-setting the flag fields and the last-updated-by indicator fields to default values to show them as having been synchronised.

It is advisable to start each of the scripts described above with an "Allow User Abort [Off]" step so that the sync procedure cannot be interrupted part-way through. Once the scripts are in place and tested, they will run efficiently provided there is high speed communication between the two files. The effectiveness of the whole procedure will then hinge on the human intervention part of the process which will resolve the otherwise insoluble dilemmas resulting from maintaining divergent data sets.

Hope the above outline gives you a rough 'roadmap' to work from.

[domb]

Thanks for your answers. I will study this in details !

[Jay G.]

CobaltSky is right that this is a complex issue. But take a look at SyncDeK:

http://www.syncdek.com/

SyncDeK synchronizes distributed copies of a FileMaker database, including between multiple users and multiple servers. SyncDeK identifies all Adds/Deletes/Changes to fields in each database copy, marks these changes, encrypts them, sends them to other nodes, and ingests the changes on the other side(s). And vice-versa for full two-way (or multi-way) synchronization.

SyncDeK includes methods and a user interface to resolve conflicts at the *field* level between database nodes.

SyncDeK's mature technology (almost 5 years old), so we think it's actually pretty "watertight". It has error-capture, logging, fault tolerance and testing functions built-in.

[Kurt Knippel]

SyncDek's only problem and this is a big one is the cost. Last time I look it was pretty astronomical, especially for smaller installations.

[soullie]

I hope that there are more programs and ofcourse less expensive then SyncDek. Does anyone know a program.

[pilb]

I had a crack at synchronizing, using Import. I put down my lack of success to my scripting ability but I was wondering if its just not possible to do it that way?

The idea was to find only the records that had changed and flag them in both files, then import the found records from one file - (update records found in current set adding new records) to the other file showing all records and then do the reverse.

I had a few problems based on the unique field in both records and new records seemed not to get transferred all the time. I was wondering if I should pursue fixing the unique ID field or just drop my idea of using Import for this task.

[CobaltSky]

Synchronisation can be done using import as the method of transfer of data between files. However before attempting the scripting of a synchronisation process, you definately need to resolve the issues surrounding issuing of unique record IDs - otherwise the first time you try to synchronise you will be risking unrecoverable data loss.

Once you've sorted out the unique ID allocation side of the problem, I suggest that you take another look at the scripting. The logic you will need is likely to run along the lines of what I suggested in this thread, but with scripted finds in one file followed by scripted. Imports in the other.

In a sense, this is the mechanics of the process - the real issues lie before and after - ie with the allocation of unique IDs and with the resolution of conflicts where both copies of a record have been modified.