5 Replies Latest reply on Mar 13, 2014 9:28 AM by philmodjunk

    Can I update multiple copies of a database with changes I make in one?

    CoraWoolsey

      Title

      Can I update multiple copies of a database with changes I make in one?

      Post

           Hi there,

           I work with multiple copies of a database: one is a master copy, where records from the other databases eventually get put, but each of the other databases are concerned with a subset of records and it makes more sense to keep them separate for analysis. The trouble is, I change one to better capture the data I am looking for, and then I have to change the others. This is very bulky, and I can't keep up. Is there a way to save changes in one to all the others?

           Thanks in advance,--------------Cora

        • 1. Re: Can I update multiple copies of a database with changes I make in one?
          philmodjunk

               It sounds to me like it doesn't make sense to keep them separate. You can analyze data without putting them in separate tables.

               And wouldn't it make more sense to enter all the data into a single unified system and then, if you truly do need that copy export the data from the centralized, up to date file rather than the other way around?

               The only time it really makes sense (as far as I can see) to set things up like you have is when you need to use mobile devices to collect data in environments where you can't rely on a good wireless connection back to the hosted copy of the database.

          • 2. Re: Can I update multiple copies of a database with changes I make in one?
            CoraWoolsey

                 I was afraid you were going to say that. One of the issues is something like what you mentioned: more than one person often works on the database at a given time, and one of my jobs is to keep improving the database even as others are entering data. The other problem is that the amount of data (250,000 records and 200 fields spread across ca. 15 tables) is rather large. It can sometimes get a bit bulky trying to sort records and specify (repeatedly) only the subset I want to look at. But if you are telling me that multiple copies is just not feasible, I will believe you and try to unify the copies I have.

            • 3. Re: Can I update multiple copies of a database with changes I make in one?
              philmodjunk

                   I didn't say it wasn't feasible, but that it doesn't seem like the best design approach. It still doesn't. (Some of my tables now hold millions of records. They do need to be treated differently than tables with fewer records but it can be made to work.)

                   

                        more than one person often works on the database at a given time, and one of my jobs is to keep improving the database even as others are entering data.

                   The other users should all be accessing a hosted database so that all data is entered into a single, regularly backed up file or set of files. YOU should be making changes to a copy and should periodically use an import script to pull the current data into your new copy for deployment back onto the server. (Some small changes can be made while the system is hosted--such as modifying a layout. Others should NEVER be made to a file while others use it to enter or edit data--such as making changes in Manage | Database--even opening the specify calculation dialog for an auto-entered calculation just to look at the data can lock all other users out of making changes to data in that table until you exit.)

                   To minimize the need for data imports--which can be scripted or even done incrementally with tables with large numbers of records (in some cases), use the Convert to Seperation Model so that you can modify the interface--the layouts, scripts, etc in a copy of the file without needing to import any data. Only changes to Manage | Database will require an import.

              • 4. Re: Can I update multiple copies of a database with changes I make in one?
                CoraWoolsey

                     Okay, I see that there are some good reasons for using the server version, but there are a few reasons why I don't really want to: 1) I already purchased the basic FMP version, and don't really want to buy another one (I am not a business, just a poor PhD student). 2) Most of the time, it's just me entering data in a lab, with an assistant helping me one day a week. 3) Consistent internet connections is definitely a problem, especially since I travel to analyze collections in other cities. 4) I hope to eventually leave copies of the database with other institutions as a means both of managing records they have received from me on their collections and also for inputting their own data, and I would like a way to update their versions as I develop new versions. 5) It kind of seems like overkill considering that I'm the main person that uses it.

                     Additionally, it seems like maybe you are saying that a server version actually wouldn't work so well for someone editing the database while someone else is inputting data. Please tell me if I have misunderstood any of this; I am much less familiar with the server version.

                     So, do I understand correctly that there is no easy way to update or merge copies of databases?

                     I will read up on that thread you recommended about a data separation model.

                • 5. Re: Can I update multiple copies of a database with changes I make in one?
                  philmodjunk

                       There are options, but there is a cost involved in terms of complexity and a real chance that a mistake could create major data integrity issues for your data. Thus, you don't want to go there unless there is a valid reason for doing so. Your last post if the first to provide what I see as valid reasons for your design.

                       It Doesn't matter if you use the server version or FileMaker Pro to host your database. (FileMaker Pro can host a DB for up to 5 other users if this is FileMaker 13, up  to 10 if this is FileMaker 12 or older) There are risks to modifying the design while others use it. Think of it as trying to change the car's spark plugs while driving it down the freeway: wink

                       And FileMaker 12 could publish your database with Instant Web Publishing so that others could use a web browser to access the DB--again there are trade offs and this is not an option with FileMaker 13.

                       You can use Import records to Merge/update your date. But how you modify data makes a big difference as to whether this is simple or complex. It's why two companies (360Works and SeedCode) offer third party produced Synch tools. I think SeedCode offers a limited demo version, so you may want to check that out.

                       If you have to "roll your own"--as seems likely given your financial picture, you'll need to carefully look at how you create and modify data in your database. If copies of the DB are only used to create new records, the "merge" process is simple, you just use Import Records to import the data back into a central copy of your file. But if two or more users might modify the same record in two different ways at the same time in two different copies of your DB, merging that data so that both changes are merged is difficult and can require a lot of special design effort. (FileMaker can set a timestamp field with the date and time each record was last modified to help identify which record was last modified to compare to a date and time the data was last merged, but that doesn't tell you HOW the record was modified...)

                       The fewer copies, the fewer cases where two users might modify the same record at the same time, the simpler this process becomes. But the converse is also true.