Invoice base design

[David_Van_Eeckh]

Hello folks,

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?

[Justin_Barrett]

I built my own invoice system in Airtable last year. In my case, I build line items on their own in the [Line Items] table, then move to the [Invoices] table, and add links to those line items from the proper invoice record. My line items don’t have any invoice-specific data, and also don’t link to clients. That all happens in the invoice record. When linking to line items from an invoice, I’m only shown line items that haven’t already been linked, which keeps the list of possible choices very low so that I don’t have to hunt through a massive list just to find the line items I need.