10 Replies Latest reply on Feb 14, 2017 10:57 AM by dsimpson_dcsi

    consolidating several files into one with multiple tables



      We have an old multiple file solution (26 files) that we migrated to FMP15.  Is there a Best Practices guide to consolidate these multiple files into one or at most a few files using multiple Table Occurrences?  Thanxs

        • 1. Re: consolidating several files into one with multiple tables

          This old document may be of some assistance.

          2 of 2 people found this helpful
          • 2. Re: consolidating several files into one with multiple tables

            tech brief as Mike suggested. Time for a clean new from scratch build, and re-think/appraise the db logic.

            Have a look at data separation as well; basically 2 files;  a_interface file and b_data file

            1 of 1 people found this helpful
            • 3. Re: consolidating several files into one with multiple tables

              Thank you both.  We were thinking along those lines.

              • 4. Re: consolidating several files into one with multiple tables

                Merging single table files into a multi-table file is possible. Whether it makes sense depends on a lot of different variables including your own expertise and what developer tools you have to use. FileMaker Advanced would be the minimum requirement here. I once merged a 20+ file FMP 5.5 into just a few FMP 10 files several years ago so I've been down this path.


                This is not a full up, step by step description of how to do it, just a kind of outline of the steps involved and basic tools:

                1) You can easily replicate a table defined in one file in another:

                     a) In FMP advanced, you can copy and paste table definitions from one file to another.

                     b) In FMP Pro or Advanced, you can use Import Records with the new table option to pull in both data and field definitions.

                     c) Neither method replicates relationships--which have to be manually recreated. So calculation fields in the newly replicated field will be enclosed in /* comment brackets */ to keep from tripping errors. You have to pull in the other Tables and recreate the relationships before editing the calculations to remove the comment brackets.


                2) Scripts can be imported or their steps copy/pasted. But be aware that references to objects in the old file, such as layouts, table occurrences, other scripts etc that do not exist in the new file will be broken until you fix them.


                3) Value list definitions cannot be imported. Best you can do is select all the custom values to paste into the definition of a new value list in the new file.


                4) You can select all layout objects on a layout in the old file and paste them into a new layout of the new file but:

                     a) first, base the new layout on a table occurrence with exactly the same name as that of the original. Make sure it's a replicated copy of the original table so that it has all the same fields with all the same names.

                     b) Button objects that refer to scripts not yet imported will be broken and scripts that refer to layout objects will also be broken. The trick is to paste the layout objects twice. Paste them once, then import your scripts, then delete all the pasted objects and paste again. Your buttons and scripts then don't have broken references to each other.

                     c) All value lists used in the layout need to be created and given exactly the same names before you paste layout objects or these references will also be broken.


                5) At a minimum, you'll need to rely on FileMaker Advanced's Database Design Report to double check for broken references. A 3rd party developer tool such as Base Elements or Inspector would be even better.


                So merging multiple single table files into just one can be a lot of work. But since redefining your file from the ground up is also a lot of work, it makes sense to me at least that you carefully consider both options.

                1 of 1 people found this helpful
                • 5. Re: consolidating several files into one with multiple tables

                  I'm in the middle of converting a 50 file FM5 solution so I feel your pain. It's a "rebuild" not a straight conversion. A

                  conversion isn't really worth it since you need to rebuild the layouts, scripts, and layouts anyway. Especially if you're not using an outside tool, like MetaData Magic.


                  You've got some great advice already from philmodjunk and @Mike_Mitchell.


                  The only things I would add are (if it's a rebuild)

                  1) You can actually copy the table itself, not just the field definitions.

                  2) For the data import, use an intermediate file. Copy and paste the old tables into the intermediate table, add file references to the new file and do all your imports scripts in that intermediate table. Then when you're done, you won't have all that clogging up your new system. You won't need that intermediate file anymore.

                  3) It's likely you'll need extra fields, relationships, and script actions (mostly Replace[] in the intermediate import file to handle converting from old style keys to new UUIDs or auto-enters and Delete All Records[] clear out data as you test the imports.

                  4) I convert all Lookup fields to Auto-enter calcs in the new file.

                  5) Security is a bazillion times more improved. Use accounts and privileges!

                  6) I'm not sure how MetaMagic handles the Classic layout theme, but you want to avoid it at all costs.

                  1 of 1 people found this helpful
                  • 6. Re: consolidating several files into one with multiple tables

                    one other thing to add, is that however things were approached back in the days of multiple (x26) files, it is somewhat unlikely that many of the processes (scripts...) , or even data model o some extent, would be how one would approach the equivalent data manipulation today. Depending on who wrote it, with what level of expertise of course.


                    Look out for wide tables, too many fields in a table, common in many I have dealt with.

                    Look out for repeat fields, and iterative fields, also common practice back when; plan to normalise.

                    Also look for copy and paste script steps in old db, a common poor practice ( in c. fp5 days) , and generally a punished one on conversions.


                    I would argue that converting the old db into a current format, is actually a worthwhile and simple enough process. The result is a working model, against which to compare, whatever you build anew.

                    1 of 1 people found this helpful
                    • 7. Re: consolidating several files into one with multiple tables

                      This is especially true when we talk about scripting. I once had a system I converted in this manner. A script that took around 150 pages to print was consolidated down to 2 pages. The primary reason what that, by consolidating the business logic into a single file, scripts that used to have to be segmented between multiple files could be pushed into a single script.


                      Adding to Phil’s list, I would say that the order of operations is important when copying / importing objects. To make it as painless as possible, do things in this order:


                      1) Custom Functions

                      2) Tables

                      3) Relationships Graph (i.e., consolidate, re-establish relationships)

                      4) Layouts

                      5) Scripts


                      This approach gives FileMaker the best chance of evaluating the names of objects as they’re pulled in, and will avoid at least some of the commenting-out of stuff Phil mentioned.

                      2 of 2 people found this helpful
                      • 8. Re: consolidating several files into one with multiple tables

                        If you think of it as migrating the code from the old system to the new system, Todd Geist's code migration checklist should be helpful:

                        Checklist For Moving FileMaker Code - geist interactive


                        It breaks down what to move and in what order to minimize breakage.  Check it out.

                        • 9. Re: consolidating several files into one with multiple tables

                          I cannot thank you guys enough! This is my first time participating (ok, asking for advice!) in the forum, and I greatly appreciate your generous insights.

                          I will certainly be on the lookout for "Copy" and "Paste" routines, as our initial dbase dates from the days of good ol' FMP 2.0, although the bulk of development was on FMP 5.

                          Two other areas of concern are sorting and loops, both big time hogs.

                          Thank you again everyone.


                          • 10. Re: consolidating several files into one with multiple tables

                            There is a wealth of info in these answers. I especially like the ideas of re-writing the original database. Mike's note about rewriting a 150 page script down to 2 pages is especially instructive. We have lots of ways to reduce code with newer FileMaker versions, like passing parameters and just basically not having to have little bits of code scattered among separate files will help a lot.


                            If you need help with the just the boring mechanical process of combining the files together into one single file, my FmPro Migrator software can help with this task. I have recently re-tested and updated the automation scripts in the Table Consolidation feature supporting the latest FileMaker versions.