6 Replies Latest reply on Dec 3, 2010 1:41 PM by philmodjunk

    Changing database - what is easiest method?



      Changing database - what is easiest method?


      I need to work on a database for a customer, but they need to keep using it. In the past I have just copied their DB, worked on it, and returned (via Dropbox). But now they need to use it daily and cannot relinquish it to me.

      So…. What is the best way for me to move my updates (new tables, reports, and scripts) into their existing DB?

      I see that I could empty my new DB then do an import from the original DB file. But i see some problems here. The first two being the big ones.

      1- Tedious to do with more than 20 tables. I do not seem to be able to import except to the current open table. So I would need to repeat the import 20 times.

      2- Container fields not copied. There are not many of these, but still it would be a hassle, especially if we have to repeat it.

      3- I am not sure how the summary and calculated fields will work. I assume that they will still calculate the same way they did prior to import. Many of them are calculations on calculated fields so I do worry about this. It would be very time consuming to fix anything that broke.

      It certainly would be easier if I could take the original DB and just import the new schemas, scripts, etc.

      We are using FMP 10 - standard addition.

      I appreciate any advice here.

        • 1. Re: Changing database - what is easiest method?

          Thanks in advance!

          • 2. Re: Changing database - what is easiest method?

            In the future, you may want to use the Data Separation model. You split your solution into two files. File one contains all the scripts, value lists, layouts needed to supply the user interface. The table occurrences in the Relationship graph of this file refer to external data sources (tables) in File 2, the file where you define all the tables and where your customers store all their data. Since most updates and upgrades only affect the interface file, you can usually just deliver a new interface file and use it to replace the original--no imports of data required.

            For your current solution and for future updates to the data file, you can script the import process to move from table to table and import all the data. This will also import the contents of container fields successfully. This script should also include code to update the next serial values of any serial number fields so that new records won't get the serial number of an existing record. This still takes time to import all the data, but you can just perform the script and come back later when it's done.

            • 3. Re: Changing database - what is easiest method?


              Thanks for the reply. Very well done.

              Couple of questions, however.

              In importing via script, do I need to have the script import the container objects specifically? Or will that happen automagically with the import.

              Second - It seems that trying to implement the Data Separation model at this point would be excruciating, since every time I tried to change a relationship, it would break the old relationships without rebuilding them. Therefore everything would fall apart. Is this an accurate description of how it is?

              Thanks again 

              • 4. Re: Changing database - what is easiest method?

                With Filemaker to Filemaker Imports, container fields import just like any other field.

                Converting a single file into a split, data separation model isn't all that hard to do.

                Make two copies of your file and change one into your interface file, using the other as your database file.

                Open your interface file and select Manage | Database | Relationships.
                Double click each table occurrence box and use the Add FileMaker Data source option in the drop down to connect it to the correct table in the second file.

                Once you've done that for every table occurrence, you can delete all the tables listed in the tables tab of your interface file and you now have a data separation system. You can certainly go to the data file and delete layouts, scripts and such if you want, but you don't have to.

                • 5. Re: Changing database - what is easiest method?


                  Thanks once again. I have tried it and it seems to be working fine. Since you have already been so kind, let me take advantage of you a little more :P

                  If I set a table in the relations to a table in file XYZ_data in the same folder, I am assuming that it will always look for a file by that name in the same folder, even if both files are moved. Is that correct? (I.E. there is no other method it uses to id the file.)

                  • 6. Re: Changing database - what is easiest method?

                    You are generally correct, but there can be exceptions. The default connection is via "relative path" and that's what you are describing.

                    You can see how a given file finds the files it needs to connect to in Manage | External Data Sources.

                    A link like:


                    is a relative path reference and this is the most flexible, trouble free way to link a file in FileMaker Pro.