7 Replies Latest reply on Feb 18, 2013 12:02 PM by philmodjunk

    importing invoice details into existing database

      Title

      importing invoice details into existing database

      Post

           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!

        • 1. Re: importing invoice details into existing database
          philmodjunk

               At first, this looked like a simple case of importing the data twice--either twice from the same excel file or twice from a temporary table in your database where you import into it, assign some values to help link invoices to invoice items and then import twice from this table.

               But then you said: "Each order can have 2 invoices to it though"

               That results in questions:

               How is the data structured in that excel spreadsheet? Does each order consist of multiple rows of data or is all the data arranged in a single row with one column for each invoice item?

               If there are multiple rows of data for one order, how do you tell which rows are part of a given order?

               And does "Each order can have 2 invoices to it though" describe the data in your spreadsheet or the structure in your new database?

          • 2. Re: importing invoice details into existing database

                 Hi Phil

                 Thank you. a single record (invoice record) will contain all the lines of the invoice. so the fields in asingle invoice record would include product 1, product 2, 3 subtotal, tax and of course invoice number, as well as many other fields. A single order will have at least two invoices in it as a deposit is raised the moment an order is created. So here is the sequence of ecents, described as best I can!

                 Customer registered on databse. Given a customer number.

                 Customer places an order. Customer now has an order number(customers table). Order record created (invoice table) which will be linked via this order number. Customer also given a Job number that is created in the invoice table

                 Deposit invoice raised using same table as order (invoice table). So the record in invoices has order number, job number, invoice number as well as the product line items from the invoice, in this case only one as it is for a deposit.

                 When order completed, customer is invoiced. A new record is placed in Order table that contains same Order and job numbers and most other fields but the product lines now have new data and of course a new invoice number. So it is the job number that links the invoices and the order number that links the customer to the orders.. .i think!

                 My new database follows the same rules but just slghtly differently - deposits still are raised at the point of order except of course I have an invoice details table and invoice items table...

            • 3. Re: importing invoice details into existing database
              philmodjunk

                   Yes but you said you were importing from excel.

                   Excel has rows, not records. So which do you have in this spreadsheet file:

                   One row for each invoice with the products listed in columns?

                   Multiple rows for each invoice, one product on each row?

              • 4. Re: importing invoice details into existing database

                     Sorry - terminology confusion! When I said records, i meant rows...

                     Yes, one row would contain each of the products that are listed in columns.

                     one row per invoice, but multiple rows per order if you see what I mean.

                     Actually see here as it may help a little more than my explanation!

                     https://www.dropbox.com/s/y9ji11wm7jw2kt7/Example.xls

                      

                     Ihave pasted in two rows to a blank sheet for you to see...

                     One invoice is a deposit (I think) and the orther the main invoice...

                     Thank you

                • 5. Re: importing invoice details into existing database
                  philmodjunk

                       Looks like you will need to import the data into a temporary table and then use a script that loops through the fields (columns) in each record in order to move the data into the Invoice Items table.

                       If you arrange the fields in this temp field on a layout just right, go to next field can be used to put the data into the needed records in your Invoice Items table.

                  • 6. Re: importing invoice details into existing database

                         Thank you...

                         How would the next field function work as unfamiliar with it - I have created a couple of temp tables as you suggest as this seems to be the best way of doing it and then auto filling with calculations...

                          

                    • 7. Re: importing invoice details into existing database
                      philmodjunk

                           Whoever you decide to set it up. This won't be a simple script.

                           Go to next field puts the cursor into the next field in your tab order as specified by your current layout. This allows you to use Get ( ActiveFieldContents) without spelling out the exact field from which you are getting the data. YOur example showed multiple columns of a lot of different data and it looks like you may have several different sets of fields that contain data destined for invoice items.

                           If that's the case, you'd arrange your layout so that go to next field will move the cursor from field to field in the group of fields for the first invoice item record, then the second etc.

                           A looping script can then loop through these fields to extract the data from these fields and create new records in the invoice items table via a relationship (so as to avoid changing layouts to create the new records).