11 Replies Latest reply on Sep 29, 2016 7:35 AM by tbcomputerguy

    Trouble with tax rates


      Hi there, it's been a long time since i was here.  We are doing an invoice solution for our work.  Everything seems fine for the most part.  We are experiencing some issues with taxes.  Each product has two tax rates.  13% and 5%.  Sorry almost every product.  I have buttons tied to products that when clicked populates the portal with product, amount (as lookup) and the tax rate for that product as a lookup.  The tax is calculated as follows; all products have the 13%, but some as I said have the 5%.  The second rate kicks in when the gross invoice is = to or under $4.00.  Example, coffee: 1.76 net.  By itself has 1.76 price and the 5% tax as the total bill is under $4.  Now if I add say a fries and gravy ($3.72) the total of the bill is now over $4.00.  The Fries and gravy only have the 13% tax so no issue.  But because the coffee is a lookup, it sets the rate at 5% for that line item and that's it, no matter how many items I add.

      If I add say 3 coffees one at a time, the taxes are as follows by line, 5%, 5%, 13%, when the total is now over $4 so all three should be 13%. One more thing, if I enter the F/G first then the coffee, no problem.

      Now my question is, how do i get it to autorefresh the tax rates not knowing when I've entered my last item.  should I put a subtotal button and have the user click it, then have a script goto the portal to each line, capture the product code and replace it to make it relookup.  Seems kind of cumbersome.  there must be a way to have it recalc taxes on the fly.  Or perhaps a script that only kicks in when the total is under $4. this is a canadian tax system here in ontario.

      Any thoughts would be appreciated.


        • 1. Re: Trouble with tax rates
          Johan Hedman

          Then you should not use Lookup, instead use Calculated value just above in the Field settings and do a Case() to set your tax

          Image 1.png

          • 2. Re: Trouble with tax rates

            Since the level of tax depends on the total bill, not the value of individual items, then I suggest you should apply the tax at that level, not the line item level. This would be easily done if your are using a standard invoicing system with line items, as the latter will be in a line items (child) table but the tax will be calculated in the invoice (parent) table. Also, take note of Johan's advice and use an auto-enter calc instead of a Lookup, as that will readily update as line items are added (or subtracted or changed) and the total bill changes. You should probably store the tax rates in a separate reference table, as insurance against future government policy changes. Your calc will be something like:

            If ( totalBill ≥ 4 ; higherTaxRate ; lowerTaxRate )

            This will be much easier than trying to calculate tax at line item level.

            • 3. Re: Trouble with tax rates

              I would lookup 2 tax rates for the items and calc the correct rate based on the total of the bill, that way you don't have to adjust the tax rates in the lines.


              see example

              • 4. Re: Trouble with tax rates

                An auto-enter calculatio will not update automatically when a value from a related table updates-such as adding/removing/changing a line item record.

                • 5. Re: Trouble with tax rates

                  Not sure why its happening, but your file works perfectly.  I tried implementing in my system and nothing but "?" show up.  I thought because of relationship setup so I created a product and tax table in your file and works perfectly.  I'm stumped.  Maybe in the script for inserting the product in the lineitems.  Are there any rules that perhaps i'm missing.


                  Ill keep looking and get back.



                  • 6. Re: Trouble with tax rates

                    Yes, of course. Thanks for the correction. The update would need to be triggered somehow.

                    • 7. Re: Trouble with tax rates

                      And if you use a script to trigger the update, you might as well have the script set the value in the first place


                      But not even trying to compute the tax rate on an item by item basis but instead computing a tax amount against the total of all taxable items makes mores sense to me.

                      • 8. Re: Trouble with tax rates

                        Thanks phil, I do have it working via rubens suggestion.  My problem was that my total field was a sum field on the invoices layout and rubens was a summary of the line items. And yes I'm at the point where, after entering the item, until I commit the record or click off the portal, it updates.  I tried to add a commit record script step, but it plays with my portal by making the last record to add, appear at the top..


                        Heres my script to add a product. Via a demo file that I can't remember where I got it.  Some of it doesn't even happen as I have removed the panels for instance.

                        Screen Shot 2016-09-28 at 10.59.29 PM.png

                        • 9. Re: Trouble with tax rates

                          If you use a relation and you use the total of the invoice to switch the tax, the calc gets into a loop.


                          The easiest way to solve this is to add a summery field to the lines and use that to switch the tax rate.


                          Hope that helps,



                          • 10. Re: Trouble with tax rates

                            Seems like all you need is this:


                            Define a field, Taxable Total, in Invoices as Sum ( InvoiceData::Cost )


                            Define another field in Invoices as:


                            If ( TaxableTotal > 4 ; TaxableTotal * 0.13 ; TaxableTotal * 0.05 )


                            No scripting needed


                            (I've kept this simple, in a real database, I'd use number fields in Invoices that look up the Tax rates from a tax rate table so that I can change the values any time the government changes the tax rates. This example also assumes that all items are taxable. If some are and some are not, the same concept can be used, but you need either a relationship that matches only to taxable items or an executeSQL() function call to get the total of just taxable items.)

                            • 11. Re: Trouble with tax rates

                              Indeed, very simple.  Can't believe the obvious simple route was overlooked.  All items are taxable.  So yes when the bill is under $4 only 5% applies and conversely with the over $4 tax.  Seems to be working.  Thanks again everyone.  I'll have some other questions regarding this project I am sure.  So until then.



                              1 of 1 people found this helpful