3 Replies Latest reply on Feb 4, 2010 6:52 PM by KenS

    FM 8.5Pro importing from one flat table in one DB to multiple tables in another, where to start?

    cpudenusa

      Title

      FM 8.5Pro importing from one flat table in one DB to multiple tables in another, where to start?

      Post

      Hi,

       

      Using FM 8.5 Pro on XP and my experience with FM in general is pretty limited.

       

      This is a highly simplified version of the schema. 

       

      table1:

      Video_Titles - text

      Video_Titles_pk - Auto-Incrementing, non-modifiable serial.  Increments by one forever and ever

      descriptions_fk -  related to descriptions_pk in table2

       

      table 2:

      descriptions_pk  - Auto-Incrementing, non-modifiable serial.  Increments by one forever and ever

      eng_desc

      fra_desc

       

      I am importing from another DB that has all of this information in one flat table.  

       

      I just want to know how I would begin even attacking this with a script or if possible via the GUI and if my schema could use modification to ease this process that would be helpful to know.

       

      Thank you. 

       

        • 1. Re: FM 8.5Pro importing from one flat table in one DB to multiple tables in another, where to start?
          KenS
            

          Actually, FM is pretty good at doing what you want with a minimum amount of work. First set up the fields for the two tables the way you are describing them. Then use the Filemaker import tool (File->Import) to import the data into your new tables. If the old database is a filemaker database you can do it directly. If some other format you can export it to a format filemaker recognizes and import it.

          • 2. Re: FM 8.5Pro importing from one flat table in one DB to multiple tables in another, where to start?
            cpudenusa
              

            Ok Between table1 and table2 I setup a relationship before importing:

             

             descriptions_pk = descriptions_fk

             

            When I imported all my titles I got 3900 titles imported. 

             

            Title -> Title

            So now I looked at my title layout and I have 3900 titles and each one of them the has a blank value for the descriptions_fk, makes sense I think.  Then I go to my descriptions layout and I import my eng_descriptions and all those values increment to 3900.  I went to my title layout and added the field for eng_desc from my related table, but none show up, because for some reason the table isn't relating when I import.

             

            I must be missing something on matching or updating when I import I'm assuming.  My serial # increments in import and when importing I ask it to update the serial # when importing.

             

             

             

             

            • 3. Re: FM 8.5Pro importing from one flat table in one DB to multiple tables in another, where to start?
              KenS
                

              I'm guessing you've got the usual problem in moving a flat/unrelated db into a multi-table related field. That being that the flat db had all the data in one "table" and didnt need anything to associate the two parts of the table. When you import the data into the two new tables you have to provide the key that relates the two new tables. The update serial numbers option doesnt do it because it creates a serial number for the table/record it is creating and nothing to relate it to the other table and it's associated/related record.

               

              You've got the right idea in setting up the key fields. Now you just need something to use during the import that provides the "key". If you can modify the old database, add a single key field to it, and populate it with unique numbers. Now when you re-import the data one table will have half the data and the key field as a foreign key. the other table will have have the data and the key field as a primary key. That's how the relationship get's established. 

               

              If you can't modify the old database to add a key field, you can import the whole table into a single filemaker table, add the key field there using the "update serial numbers option" when you import. Then reimport that table into your two new tables as described above.

               

              Ken