I do this in my database. The invoice record has the fields for subtotaling, tax, shipping and handling, and grand total. The line items are in a chid table. I have a standards table to hold global information, i.e. sales tax rates for one, or if you work in a lot of states a sales tax table with rates by state.
The information is looked up in the invoice record and stored. Never calculate off of the global as the rate may be changed and the invoice will recalculate.
Hopefully this is helpful.
Are all the items listed in your invoice always taxable? If some items are taxable and others aren't this calculation becomes more complex.
thanks for the reply, yes they are always ALL taxed. still trying to figure this out.
and by the way TKN, the Chid table is over my head lol. i appreciate your input but I'm not to good with file maker yet, i just started this data base. however I'm excited about it since is it super intuitive.
But the child table is exactly what you need here. Take a look at the Invoices starter solution that comes with your copy of FileMaker 11, 12 or 13.
You can open Manage | Database | relationships to find the structure that he is recommending.
In FileMaker 11, you' find these tables: customers---<invoices-----<LineItems>----Products
In the later versions, you'll see something named like this: customers---<invoices-----<InvoiceData>----Products
Same relationships different names. LineItems or InvoiceData is what TKN is referring to as the "childtable" as these records are created/displayed/edited in a portal on the Invoices layout.
Hi Phil and TKN, i got it working after i understood what i was doing with the child table more.
although i did that and thats wonderful. i still don't fully understand what the LINES or INVOICE data tables should have in them as far as fields go. i was under the impression you were using that to store information that changed per invoice occurrence. however i believe this is wrong now because TKN stores his shipping, tax, and grand total fields on the invoices table.
so i not only put a grand total under lines, but i used Invoice total under invoices and now I'm a bit confused as to which fields i need to incorporate (if any) from products into my Lines table. heres a shot of my lines table. i believe i should start by putting part_price part_quantity partbarcod cost and description and all the other part related fields back to the products table right?
i was under the impression you were using that to store information that changed per invoice occurrence.
That was a correct assumption. If I create an invoice to sell two loaves of bread two cans of tomato soup and an package of hot dog buns to a single customer, I'd create 3 records in LineITems, one for the bread, one for the soup and one for the hot dog buns. The cost of one loaf of bread, one can of soup and one package of hot dog buns would be copied (looked up) from a products table of products and unit prices.
TKN stores his shipping, tax, and grand total fields on the invoices table.
Well nothing is stored in the grand total field. It's a calculation field that computes a total of data from the LineITems table.
The shipping and tax rates would be copied to fields in the invoice table as these rates may change in the future, so these values should be copied into each new invoice from another table or a global field. That way, future changes to shipping or tax rates will not cause old invoices to compute new and incorrect totals for the shipping and tax. Shipping and Tax amount fields, like a grand total field, don't store any data, they calculate totals from the line items table and then use the appropriate rate to compute a total shipping or tax amount.
wow i totally get that now. beautifully stated. thanks for your help!
Ok, so if you understood to this point, I can answer your Invoice_line_item question.
If different items are taxes differently, you could calculate the sales tax (and shipping) on the line summarizing on the Invoice_Parent_record. You could have a tax-exempt flag to turn off sales tax by item.
I save the sales tax rate in a Company_global and look it up as it is needed storing it on the invoice. If rates change, the Company_global is updated. But only new invoices will see the new rate. Historical invoices will have the rate stored as it was when the invoice was calculated. Shipping should be done the same.
I hope I did not muddy it too much.