This is a public Forum  publicRSS

Forum post

    FlyByNight
    Move or copy a table from one DB to another?Answered
    Forum post posted January 7, 2009 by FlyByNight, last edited February 9, 2012 
    1375 Views, 5 Comments
    Title:
    Move or copy a table from one DB to another?
    Post:

    I have some old solutions that I built before FMP allowed multiple tables in one database file. Most of them are simple little tables that I use for lookup relationships. Is there an easy way to move those tables from an external file into the main file? Not a big deal, I just would like to only have to keep track of 1 file instead of 3-12 files for each database.

    Thanks for any help! 

    Best Answer

    DavidAnders

    Posted with permission of author -

    Darren Terry Wed, 7 Jan 2009 FMPexperts Digest, Vol 11, Issue 18

     

    Yes. Here are my thoughts on conversion from .fp5 to .fp7 format, and file consolidation.

    In this discussion, I'll use some specific nomenclature that is important to understand. "Master File" is the file you're consolidating tables into. "Target File" is the file whose table you are moving into the Master File. "Target Table" is the table you moved from the Target File into the Master File. Here are the steps. First, the conversion itself:

     

    1) Before you do anything, make a backup of the solution to be converted. That seems obvious, but I can't stress how important it is. I recommend converting clones with no data -- it's much faster to convert a clone and import the data in, than to convert with data (and have FMP convert the indexes).

    2) Then, before converting anything, go through each .fp5 file and make sure the passwords match in each. The .fp7 file format uses case-sensitive passwords, whereas the .fp5 format did not. I've been bitten by this more than once.

    3) Then, run the .fp5 files through MetadataMagic from New Millennium Communications. In particular, run the File Reference Fixer to consolidate all file references down prior to conversion. Also run the Conversion Issues report, and go through the issues one by one and fix as many of them pre-conversion as you can.

    4) Then drop-convert the files (that is, drop their icons onto the FMP 9 or 10 app icon). Regardless of whether you're planning to consolidate the files down to 1, or leave them as is, or somewhere in between, the first 4 steps of any good conversion project (IMO) are the same as above.

     

    You can deploy the converted multi-file system without consolidating, and consolidate it later. Consolidating files together can be surprisingly easy if you do the steps in the proper order. In a live solution, the biggest problem is kicking everyone out of the solution so that they're not modifying data while you're trying to consolidate.

     

    Here's the order that I'd do it in (assuming you've already converted the solution and it's been in place for awhile as separate files, and that you want to consolidate it down to one file).

     

    1) First, pick the Master File. This is the file into which the rest of the solution will be consolidated. There is no need to think you're going to do it all in one fell swoop. You can do it piecemeal one file at a time.

    2) Once you've picked the master file, decide which other file you want to consolidate into it (the "target file" ). Go to the Master File and import from the target file. In the Import Mapping dialog, for the destination, choose "New Table". FileMaker will import the records and define the fields (in a new table) for you.

    3) Once that's done, go to Manage Database, to the graph. Find every table occurrence that is currently aimed at the target file's table -- the target table (these will all have italicized names because they are aimed at the target file). For each one in turn, double-click the TO. IMPORTANT: before going further, copy the TO's name from the field at the bottom of the Specify Table dialog. Notice that the popup menu at the top of the dialog says the target file's name. Click there and choose "Current File", then highlight the newly-imported table. Notice when you do that FM has "helpfully" renamed the TO to the target table's name. Just highlight the TO name and paste the original name back in. Repeat this for every TO that used to be aimed at the target file.

    3a) You should pick one of those target TOs to be the "main" TO for that newly-imported table of data -- the one that most of that table's layouts will be based on. Usually in any given solution, there's one particular relationship that is the main or primary relationship between the Master File and the Target File -- it's usually the one related by a single primary key. I would pick that TO to be the main TO for the target table.

    4) In the Master File, define a new blank layout for each layout in the target file. Name them the same as the layouts in the target file. Pick the main TO to base the layouts on. Make sure they're blank layouts, and leave them blank for now.

    5) Now go to the target file and open Manage Scripts. Rename each script in there with a prefix that identifies the script as coming from the target file. For instance, if you're consolidating the Invoices file into the Companies file, rename each script from Invoices with a prefix that identifies it as coming from invoices ("INV" or something). Once you're done, go to the Master File and import the scripts from the target file into the master file. [The reason you prefix the scripts in a way to identify where it came from, is that it's very common to have identically-named scripts in different files of the same solution. For instance, there could easily be a script called "Import" in each file in your solution. You want to differentiate between them post-consolidation.]

    6) Now, go to the target file and copy each layout one by one, and paste its contents into the corresponding blank layout in the master file. Make sure the parts are all the same size, and that the layout objects are all in the same place. It's not very hard to do, really. I tend to rely heavily on the Object Info palette for this sort of thing, and nudge things around one pixel at a time with the arrow keys on the keyboard.

     

    The reason you want to define the layouts first, then import the scripts second, then copy the layout objects third is this: When you import the scripts, there will be references to layouts in them (like Go To Layout and Go To Related Record). In order to keep those script steps from breaking, the layouts have to exist in the file already by the time the scripts are imported. But the layouts will contain objects that reference the scripts by name (such as buttons). In order for the buttons not to break, the scripts have to exist in the file already by the time the buttons are pasted into the layouts. Make sense?

     

    Doing it this way will minimize (but not necessarily eliminate) any broken references that might happen during consolidation. Always go through everything and make sure nothing is broken. For instance, Go To Related Record script steps should reference the proper TO on the master file's graph, and the proper layout. Then, let your users start using the system. If all goes well, they shouldn't notice much difference.

     

    Well, if you do it out of order, you'll make your life more difficult than it needs to be. There are a pros and cons to consolidation.

    The list of pros includes (but this isn't an exhaustive list):

    - Unified security model (define accounts and privileges in one place)

    - All scripts in one place

    - You are leveraging FM's strengths. It's really designed for a consolidated data model.

     

    The cons include:

    - Single point of failure for your system. If the file gets corrupted, the WHOLE SOLUTION gets corrupted.

    - You can only ever back up the entire system, instead of backing up only pieces of it.

    - It complicates your life to have all tables in one file, if you ever need to update the system with an off-line copy. If you make a v2 of the system offline and it has 30 tables in it, you have to import all 30 tables of data into the offline copy when you put it in place. Ugh.

     

    FileMake.com Links ===========

    Converting FileMaker Databases from Previous Versions

    http://filemaker.com/downloads/pdf/fm8_converting.pdf

     

    Upgrading to FileMaker 8: Migration Foundations and Methodologies

    http://filemaker.com/downloads/pdf/techbrief_fm8_migrtn_found.pdf

     

    Upgrading to FileMaker 7: How to Employ the New, Advanced Security System

    http://filemaker.com/downloads/pdf/techbrief_security.pdf

    Answer

     

    • DavidAnders
      Filemaker Pro Advanced allows table import.
    • FlyByNight
      I only have FMP. Does that mean that I'm out of luck?
    • DavidAnders
      Yes, I am afraid so. At least as far as I know.
    • raybaudi

      > Does that mean that I'm out of luck?

       

      You can import the data of all the tables that you want even with the trial version.

       

      But you have to create empty tables with their fields, possibly with matching names.

       

      When you go to import, you have to chose where do you want to import ( upper right: target ) and wich fields to import  ( here matching names come in handy ).

       

      After the import of the data, you have to copy and paste the layouts.

    • DavidAnders

      Posted with permission of author -

      Darren Terry Wed, 7 Jan 2009 FMPexperts Digest, Vol 11, Issue 18

       

      Yes. Here are my thoughts on conversion from .fp5 to .fp7 format, and file consolidation.

      In this discussion, I'll use some specific nomenclature that is important to understand. "Master File" is the file you're consolidating tables into. "Target File" is the file whose table you are moving into the Master File. "Target Table" is the table you moved from the Target File into the Master File. Here are the steps. First, the conversion itself:

       

      1) Before you do anything, make a backup of the solution to be converted. That seems obvious, but I can't stress how important it is. I recommend converting clones with no data -- it's much faster to convert a clone and import the data in, than to convert with data (and have FMP convert the indexes).

      2) Then, before converting anything, go through each .fp5 file and make sure the passwords match in each. The .fp7 file format uses case-sensitive passwords, whereas the .fp5 format did not. I've been bitten by this more than once.

      3) Then, run the .fp5 files through MetadataMagic from New Millennium Communications. In particular, run the File Reference Fixer to consolidate all file references down prior to conversion. Also run the Conversion Issues report, and go through the issues one by one and fix as many of them pre-conversion as you can.

      4) Then drop-convert the files (that is, drop their icons onto the FMP 9 or 10 app icon). Regardless of whether you're planning to consolidate the files down to 1, or leave them as is, or somewhere in between, the first 4 steps of any good conversion project (IMO) are the same as above.

       

      You can deploy the converted multi-file system without consolidating, and consolidate it later. Consolidating files together can be surprisingly easy if you do the steps in the proper order. In a live solution, the biggest problem is kicking everyone out of the solution so that they're not modifying data while you're trying to consolidate.

       

      Here's the order that I'd do it in (assuming you've already converted the solution and it's been in place for awhile as separate files, and that you want to consolidate it down to one file).

       

      1) First, pick the Master File. This is the file into which the rest of the solution will be consolidated. There is no need to think you're going to do it all in one fell swoop. You can do it piecemeal one file at a time.

      2) Once you've picked the master file, decide which other file you want to consolidate into it (the "target file" ). Go to the Master File and import from the target file. In the Import Mapping dialog, for the destination, choose "New Table". FileMaker will import the records and define the fields (in a new table) for you.

      3) Once that's done, go to Manage Database, to the graph. Find every table occurrence that is currently aimed at the target file's table -- the target table (these will all have italicized names because they are aimed at the target file). For each one in turn, double-click the TO. IMPORTANT: before going further, copy the TO's name from the field at the bottom of the Specify Table dialog. Notice that the popup menu at the top of the dialog says the target file's name. Click there and choose "Current File", then highlight the newly-imported table. Notice when you do that FM has "helpfully" renamed the TO to the target table's name. Just highlight the TO name and paste the original name back in. Repeat this for every TO that used to be aimed at the target file.

      3a) You should pick one of those target TOs to be the "main" TO for that newly-imported table of data -- the one that most of that table's layouts will be based on. Usually in any given solution, there's one particular relationship that is the main or primary relationship between the Master File and the Target File -- it's usually the one related by a single primary key. I would pick that TO to be the main TO for the target table.

      4) In the Master File, define a new blank layout for each layout in the target file. Name them the same as the layouts in the target file. Pick the main TO to base the layouts on. Make sure they're blank layouts, and leave them blank for now.

      5) Now go to the target file and open Manage Scripts. Rename each script in there with a prefix that identifies the script as coming from the target file. For instance, if you're consolidating the Invoices file into the Companies file, rename each script from Invoices with a prefix that identifies it as coming from invoices ("INV" or something). Once you're done, go to the Master File and import the scripts from the target file into the master file. [The reason you prefix the scripts in a way to identify where it came from, is that it's very common to have identically-named scripts in different files of the same solution. For instance, there could easily be a script called "Import" in each file in your solution. You want to differentiate between them post-consolidation.]

      6) Now, go to the target file and copy each layout one by one, and paste its contents into the corresponding blank layout in the master file. Make sure the parts are all the same size, and that the layout objects are all in the same place. It's not very hard to do, really. I tend to rely heavily on the Object Info palette for this sort of thing, and nudge things around one pixel at a time with the arrow keys on the keyboard.

       

      The reason you want to define the layouts first, then import the scripts second, then copy the layout objects third is this: When you import the scripts, there will be references to layouts in them (like Go To Layout and Go To Related Record). In order to keep those script steps from breaking, the layouts have to exist in the file already by the time the scripts are imported. But the layouts will contain objects that reference the scripts by name (such as buttons). In order for the buttons not to break, the scripts have to exist in the file already by the time the buttons are pasted into the layouts. Make sense?

       

      Doing it this way will minimize (but not necessarily eliminate) any broken references that might happen during consolidation. Always go through everything and make sure nothing is broken. For instance, Go To Related Record script steps should reference the proper TO on the master file's graph, and the proper layout. Then, let your users start using the system. If all goes well, they shouldn't notice much difference.

       

      Well, if you do it out of order, you'll make your life more difficult than it needs to be. There are a pros and cons to consolidation.

      The list of pros includes (but this isn't an exhaustive list):

      - Unified security model (define accounts and privileges in one place)

      - All scripts in one place

      - You are leveraging FM's strengths. It's really designed for a consolidated data model.

       

      The cons include:

      - Single point of failure for your system. If the file gets corrupted, the WHOLE SOLUTION gets corrupted.

      - You can only ever back up the entire system, instead of backing up only pieces of it.

      - It complicates your life to have all tables in one file, if you ever need to update the system with an off-line copy. If you make a v2 of the system offline and it has 30 tables in it, you have to import all 30 tables of data into the offline copy when you put it in place. Ugh.

       

      FileMake.com Links ===========

      Converting FileMaker Databases from Previous Versions

      http://filemaker.com/downloads/pdf/fm8_converting.pdf

       

      Upgrading to FileMaker 8: Migration Foundations and Methodologies

      http://filemaker.com/downloads/pdf/techbrief_fm8_migrtn_found.pdf

       

      Upgrading to FileMaker 7: How to Employ the New, Advanced Security System

      http://filemaker.com/downloads/pdf/techbrief_security.pdf