Database synchronization

[CanadianFile]

This is, I believe, what could get to be a rather complexe issue I want to solve:

I have a FM Pro 5 Database that I want to both leave on a DeskTop so as to share it with other colleagues at the office, and also be able to take it with me on my LapTop so as to be able to work outside. How can I Synchronize both copies when I get back to my office ?

I trust I can do this using only FM's built-in scripting, but I can't seem to figure out what would most simply do the job.

One of my defined fields constitutes a unique ID number for each record. Of course, I'm expecting to add at least a "Date" and "Time" fields in order to be able know which record was last modified.

Of course, any newly created records on my laptop will have to be inserted in the "fixed" Database while respecting it's unique "ID field" sequence and vice versa.

Can anyone give me a hand with this ?

[4.Pack]

I have a similar problem, but with 10 different laptops !!

Each record has a unique ID no, however it is feasible that two or more laptops would generate the same ID no and muck the master database up when updating.

The best way I can figure of making it bulletproof is to make each laptop database generate a diferent type of ID no, I'll be using the guys who's using it's initals. so the master db would generate a number like 12345, but the laptop would generate RH12345, obviously using different initals for each laptop. Once the number is generated its not a problem.

Anyway I hope this works as I havent tried it yet

[BobWeaver]

As a start:

Create a global date and a global time field called DateSynchronized and TimeSynchronized. These will hold the date & time when the files were last synchronized. Then create regular date and time fields with the autoenter option set to enter the date and time modified. Now you can easily find which records have been added or modified since the last time the file was synchronized by comparing these fields. Create one additional field called ExportYN. This will be explained later.

You will now need 3 scripts in the file.

The first script (called SetExpFlag) will set the ExportYN field in all the records to 'yes'

The second script (called ExportNew) one will search for all records which have been created/modified since last synchronized and whose ExportYN field='yes'; and then export these records to a file called Temp.

The third script (called ImportNew) will import records from Temp and then set the ExportYN field of the current found set to 'no'.

Finally, create a separate database file called Sync. It won't have any records, just one script which executes the synchronization scripts in two copies of your main database.It runs them in this order:

SetExpFlag in fixed dbase

SetExpFlag in mobile dbase

ExportNew in fixed dbase

ImportNew in mobile dbase

ExportNew in mobile dbase

ImportNew in fixed dbase

The reason for the ExportYN is to keep the second copy of the file from re-exporting the new records that it just imported.

At this point both files will have the same sets of records. However, there are a number of things that still have to be considered.

1. If all you did was create new records, then you are finished (except for updating the global TimeSynchronized/DateSynchonized fields with the current time and date).

2. If some records were previously existing and were modified. Then you will now have two copies of these records in each file. There will be the original unmodified record, and the modified version. Since you have a unique record ID, you can search for duplicates and delete the one which is older than the last sync. time/date. Another subscript can perform this on both files.

3. If the same record had been modified in both copies of the database, then you will again have two copies of the record, and both will have a date/time modified later than the last sync, date/time. In this case you have to decide which record is correct. This will probably have to be checked manually. You can make a subscript to search for these and allow the user to go through and edit them.

4. Records deleted from one copy of the database will still be present in the other copy. I suggest that records that need to be deleted should only be deleted as the last step of the syncronization (another subscript). The rest of the time, you can mark records that you want to delete in a special field.

This is fairly sketchy, but hopefully it helps.

[LiveOak]

Be careful about using globals to store last synchronization dates and times. In a multi-user file, this will not work. Each user gets their own set of globals which disappear when they close the file. The only time the globals are preserved is when the file is set to single user or the globals are modified when only one user has the file open (the host).

The first thing to consider when implementing synchronization is what exactly that means to you. If two people type additions to a notes field, should the new note replace the older note? In this case, what might be desired is to make BOTH additons to the notes field. Before launching off on some detailed implementation, these types of issues must be considered. There are many "synchronization" cases where the most qualified person in the company wouldn't be able to decide what to do (two address changes in the same day) without further information. The synchronization problem is often not one of implementation alone! -bd

[Vaughan]

Forget synchronisation, get them to connect to the databases through either a web interfate (using Web Companion) or through FileMaker Pro. The Web interface is faster from a network perspective, unless Timbuktu or PCAnywhere is used.

Synchronising databases is going to be nothing but trouble -- you are building a rod to beat yourself with.

[4.Pack]

well I gotta get this DB syncro sorted, and it has to be completly automated and fool proof with minimum human input !! (no easy task). As I can see many more "remote" systems looming on the horizon.

I have date and time created and date and time modified as well as by whom, therefore users can see when the record was changed, and i'm going to assume that the latest version is the most upto date. (if they all end up changing the address and changing it back etc then thats their problem not mine, plus they are gonna know who's doing it so can discuss between them whats right).

As for ammending stuff into note fields and the like, I would expect that users would add to the notes if the original text is relevant, or delete and replace it if not !

[dspires]

quote:

---

Forget synchronisation, get them to connect to the databases through either a web interfate (using Web Companion) or through FileMaker Pro. The Web interface is faster from a network perspective, unless Timbuktu or PCAnywhere is used.

Synchronising databases is going to be nothing but trouble -- you are building a rod to beat yourself with.

---

This is not always possible. I have a client with an office setup with server and wireless network but when they have a remote event they take a laptop with the latest database information. Requiring a phone connection for the duration of the day while they make changes to the database is not feasible.

Upon return after 1 day to maybe a week, the office database needs to be updated with the data contained in the laptop.

My 'syncronization' scripts take the information from the laptop for the date range and over-writes/add to the office database records (that match the dates).

The key is that they understand this and use the system accordingly.

[CanadianFile]

quote:

---

Originally posted by Vaughan:

Forget synchronisation, get them to connect to the databases through either a web interfate (using Web Companion) or through FileMaker Pro. The Web interface is faster from a network perspective, unless Timbuktu or PCAnywhere is used.

Synchronising databases is going to be nothing but trouble -- you are building a rod to beat yourself with.

---

Getting a "live" link between a portable and the main database is not possible, my car beeing the temporary office in this case.

Did anyone try "SyncDek", the plugin that is said to be made just for the purpose of synchronizing FM databases ? It isn't cheap, but if it handles the task adequatly, I might be convinced to give it a try.

[Chuck]

About six months ago, I tried using SyncDek. It was an early release, and I'm afraid that at the time it just wasn't up to snuff. I have to report that their tech support wasn't very helpful either, when I could get ahold of them.

I ended up writting my own sync routines, which was a real bear. It can be done, but it isn't easy.

However, when I was at the FM Dev Con last August, I did see an exhibit of a program that uses ODBC to syncronize databases that seemed promising. I can't find the information on it right now, but it was eSomthing. Sorry I can be of more help.

Chuck

[Vaughan]

Cellular phone modems have been around for years. Coupled with "thin" clients (like Citrix, PC Anywhere and Timbuktu) to reduce bandwidth requirements it should work fine, the only problem being call costs.

It still seems to me that syncing databases is an undoable task, due to the variety of ways records can be modified by multiple users at different times.

If *one* person only has change privileges to the whole database, it'd be easy. Perhaps the database could be set up so that while somebody is on the road it is "frozen" until they get back with their changes. Or maybe remote mobile users can only create new records not edit or delete existing data -- this would be easy to sync.

Unless limitations such as these are imposed I cannot see a robust and reliable synchronisation method being implemented.

I still recommend to take it all on-line and do it live.

[This message has been edited by Vaughan (edited November 29, 2000).]

[Chuck]

The only reason we were able to have synchronization work was that the client was willing to agree to set rules about which data would be the master data if more than one version of the database changed the same field in the same record. Without those rules, you're right, it wouldn't have been possible, or at least, the work involved would have increased geometrically.

Chuck

[4.Pack]

hmm have I underestimated the complexities of db syncro !!

what I was going to do is this.....

Each laptop has it's own seperate ver of the db, the guys go out change the details write letters etc, when they get back to the office I was going to perform an Import from the laptop to the "master db" using the unique contact ID no as the match field, importing all other fields (using update data option, with add remaining records) then I suppose reverse the process to update the laptop!

but , if while in the office then we type a letter to a customer say, then when I perform that import from the laptop will it wipe it out ??

I guess I need to experiment

[4.Pack]

suddenly it all becomes clear.....

oh bugger

[Chuck]

That program that I couldn't remember the name of is published by Casahl Technology <http://www.casahl.com> and is called ecKnowledge. I haven't actually used it, but there's an indepth article about it by Chris Moyer in this month's (December/January) issue of FileMaker Pro Advisor (p. 42).

Chuck

[paullam]

I'm glad i found this topic with its most usefull replies. I have been brainstorming myself over a synchronisation method but did not come up with something usefull, except for the 'user-related' recordID's. However it's strange that there is no such thing as a built-in sync-engine. I should think the people at Filemaker are the best placed to write and incorporate this technology as they must have had the question several times, seen the evolution towards more and more portable computing. If they can't do it, how should we be able to do it with the tools they provide. Maybe they can learn something from Microsoft. The ActiveSync program which keeps my Compaq Ipaq up to date with my Outlook agenda is realy performing well, in both directions. But as stated, when a unresolvable issue occurs, there's only the user to decide which one to keep or overwrite. That will never be possible to automate.

[Kurt Knippel]

quote:

---

Originally posted by Paul Lammertyn:

I'm glad i found this topic with its most usefull replies. I have been brainstorming myself over a synchronisation method but did not come up with something usefull, except for the 'user-related' recordID's.

---

Check out my article on "IDs" in the Development Standards forum. It covers the creation of IDs, which are virtually unduplicateable even across hundreds of computer systems, and does not artificially add a "user-related" tag to the ID.

here is the link:

http://www.fmforums.com/ubb/cgi-bin/ultimatebb.cgi?ubb=get_topic&f=8&t=000018

[BobWeaver]

As long as there is a truly unique ID for each record, then synchonizing new records is easily done.

The real problem is in determining what to do about modified records and deleted records.

Example 1: Two users modify the same record. User A modifies the phone number field, and User B modifies the address field. Now, which version is correct?

Example 2: User A deletes a record in his copy; User B doesn't. What's to prevent the undeleted record in User B's copy from being added back in when the files are synchonized?

As Chuck said earlier in this topic, it can work as long as you set up specific rule for all the situations, but it may be less than ideal. I expect that part of the synchonization script would involve presenting the user with a list questionable records that need to be resolved manually.

[Vaughan]

One simplifying rule might be to only allow people to edit or delete their own records.

[Kurt Knippel]

I moved this specific topic over to the PDA forum

http://www.fmforums.com/ubb/cgi-bin/ultimatebb.cgi?ubb=forum&f=35

[cvkaiser]

I