4 Replies Latest reply on Jul 8, 2009 8:38 AM by temporos

    Importing records from one table to multiple tables

    temporos

      Title

      Importing records from one table to multiple tables

      Post

      I completely redesigned the database I was using, because I made the first one before I fully understood table relationships.  My new database uses three tables: "Customers," "Jobs," and "Accounts Receivable."  The old database has all the information in one gigantic table.  So, each record features a unique job and unique A/R data, but not unique customer data (one customer may have multiple jobs).

       

      To avoid having to manually type all these in again, I need to transfer the information from the old, single-table database to the new, multi-table database, without loss of connections between jobs, customers, and A/R.  All the jobs would need to be transferred, but I'd like to avoid having duplicate records in the Customers table.  Is this kind of import possible?

        • 1. Re: Importing records from one table to multiple tables
          philmodjunk
            

          Yep,

           

          Use Import Records | File

           

          You'll do this at least once for each of your new tables--just switch to a layout based on the desired table before starting this process.

           

          There's a field mapping tool that allows you to match a field from your old table to a field from your new table. If the names are exactly the same, you can choose a "matching names" option to automatically align them.

          • 2. Re: Importing records from one table to multiple tables
            temporos
              

            Hi, Phil.  Thanks for the help.

             

            I tried what you suggested, and all my records from the old database copied over.  However, lots of identical records were created in the Customers table, one for each record in the old database that had the same customer.  I wrote a script to find and delete all the duplicate records in the Customers table, and that took care of the identical records issue, but now the records that were related to the (deleted) duplicate records are orphaned.  Do you have an idea on how to re-relate these to their customer record without having to find and change all of them manually (there are a hundreds of them)?

            • 3. Re: Importing records from one table to multiple tables
              philmodjunk
                

              You can either use a script or Replace field contents to connect your orphan records to the correct matching record in customers. It's hard to spell out the details without knowing how you've set up the relationship between your two tables and what fields are present in the second table (where you have your "orphan" records).

               

              Replace Field Contents:

              Find all the orphan records for one customer.

              Use Replace Field contents to give them all the Same ID value that they need to match to an ID field in Customers.

              Repeat for each customer.

               

              You may be able to do the above process in a script.

               

              If all else fails, you may need to delete these records and try again. It depends on whether you can perform a find to locate all the orphan records for a given customer or not.

              • 4. Re: Importing records from one table to multiple tables
                temporos
                  

                I ended up writing a series of scripts (four of them) that worked together to import the records to the appropriate tables, found the connections between the records in each table, and copied the appropriate ID numbers between records across tables.  Two of the scripts ended up similar to the "delete duplicate records" example on the FM help site, with some file-specific changes, of course.

                 

                Thanks for the help, Phil!