8 Replies Latest reply on Mar 12, 2009 2:43 PM by CarrieB.

    Merging database files?



      Merging database files?


      Is it possible to import an entire database into another? Tables, fields, layouts, everything? I'd like my database to work out of one file. I know I can add all this information manually. I was just wondering if there was a quicker way to do it. Also, is there any compelling reason to keep your files separate (i.e. Contact Management and Purchase Orders)? Is there a point where there is too much in one file that it won't work as well?



        • 1. Re: Merging database files?



          I'm in the final stages of upgrading and merging several massively complex FMP 5.5 files, so I've done this a lot....


          No, you can't do it all in one step. It depends on what you mean by "I can add all this information manually."


          You can:

          1) Import a table, creating the new table definition and pulling in all the data in one operation

          2) Create a new layout with the correct table reference and then copy and paste all layout elements in a single step. (You may have to create and reposition layout parts before or after you paste the layout elements.)

          3) You can import all scripts in a single import.


          You can't (as far as I've been able to figure out) import the relationship definitions but instead have to use Manage Database to recreate them "brick by brick". This can be very, very tedious and requires painstaking precision to make sure all details are correct.


          You may already be doing this. Here's a few tricks I've learned along the way....

          1) Examine the relationship graph for the file that will receive the imported table et al. Identify the table reference that will be replaced by your table import and rename to something unique.

          2) Next, Import the table. If you have calculation fields that include references to other tables/relationships, they will be enclosed in /* */ comment symbols.

          3) Print out the relationship map for the source file you are merging. Open up the relationship map for the file where you imported the table, place the new table reference adjacent to the original reference from step 1. Locate all table references that refer to the original external table and repoint them to the newly imported table, being careful to keep Filemaker from automatically renaming the table reference. (Copy the current name to the clipboard, select the new table, paste copied name.)

          4) Now it gets really tedious, carefully recreate each relationship from the original file. You need to match every name precisely and define all relationship details identically.

          5) Go back to any "commented" calculation field definitions and remove the comment symbols (/* */). If you've done step 4 correctly, you shouldn't get any "field not found" messages when you click "OK".

          6) Recreate all layouts through copy and paste.

          7) Import all scripts.

          8) Review all imported scripts to fix any issues

          9) reconnect all pasted buttons to the correct scripts.

          10) Test everything thoroughly to catch any mistakes.


          If you are importing more than one simple table or the table has many relationships, GET FMP ADVANCED! the Database Design Report, flawed as it currently is, will be a major life saver in finding layout and script reference errors.


          Phil Caulkins

          Modesto Junk Company

          • 2. Re: Merging database files?

            Thanks for the info! I will look into it. I'm just starting this database, so I don't think it will be too terrible. I would like to start with some of the built-in templates though, but want them to be in the same FileMaker file.


            When you say you can import scripts in one step, do you mean by using "copy and paste"? 


            Thanks again!

            • 3. Re: Merging database files?

              CarrieB. wrote:

              Thanks for the info! I will look into it. I'm just starting this database, so I don't think it will be too terrible. I would like to start with some of the built-in templates though, but want them to be in the same FileMaker file.


              When you say you can import scripts in one step, do you mean by using "copy and paste"? 


              Thanks again!

              No, I am referring to selecting Import Scripts from the Script Manager. (Look for the button with the arrow on it.) Using this tool you can open another file and click check boxes to select which files you want to import into your current file. When merging files, I create a new script folder and then, after first recreating all the required layouts, import the scripts into this new folder.

              • 4. Re: Merging database files?
                   Okay, awesome. I will look at that too. Thanks for your help! =)
                • 5. Re: Merging database files?


                  When I started developing my current solution I had intended to market it and thought separate files would allow a more modular approach (relatively simple to script or manually create different versions). Having decided against marketing  the solution I now see advantages to both approaches. Global variables can be used more effectively in a single file, for example. A reason to use multiple files would be the limitation on the number of custom menu items, which I believe is 100 per file. My solution requires, in all, many more than that. I'm sure there are many other pros and cons on either side. An interesting thread.



                  • 6. Re: Merging database files?

                    Interesting. My thought was a little opposite. There is a chance I may consider marketing the new database I am working on and I thought one file would be easier. =) I have a database running in my office now and the multiple file thing seems to throw my users off. Originally, I had the "main menu" open all needed files, then place the windows on top of each other and just pull the current one forward. Well, my users would start moving the windows around or trying to minimize the windows and then see all the windows that were actually behind the current window. So, I changed my current file to not open all the files originally, but just as requested. Then, hide windows when moving from one to the other. This seems to work well for now, but I thought it would be nicer to house all the information in one file. But, like you said, I'm sure there are pros and cons to both scenarios.


                    Thanks again for all the input!

                    • 7. Re: Merging database files?

                      Yes, the multiple windows can be an issue. My solution consists of five files. All actions are pretty tightly controlled by custom menus and basically there is never more than one window per file open at a time. I also script sizing and placement of windows to a certain extent and this helps with clutter. . . . then again I use two 22" monitors so it's not a big issue for me. I decided not to market my solution for a couple of reasons. My software runs my orchestra contracting business painlessly and efficiently, but it's very specific. If every orchestra contractor in North America purchased a copy, even at a hefty $500.00 (?!) price tag, I would still have a very small user base and little income from sales. When I say "little income" I measure the potential income against either the cost or hours required to provide support. It's something to think about before marketing even an excellent product.



                      • 8. Re: Merging database files?

                        I'm so new at this. It will be a while before I have a marketable product. It's not even my idea anyway. I'm helping my husband make a database for his machine shop and he thinks others might be interested in a similar product.


                        I haven't spent a ton of time on custom menus because I've basically limited my users to button pushing. Guess I would have to think about that for a product that people may not set users up for. Hmmmm ..... so many things to look at. I'm so glad there is a forum like this to help! :smileyvery-happy: