9 Replies Latest reply on Jan 13, 2014 5:25 PM by TKnTexas

    display sales tax on printed invoice

    tedesco12

      Title

      display sales tax on printed invoice

      Post

           hi, i would like to add up the tax on the items I'm displaying on an invoice and list it for the customer. for example, i used "total" and made it a summary field totaling up the price_extended and got my sub total for all my items, now i want to put a field under sub total and call it tax, and display the 7 percent sales tax they are paying on the all the items, then I'm doing a grand total after that….do i use summary fields to do this as well? 

           thanks in advance. 

           TT

        • 1. Re: display sales tax on printed invoice
          TKnTexas

               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.

          • 2. Re: display sales tax on printed invoice
            philmodjunk

                 Are all the items listed in your invoice always taxable? If some items are taxable and others aren't this calculation becomes more complex.

            • 3. Re: display sales tax on printed invoice
              tedesco12

                   hi,

                   thanks for the reply, yes they are always ALL taxed. still trying to figure this out.

              • 4. Re: display sales tax on printed invoice
                tedesco12

                     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. 

                     thanks again 

                • 5. Re: display sales tax on printed invoice
                  philmodjunk

                       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.

                  • 6. Re: display sales tax on printed invoice
                    tedesco12

                         Hi Phil and TKN, i got it working after i understood what i was doing with the child table more. 

                         thanks !

                         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? 

                          

                         thanks again 

                    • 7. Re: display sales tax on printed invoice
                      philmodjunk
                           

                                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.

                      • 8. Re: display sales tax on printed invoice
                        tedesco12

                             wow i totally get that now. beautifully stated. thanks for your help! 

                        • 9. Re: display sales tax on printed invoice
                          TKnTexas

                               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.