AnsweredAssumed Answered

importing invoice details into existing database

Question asked by willrollo on Feb 18, 2013
Latest reply on Feb 18, 2013 by philmodjunk


importing invoice details into existing database


     I am trying to import some invoices from excel spreadsheets into my new FM database. The problem is this:


     New database has

     Customers table. (details of customer, invoice history etc)

     Invoice details table (details of invoice)

     Invoice items table (link table between Invoice details and products, contains lines of invoice)

     Products table (list of products and prices)


     Old database tables exported as excel files has:

     Customers file - Contains customer details , history etc.

     Incoice headings table - this contains the invoice numbers, line items of invoice, shipping and billing addresses, totals etc etc. So it is more like a combination of my Invoice details and invoce items table.  There is a field in each the two table that link them. There is also (in the customers table) an ID field that I will use when I import the customer details into my customers table.  The old database would only create the link field data when an order is created, so this is why I am using the ID field as my "Customer number" as they have not all placed an order. 
     What I really would like help or advice with, is how to import the invoice details from the old database, into the new one. All I really want to be able to have, for example, is a pop up window from a customer's layout that shows a portal to the invoice history of the customer. I have this working fine for new customers in the new database, but unsure how to import and link which fields from the old one. I have imported the two old tables as they are into new layouts. I have linked the imported customers::customer ID field to my existing Customers::customer_pk. I have also linked the fields imported_invoices::Order_id to imported_customers::ID field. But now am stuck on how to view them seeing as each customer may have several orders (identifiable by order number. Each order can have 2 invoices to it though)

     Help appreciated!