10 Replies Latest reply on Sep 29, 2009 2:47 PM by KenS

    Best way to work on FileMaker database whilst it is in use at different location

    jamie.hunt

      Title

      Best way to work on FileMaker database whilst it is in use at different location

      Post

       

       

      I have created a FileMaker Database for our company. It holds guests information, a record of their previous reservations and tracks which services and retail each guest enjoyed.

       

      The problem I now have is we are open 6 days a week. I would like to further develop the database however I do not know the best way of doing this without messing up the records within it as I need to work on it whilst it is being used. 

       

      I have tried saving a copy, working on it at home and then importing the records the following day. This however caused problems as each guest is given their own id on creation of their profile. 

       

      I imported 34 records that were created on the file still at work into the updated file i worked on and then found that I had 34 duplicate client ID's as the updated file carried on from where it left off. I hope that makes sense.

       

      Basically whats the best way of updating, making changes to my database, without causing havoc with the records in it because I want to work on it in the office while its still being used on the front desk.

       

      There must be a standard method of doing this?

       

       

      I am using FileMaker pro 10, Max os X and this is the first FileMaker solution vie made.

       

       

      I hope someone can help.

       

      Thanks

        • 1. Re: Best way to work on FileMaker database whilst it is in use at different location
          philmodjunk
            

          Here's a general outline that can work for many situations.

           

          1) take a copy down off the server. You can modify the database while it is in use if you have a full access password, but it's sorta like repairing your car while driving it down the freeway. Not something you want to do except for minor changes or when no one else is accessing the database.

           

          2) write an import records script that copies all records from all tables in the old file to matching tables in your new file. (Use matching field names in your script it's much safer than any other field name matching.) Set up your import so that auto-enter options are NOT enabled.

           

          3) Examine your database fields for any auto-entered serial number fields. You'll need to either manually update these or include code in your import script to update them automatically.

           

          4) Modify your database as needed.

           

          5) Close your shared database file so no one can modify it.

           

          6) Open a clone of your new file and kick off the script to import all data from your current file (this may take a while).

           

          7) Per step 3, update any serial number field settings

           

          8) Test your file to confirm all data is where it should be.

           

          9) Upload your new file and open it for use.

           

          Step 6 can be a challenge for large databases. Depending on the type of data in your database, you may be able to do this step in stages. (stage 1, import all old/unchanging data, Stage 2 import all recently changed/added data.)

          • 2. Re: Best way to work on FileMaker database whilst it is in use at different location
            jamie.hunt
              

            Thanks for your help.

             

            It first glance this all seems quite complicated, however I have only recently started using FileMaker.

             

            I'm sure I will have to test this out on a saved copy before I dot it on the real thing!

             

             

            Thanks for the advice. 

            • 3. Re: Best way to work on FileMaker database whilst it is in use at different location
              mike.white
                

              I have the same problem but worse: the production database is at another location on a separate network.  Accessing it means driving 45 minutes etc.  What I'd like to do is split the database into two parts: one file that contains all of the tables, and the other that has all of the layouts, scripts etc.  IThe only way I have found to do this is to make a copy of the database (to be the front end part), remove all of the tables (thereby breaking all links in the layouts), and then redirecting all of the layout parts to point to the same parts in the original file.  With hundreds of fields and dozens of layouts this will take a huge amount of time, not to mention the errors that I might make along the way.  Is there anyway to simply remap the old fields to the new external ones?

               

              I am using FM Pro Advanced v10 on Windows XP.

              • 4. Re: Best way to work on FileMaker database whilst it is in use at different location
                philmodjunk
                  

                Take your database copy that you want to be your "front end".

                Open Manage Database | Relationships

                For each table occurrencein the graph:

                  Double click the Table Occurrence

                  Select Add filemaker data source from the data source drop down

                  Locate the "back end" copy of your file and select the matching table from this file.

                  (I'd change the color of Each TO as I do this to keep track of which TO's have been redirected).

                 

                Done

                • 6. Re: Best way to work on FileMaker database whilst it is in use at different location
                  philmodjunk
                     Just watch out for cases where the file locations at the time you split them are different from what you'll have when you set them up on the server. (The front end file can be hosted from the server or copies can be distributed to each client computer). You may need to open Manage | External Data Sources... and update the file reference to include an IP address or DSN name. (If both files are hosted from the same folder, a simple relative path reference File: Filename will work.)
                  • 7. Re: Best way to work on FileMaker database whilst it is in use at different location
                    mike.white
                       Thanks for the advice.  I'll be sure to watch those paths.
                    • 8. Re: Best way to work on FileMaker database whilst it is in use at different location
                      KenS
                        

                      I am working on the same issue Mike, but I've been working with the split files for a while. When I first made split my file into two I unknowingly created a complication for myself. Duplicating the file and re-directing the data as described above is fine, but dont forget all the relationships now exist in both files. If you are not careful you'll end up pulling your hair out debugging something in the front-end that is happening in the back-end. I am going through a re-design and I plan on going back to the back-end file and deleting all the relationships, making the split all the more clean and clear. I dont know all the ins and outs of this by any means, maybe someone more knowledgeable can provide more clues for us.

                       

                      Also, if you are using accounts, both files have to be set up with the same accounts. I'm no expert at the accounts, but it seems if both files have an account with the same name and password it acts invisibly to the user. If not the user will have to provide acct/pwd twice, once for each file. Having created a couple accounts by hand, I'm ready to write myself a script to handle adding new users.

                       

                       

                      • 9. Re: Best way to work on FileMaker database whilst it is in use at different location
                        philmodjunk
                          

                        You may not want to delete the relationships. If you have calculation or validation type expressions in your tables that rely on a relationship, removing the relationship will break it.

                         

                        It's possible to purge the Back End of everything but the tables and needed relationships. Layouts, scripts, value lists can be purged as they all now live in the "front end". Whether this is a good idea or not depends a bit on how you plan to set this up. I'd at least keep one table view layout for each physical table in the back end just so I can view the data directly.

                         

                        With all these kind of operations, it's a good idea to make frequent back ups so you can "revert back" if you mess something up.

                        • 10. Re: Best way to work on FileMaker database whilst it is in use at different location
                          KenS
                             Right! Thats what I wasnt quite remembering. Thanks for the help PhilModJunk...