2 Replies Latest reply on May 17, 2010 10:09 AM by Storm

    Split one table 164 fields into several tables



      Split one table 164 fields into several tables


      New Here so pls. forgive ignorance. Over the years I have added fields to my database expanding inventory and now my one table has 164 fields, which makes it more and more difficult to manage - although it works. How do I remdodel the 1 huge table with 3000 records into 6 smaller related tables the fastest way? Do I import relevant record info from 1 old table into each of 6 new tables or what? I have laboured countless hours over this, so many thanks for help and input.

        • 1. Re: Split one table 164 fields into several tables

          Yes, you would Import them into the new table. You can create the new table at the same time, by specifying the "New Table" option (at the bottom of the Target drop-down, in the Import dialog). If you have FileMaker Pro Advanced, you can alternatively Copy/Paste selected fields into a new table (which you create first), then Import.


          However, whether you should do this, and which fields you should do it with, are serious questions. You do not split a table into related tables unless there is a sound relational structure reason for doing so. In almost all cases a field belongs in a particular table.


          If you are using repeating fields for real data, then yes, those should likely be a child table. If you find yourself using several self-relationships to filter and manipulate data which is the same in multiple records, then possibly you need a parent table (much less likely). 


          It is fairly easy to move repeating fields into a child table. FileMaker will ask you when you Import repeating fields, whether you want to split each repeat into separate records. If you include the primary ID of the parent table in the Import,* and build a relationship on it, you'll have a linked table which works much the same as the repeating fields. On the layout, you can point the existing repeats to their corresponding new non-repeating field (based on the above relationship), then surround them with a portal (ditto re: relationship). If it has the same number of rows as the repeats did, it will occupy much the same space on the layout (couple pixels more for the portal), and work much the same.


          * The primary ID of the parent table will be a "foreign key" in the child table. It will NOT retain the auto-enter options of the main table; it will usually be just a plain data field. Whatever mechanism you use to create new child records, [x] Allow creation in the portal's relationship, or scripted, must populate this field, to create the link back to its parent.


          • 2. Re: Split one table 164 fields into several tables

            Beautiful. Thank you so very much. I will try this out and see what happens. I'll report back.


            Many kind regards,