Syncronizing multiple runtime databases

[shadowfelldown]

[color:red]Note: I apologize in advance if this is posted in the wrong section, I wasn’t really sure where to put it. Feel free to move this to the proper section if necessairy.

Hello all, I am so sorry to just come in right off the bat with my first post and ask for help, but I've sort of landed myself in a bit of a pickle.

I am a regulatory affairs intern at a small biotech device company and I seem to have inherited the administration of a filemaker pro database. This database was apparently set up a couple years ago to allow us to keep track of our small library of important regulatory documents and their digital counterparts. It currently exists as a runtime solution file hosted on one of the company's shared drives.

Because the database is in a runtime, only one user at a time can access it. Apparently in the past this limitation has not been a problem and the department has just dealt with it. Unfortunately, this is no longer the case, and I have been given the task of enabling the database to be accessed by multiple users simultaneously.

The only problem is that I know next to nothing about databases, and until I started this internship I had never even heard of FileMaker. All my knowledge about this database has basically come from a mixture of help files, trial and error, and a general level of comfort with computers. With that said, I have still attempted to solve the problem the best that I can, probably using methods that sound downright insane to those of you that have more experience (read: any experience) with filemaker, So please feel free to suggest any sort of alternatives that could work better.

I have decided that the most workable solution is to give a copy of the runtime to each party that needs access to the database. (There is an unlimited copy license on the runtime, and getting any other copies of the software is not in the budget.) This raises another issue because if any documents are added or removed to the database (which they periodically are) all the runtimes need to be updated with the change. In addition, I don’t want to really bog down the loading times of the runtimes on all the machines by deleting and re-importing all records every time they open a runtime if there are no changes that were made.

Here is my attempt at solving these problems:

First, I have designated the copy of the runtime on the server as the "master" database, and the copies on the computers as read only clients. I then placed the actual information from the database in an excel sheet which I have designated as the only place that records will be changed, added, or deleted. This excel sheet contains a cell with a counting formula which totals the number of records that are in the database.

So now when there are changes to be made to the library document list, the person adding or removing a record will first enter the new information into the excel spreadsheet, delete all existing records in the "master" database, and finally re-import the spreadsheet into the "master" database.

When the records are imported to this master database, the cell containing the number of total documents is imported into a container field called NoOfRecordsServer. Upon closing the runtime, a script runs that exports the single record contained in the NoOfRecordsServer field and saves it to a file called NoRecordsServer.xml on the shared drive.

When any of the clients opens their copy of the runtime, a script will kick on that does the following:

1) The script stores the total number of records in the current database as a variable named $$NoOfRecordsClient using the Get(totalrecordcount)

2) The script imports the records from the NoRecordsServer file (I figure importing one record takes only a little bit of time) into a field called NoOfDocumentsServer

3) The script stores the value in NoOfRecordsServer as a variable $$NoOfRecordsServer

4) The script then executes an If: then statement that does one of three things:

- If $$NoOfRecordsClient < $$NoOfRecordsServer, the script will assume that a record has been added and import the library from the excel spreadsheet using the update existing records option (with add remaining data as new records checked)

- If $$NoOfRecordsClient = $$NoOfRecordsServer the script will assume that there has been no changes to the database, and just go on with loading the initial layout without the import step.

- Finally, if $NoOfRecordsClient > $$NoOfRecordsServer the script will assume that a record has been deleted from the main server and proceed to delete all records and import them all again (just using add new records)

[color:red](A PDF File containing the steps in this scrip is attached to this post)

Unfortunately, for some reason this is not working. Regardless of the value stored in the NoRecordsServer.xml file the client script keeps deleting and re-importing all the records. The only possible explanations that I can come up with are either that somehow the client is not importing the proper value from the NoRecordsServer.xml (perhaps it is importing a blank record?) or for some reason the variables are not updating correctly when the single record is imported (perhaps the difference in the number of records in the database vs. the NoRecordServer.xml file is causing issues.)

I have tried everything that I can think of to get it to work, but I have reached the extent of my limited filemaker knowledge. Any help or advice in this matter would be greatly appreciated.

Thanks,

~Dan

system specs:

(this computer, used to edit the database)

Windows XP

running filemaker pro 9

(other computers)

windows xp

running filemaker pro runtime database (I think made in Filemaker version 7)

open_script.pdf

[bcooney]

I'm sorry, but your mgmt needs to pay the developer for a full version of the system, and host it on a FM Server. End of story.

[shadowfelldown]

Forgive me if I'm being a pain, but I guess I don't quite understand what you mean. Do you mean that is there no way to accomplish this using the setup that I have now? (If so, then why can't it be done?) or are you just saying that FM server is the ideal solution to the problem of synchronizing multiple databases? (I would totally agree by the way. But unfortunately I've gotten a pretty clear impression that if this change is going to be implemented, it will be done with the resources that we already have. )

In other news, After doing a little more testing on the process to try and find the source of the hang up I've decided that it is doing everything the way that its supposed to, except the last part: importing the single value that is stored in the NoRecordServer.xml and storing it as the $$NoOfRecordsServer variable for use in the If: then import step.

here are the results of the testing:

green is fine, while red is not so fine.

[color:green]new record updated in excel file --> [color:green]excel file loaded to Master --> [color:green]NoOfRecords field exported to NoRecordsServer.xml --> [color:green]correct value can be found in cell A:1 of excel file if opened at this step --> [color:red]Import the single value from cell A:1 of NoRecordsServer.xml --> [color:red]store imported value as a variable --> [color:green]If:then import step (works when variable is manually plugged in) --> end

If I could just figure out a way to import the value of that one cell into the variable I believe I would be all set.

thank you,

~Dan

[bruceR]

BCooney has it right. Don't waste your time. Use server.

You don't sync databases, you use ONE database and have multiple user connect to it.

There are professional sync product that do this job but they cost more than server.

[Vaughan]

I have been given the task of enabling the database to be accessed by multiple users simultaneously.

Ok, the FileMaker runtime cannot share files. You've worked this out.

I haven't taken the time to read your whole post, but I have read enough to reaslise that what you propose with the runtimes and Excel files *might* be possible, but it is really going about it the hard way. I can see it only ending up in tears.

As Barbara suggested, buy FM Server and share the database. Put FM Server on its own box dedicated to the task. Set up FM Server to automatically backup the files every couple of hours or day, whichever you prefer.

Put a copy of FileMaker Pro on each user's desktop computer. They will use this to access the shared file.

The cost of doing it right with FMS/FMP will be significantly less than the cost of making the hack with runtimes and Excel work, plus the amount of downtime when things go wrong and need to be fixed etc.

[comment]

Consider the following scenario:

11:05 User A has modified the title of a document.

11:06 User B has deleted the record of this document.

11:07 User C has modified the description of the same document.

Now what?

[bcooney]

I am happy to see that others support me on this. Honestly, I'm not usually so blunt. I just think that you are being put in an impossible situation and if this data is at all valuable to the firm, then they need the appropriate setup. And, as comment has pointed out, synchronizing is not as straightforward as it appears.

Sometimes we answer how to do something on the forum without knowing why you need to do something. In your case, perhaps someone will chime in to help you build this house of cards, but I feel it's a disservice to you and your company to encourage such a Rube Goldberg setup.

[comment]

A possible setup for very limited budgets:

Make one person/station responsible for making all the changes. Others can only submit requests for changes. Maintain one file that is kept up-todate, and distribute it periodically as read-only to the other stations.

[bcooney]

That is the best fall-back solution.

[Kris M]

Maybe some of the experts here could enlighten the originator on how to argue for the server based solution?

Just thinking out loud.

K