2 Replies Latest reply on Apr 26, 2011 8:08 AM by silba2021

    Splitting imported data into different tables

    silba2021

      Title

      Splitting imported data into different tables

      Post

      I am importing data from a flat file into one with relational tables. The old flat file has products and developers in one table, the new database has these two split up into two tables with a many to one relationship (one developer creates many products but each product has one developer). I have brought in the data for the products not sure about how to get in the info about the developer for each product.

        • 1. Re: Splitting imported data into different tables
          LaRetta_1

          So you only want to import ONE of each Developer into your Developers table In your Developers table, go to field definitions and the validations tab.  Set it to 'Valid data in this field ALWAYS' , uncheck 'Allow User Override' and then in Requirements, check 'Unique.'

          Then import your flat file into Developers and specify Add New Records only.  This will only allow ONE of each unique Developer into the Developers table.  Both your Products table and your Developers table should have a unique, FM-generted auto-enter serial number for their table, called ProductID and DeveloperID respectively so create them now if they don't exist.  Then relate as:

          Products::Developer = Developers::Developer

          Then read this post on how to now switch the relationship to being based upon proper keys:

          http://fmforums.com/forum/topic/77866-converting-name-based-relationships-to-serial-based/page__pid__364823

          Once you have the tables related using your new keys, you can delete the Developer field out of Products.  To view the Developer name in Products, simply place the related Developer::DeveloperName field on your Products layout.

          • 2. Re: Splitting imported data into different tables
            silba2021

            I have been my present solution for the last six months, there was about 300 records for products related to unique developers from the developers table (related to products with a foreign key). Both tables have unique serialized primary keys.

            I have been asked to import a much larger older flat file with over 53,000 product records into this file, so I flushed the 300 records I had (redundant) but not the records for developers. Is it better to flush out the developer records too?

            Are you saying I should create a developers field in the products table and do the same with the developers table with unique value for each developer. Relate these two developer fields and then later delete the field from products. Please bear with me if I am not following this correctly. Thanks