3 Replies Latest reply on Mar 26, 2014 4:45 PM by philmodjunk

    Converting From Multi File System to Single File with multiple tables

    PhilCornish

      Title

      Converting From Multi File System to Single File with multiple tables

      Post

           I am getting ready (I think) to convert from an old related file system to a single file tabled system. Do you know if a good step by step article I could read about this? Any other advice? I think I understand what I must do, but there are surely pitfalls. Thanks!  I hear I might need (or want) FMPro Advanced?  Can I do without that?  Or will having that speed things up?  Am I stupid for trying this without a professional?

           Thanks!

        • 1. Re: Converting From Multi File System to Single File with multiple tables
          philmodjunk

               Much depends on how complex a database system that you are converting. When FileMaker 10 was state of the art, I took a system of FileMaker 5.5 files (over 50 files of one table per file), converted them to .FP7 file format and then merged them down to less than a half dozen. It can be done and while you can do it with FileMaker Pro, FileMaker Advanced's database design report is invaluable in monitoring your progress and looking out for problems that might creep in during the process. Just to give one example, you can do a text search of this report to find every instance of "missing" or "unknown" to track down each case were a reference to a field, script or table occurrence has been broken during the conversion process.

               To move a table from File 1 to File 2 is actually quite simple, it's the rest of the details that become very tedious and require careful attention to detail.

               With FileMaker Pro or Advanced, you can use import records to create a new table in File 2 populated with all the data from a table in File 1. You simply select the "New Table" option as the target table in the Import Records dialog. (With FileMaker Advanced, you also have the option to copy a table definition in Manage | Database | Tables of File 1 to paste into Manage | Database | Tables of File 2. This brings over the table but leaves out the data.)

               But this does not reproduce any of relationships, layouts, scripts, value lists.... And calculations defined in calculation fields and auto-enter calculations will be encapsulated in /* comment */ brackets if they refer to something, such as a field from a related table, that does not exist in File 2.

               Thus the first steps are:

               Import the Table

               Recreate or update any additional table occurrences needed and link them correctly in relationships. In many cases, this is a case of double clicking table occurrences that use an external data source reference to the original table in file 1 and changing it to refer to the new table you have created with the import.) It can be helpful to click one such table occurrence, then select the "select tables with same source table" option in the button to the immediate left of the percent magnification box and then select a unique  color for them to color code all occurrences with the same data source table so that you don't miss one that needs updating.

               Then go to the layouts in File 1 that refer to this table and identify each value list used with them. You'll need to carefully recreate each with identical names (copy and paste where you can).

               Then you can follow the steps in this thread to replicate layouts and import scripts while keeping most of the buttons in the pasted layout objects still connected to the correct scripts: Importing Layouts

          • 2. Re: Converting From Multi File System to Single File with multiple tables
            PhilCornish

                 Thanks PhilMod, new part to this question: We have multiple licenses of 11 here...can I buy Advance 13, do my thing, and all the other folks with 11 will still be able to use it?  Or do we all have to get to 13?  Thanks!

            • 3. Re: Converting From Multi File System to Single File with multiple tables
              philmodjunk

                   The file formats are different so your FileMaker 11 users would not be able to open the file once you have converted it. FileMaker versions 7 - 11 used one file format with the .fp7 file extension and versions 12 and 13 use a new file format with a new file extension: .FMP12.