12 Replies Latest reply on Dec 12, 2014 3:32 PM by Stephen Huston

    Invoice Solution & Sales Tax


      Hi Forum. I am currently trying to add an invoice piece to my DB for my small business. So far so good but I am having a problem with sales tax.

      Basically I have the following situations:

      1. Some products are taxable (2 different sales tax rates) & some are exempt.

      2. Some customers pay tax on all goods/services that are taxable, some customers pay tax on some goods/services, and some customers are exempt on all goods/services (gov't agencies, school districts, churches).

      3. There can be an invoice where some of the goods/services are taxable, and some are not, and there can be an invoice where the goods are taxable, but the customer is exempt.


      I'm using (what I think) the standard invoice layout/format, with a lines portal looking up the products. It does work the way I have it set up, but when I create a new invoice, the tax rates wont apply until I commit the records. Committing at the start would mean that I would be unable to Revert a record, unless I put a button on the layout that just deletes the record. But then I guess I would have to trap for the last invoice number and reset the invoice number field (which is NOT the primary key).


      Sales tax is an attribute of the goods/services (checkbox-Taxable)

      Sales tax rates are a separate table with 3 records, PA Sales Tax, Phila Sales Tax, Exempt


      I am wondering is this a standard preferable method or is there a better approach?


      Any Help or guidance (or criticism) is always appreciated.



        • 1. Re: Invoice Solution & Sales Tax
          Stephen Huston

          Are you indentifying each line-item (child record) by a taxable status, and applying the tax to the line-items, then summarizing the line-item tax in the invoice record, or are you trying to apply tax at the invoice level and doing something to track line-item amounts if they are taxable?

          1 of 1 people found this helpful
          • 2. Re: Invoice Solution & Sales Tax

            Have you tried using a script trigger that sets the tax rates on creation of the invoice record or after selecting the item linked to each individual line on the invoice?  This should allow setting/viewing the tax rates prior to commiting records. 

            • 3. Re: Invoice Solution & Sales Tax

              Sales tax is and will always be a moving target. I suggest that before you start any DB programing that you get a set of rules that can be translated into a Case/If statement calculation. As Mr. Huston has pointed out, each line item will have its own calculated tax rate based on at least 3 conditions. The ultimate sales tax for a invoice will be the sum of those lineitems.


              The conditions are driven by the product, the type of customer and the location to which the order is shipped. You will likely find that there are tax "overlay" zones such as a Downtown Improvement Tax. You will find that the tax rates change monthly or at least annually so your system must not allow old invoices to change when new tax rates come along.

              In pesudo language:


              (customer = tax exempt; 0;

              productID = tax exempt; 0;

              ShipZipCode; TaxRate:")

              I think you get the idea. There is a service that publishes tax rates for each state - www.taxrates.com If you are a small biz and want to download manually it is free. Else you can subscribe for a fee to get monthly updates.

              Hope that helps

              • 4. Re: Invoice Solution & Sales Tax

                Your line-items should lookup the tax rate.

                Your invoice should lookup your clients tax status.... which based on... the line items can then calculate how much tax applies...or not.


                Sent from my iPad

                Lyndsay Howarth

                11th Hour Group Pty Ltd

                1 of 1 people found this helpful
                • 5. Re: Invoice Solution & Sales Tax

                  Thanks for all the responses.  I have a little of each of what everyone recommended.  I'll try to answer as many as possible, with some screen shots.

                  @ Mr. Houston.  A little of both. Each line item record is identified as taxable/non taxable.  The ones that are taxable will fall into 2 catagories at the invoice level, PA Sales Tax and Phila Sales Tax.  I have a calculation field shown in the line items portal (just to check) that calculates the tax amount based on the checkbox selected and the Tax Rate selected.  And (or but?) on the invoice layout I have a field Sales Tax with 3 records-PA Sales Tax, Phila Sales Tax or Exempt, with the appropriate rates  This is a lookup of the Tax Table related by name.

                  So when I select, as an example:

                  1. Sales Tax Rate = PA Sales Tax (.06), Line Item Taxable: 1 (Checked)

                  Line Item        Line Item tax calc

                  $200.00                $12.00


                  2. Sales Tax Rate = PA Sales Tax (.06), Line Item Taxable: 0 (Not checked)

                  Line Item        Line Item tax calc

                  $200.00                $0.00


                  3. Sales Tax Rate = Exempt, Line Item Taxable: (Checked or Not checked)

                  Line Item        Line Item tax calc

                  $200.00                $0.00


                  On the invoice I have field, Sub Total, which is a sum of the Price Extended (no tax), a field Tax, which is a sum of the  line items calculated tax, and a Grand Total, the sum of those last 2 fields.

                  I felt the reason for this set up was in any invoice, there can be one line item that is taxable and one that is not, but each invoice will only have one tax rate.  Clear as mud?


                  @Mr Sexton (lots of Stephen's tonite)  I did try setting the tax rate on creation (I picked PA Sales as that's the most often used), it is calculating the rates properly on the invoice, not the portal, until I commit the records.  I figured I would add a trigger so when I pick the first item I would commit/refresh window-then it works.  Once the record is first committed, any changes, add line items, etc, everything works fine.


                  @usbc Thanks for the pointers.  The tax rules in my case or pretty straightforward.  One main tax rate most of the time and hasnt changed in about 20 years, and if I'm in the city (rarely) a different one. Your Psudo-code for calculating is pretty much what I have (without the reference to Zips).


                  I assume my problem (if the structure is right) is that the unstored calcs I can't avoid are preventing the initial creation from working properlyINV_form.pngINV_layout.pngINV_graph.png

                  • 6. Re: Invoice Solution & Sales Tax

                    Thanks for your input.  I didnt see your response before I started my last post. That is interesting and I thought to go that route.  That's how I do it in QuickBooks.  The problem trying your method (for me, doesnt mean I'm doing it correctly) is that in QB have to change the customers tax status many times.

                    Let me try some real-world examples.


                    I sell heating oil.  Heating oil is exempt from sales tax to residential customers, and businesses with tax-exempt status-easy.

                    But if I do service on any customers heating equipment (unless they are tax exempt) it's taxable.

                    So for line items, heating oil can be taxable or non taxable, and servicing a heater can be taxable or non taxable.

                    If I have an invoice where a residential customer received heating oil, and had an additve put in their tank, the heating oil would be non taxable, and the additive would be taxable.


                    I do not have a tax status field in the customers record, so I should point the field at the bottom of my invoice layout to lookup the customers status, then use that in the calculation for total taxes?

                    Either way, I will have situations where I have to uncheck a box or change a status.

                    Thanks for your suggestions.  I'll keep plugging away.

                    • 7. Re: Invoice Solution & Sales Tax



                      Is this a correct assumption:


                      if customer exempt than product: oil tax exempt

                      if customer NOT exempt than product: oil taxable

                      if customer exempt or not than product: parts taxable

                      • 8. Re: Invoice Solution & Sales Tax

                        Thanks for your response.  Quick follow up:


                        if customer exempt than product: oil tax exempt

                        Yes.  If Customer is 'exempt' then ALL products (sales/service exempt)


                        2.  if customer NOT exempt than product: oil taxable

                        Sometimes.  If customer taxable, then all sales and service taxable, but heating oil to a residential customer exempt from sales tax.  If customer taxable, and not a residential customer, then all sales/service taxable.


                        3.  if customer exempt or not than product: parts taxable

                        Not true.  Following the logic of 1 & 2.

                        • 9. Re: Invoice Solution & Sales Tax

                          Hi Steve


                          Basically you need two flags. One on each line item to mark a record as taxable or not. Another in the Invoices table to make the whole invoice as taxable or not.


                          In the line items table you have to calculate the individual tax, in case the item is marked as taxable. Then in the invoices table you can have a calculation like this:


                          Tax =


                          Case ( not Invoices::Flag_Tax , 0 ,

                                     Average ( Invoices_LineItems::Flag_Taxable ) = 1 , Sum ( Invoices_LineItems::TotalAmount ) * Invoices::TaxPercentage / 100 ,

                                     Average ( Invoices_LineItems::Flag_Taxable ) < 1 , Sum ( Invoices_LineItems::Tax ) )




                          1. If Invoices::Flag_Tax = 0, this means that the whole invoice is not taxable and Tax = 0, regardless of if line items are marked as taxable or not.
                          2. If Invoices::Flag_Tax = 1 and all line items are marked as taxable, then the average = 1 and it will calculate the tax based on the sum of all line items.
                          3. If Invoices::Flag_Tax = 1 but not all line items are marked as taxable, the the average will be < 1 and it will calculate the tax based on the sum of the individual line items tax.


                          This will make the tax calculation unstored. If you want to stay away from unstored calculations you can apply these rules using script triggers.


                          Hope this helps.



                          • 10. Re: Invoice Solution & Sales Tax

                            Thanks for responding.  This is pretty close to how I have the structure set up.  I'm just cant figure out why I have to commit the invoice record before the calculations in the Line Items portal will respond.  Don't know if that's normal, or if most lilkely I am doing something incorrectly.

                            • 11. Re: Invoice Solution & Sales Tax

                              Figured out my calc dilemma.....standard simple issue "Do not evaluate if all referenced fields are empty' was checked.  No one is here to whack me in the head....grrrrr.

                              • 12. Re: Invoice Solution & Sales Tax
                                Stephen Huston

                                Records in a portal are not committed until the parent record wich shows the portal is committed. This will mean that final calculations which are unstored won't be completely up-to-date until a record commit is performed.


                                You can script an update or "save" button on the portal row which starts by capturing the portal object name and portal row number, then commits the records, then Goes to the portal object and the row number, so the user appears to be back where they started in that portal row, but the record got committed/updated in the process.