4 Replies Latest reply on May 11, 2011 10:42 AM by philmodjunk

    Table to table data transfer

    ShaneC.

      Title

      Table to table data transfer

      Post

      I have an invoice table in my database that was created incorrectly initially. I didn't use a separate table for the item list. The item list and invoice is in the same table.  The items use repeating fields up to 40 repetitions and are not in a portal.  It works ok but the primary problem is if I have a multiple page invoice I have to create each page in a separate invoice.

      Anyway, now that I have learned that I need 2 tables for the invoice I am preparing to recreate the tables. My question is after I create the new tables for invoicing is will I have trouble copying or moving the item data from my old invoice table to the new "itemlist" table? I have nearly 3000 quotes & invoices in the database with anywhere from 1-29 items per invoice so I don't want to have to manually move all that data.  If I have to I'll just archive the old records and start a fresh set but I'd rather have them integrated.  I've noticed that if I try to export an invoice to Excel it does not use all the data so I'm concerned that the items will not all export into the new table properly either.

      Thanks for any help.

        • 1. Re: Table to table data transfer
          philmodjunk

          FileMaker has a nice widget for this.

          Use Import Records to import your data twice: Once into Invoices and once into Line Items. You specify the appropriate sub set of fields as needed for each import. When importing into line items, specify your repeating fields to map to your new non repeating fields and also include the field that serves as the primary key for your invoices records so that they will be correctly related to the right invoices record. WHen you do this, you'll get a little dialog that pops up asking you if you want to split the repeating fields into separate records. Answer yes, and these will all be nicely split up into multiple records for you.

          • 2. Re: Table to table data transfer
            ShaneC.

            Thank you for the great explaination. Sounds like the perfect solution.

            • 3. Re: Table to table data transfer
              ShaneC.

              Started looking in to this solution but ran into a question.  Using File -> Import Records ->...  I have a few options.  File...  Folder...  XML Data Source...  ODBC Data Source...  How do I use these options to transfer records from one table to another within my same database file?  These options all appear to be importing data from an external source.

              I'm using Filemaker Pro 9... should have mentioned that earlier in case that makes a difference.

              Thanks.

              • 4. Re: Table to table data transfer
                philmodjunk

                Use the FileMaker file type and select the same file that you currently have open.