AnsweredAssumed Answered

Create records from a single field

Question asked by williamrollo on Jun 8, 2016
Latest reply on Jun 10, 2016 by williamrollo

I am trying to sort out some final imports from an old access database that is more or less an invoicing solution. My new FM database works in a more conventional system ( using separate Line items tables between products and invoice Table). However, I see in the exported XLS table from the old Access DB that each invoice record is on a single line, with no separate join table for the invoice lines. Each record contains a series of fields that correspond to the relevant lines of the invoice. I have attached a mini version to demonstrate. Is there  a way to script an import from this table to create new records from each of the lines so that record 1 will show the invoice number, line 1 of the product and line 1 price - three fields in total for each record...


My excel file obviously is v different and has many more fields in each record, but if this can be sorted then I should be able to expand to fill out the other fields in the record. (about 800 lines in the xls spread sheet - each invoice has 8 Lines maximum so 6400 records will be created i think)


Many thanks for help and advice.