1 Reply Latest reply on Jul 22, 2013 1:19 PM by FentonJones

    How to link multiple invoices



      How to link multiple invoices


           My solution is roughly equivelent to the invoices starter solution with Customers, Products, Invoices, and Invoice Line Item tables.

           However, in my business, I need to be able to link multiple invoices together when customers are sharing costs.  

           For example, Customer A needs 12 widgets and 4 woodgets.  Customer B just needs 15 widgets.  Customer A+Customer B are not actully using the items at the same time, so they would like to share the 12 widgets they have in common (and share their cost).

           I'm thinking I might do this with an additional table occurance of my "current invoices" table, but not sure if that's the best solution.  The user would need to be able to choose 5 or more different invoices to "link" together, and manipulate all the quantities of the items while seeing how the sum of each of the lines would be effected.  One master invoice listing the totals would then have to be sent to the shop for completing the order.


           thoughts / advice / examples appreciated.

        • 1. Re: How to link multiple invoices


               OK, I will attempt this, for two reasons. 1. I did something like this, years ago, for something to do with horses; who can have multiple owners; though my database was simpler, as there were few products involved. You're going to need at least one more table. 2. No one else has answered :-/
               Here's the tables I see (in pretty much date entry order, as each needs data, ID, from the own above it):
               Table, ID fields, what it is.
               InvoiceID_your (local)
               Basic; but NO CustomerID!
               InvoiceID_your, ProductID, InvoiceLineID_your (local)
               Basic items (also no customerID)
               InvoiceID_your, InvoiceID_customer (local), CustomerID
               One record for each customer on the Invoice above.
               InvoiceID_customer, InvoiceLinesID_customer (local), ProductID, CustomerID (optional)
               The items they want a percent of (from InvoiceLines_your).
               One record for each item-customer combo.
               The one "odd" table is Invoice_customer. There is not much too it. But it makes things easier.
               A Value List, viewed back from InvoiceLines_customer via relationship, can drop-down the customers involved.
               It is also useful to view from customer.
               I am not sure exactly what layout the data entry is done. Likely you'd like to do it all from a form view of Invoice_your and/or InvoiceLines_your; using portals (maybe several :-| ); Value Lists based on relationship will help.
               I may not be able to help you much more, as I am no longer a developer; it's more a hobby to me now. I retired two years ago, after a stroke damaged my abilities considerably. I am better now, but still cannot read sentences very well, and have a lot of trouble both remembering words or memory, and I have trouble reading long sentences (though I can apparently write them :-/ ). But building relationships is remembered forever :-/ (joke, kind of)