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.
Thank you for the great explaination. Sounds like the perfect solution.
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.
Use the FileMaker file type and select the same file that you currently have open.