I have an existing base that has a line item table, vendor table and product table. I need a total for each invoice along with vendor information for importation to QuickBooks, but am having problems figuring out a relatively simple way to do this.
In the line item table I enter an invoice number from written invoice pads that we are using for our farmer vendors who don’t have any sort of record keeping system. If I group by invoice # I can see an invoice total, but without the vendor lookup data. I tried creating an Invoice table with a lookup to the line items table and a rollup summing the subtotals, but it only brings in one line item rather than all with that invoice #, and if I add a lookup to invoices from line items, it brings in multiple copies of the vendor data, which I guess is one problem of having vendor data as lookups in the line item table, maybe that’s a many-to-many problem.
I was looking at an example of an invoice base posted by @AlliAlosa, but in that example it looks like after you enter line items, you would then need to go to the invoice table to enter customer information, which is something I’d like to avoid. Thoughts?