Trouble with tax rates based on invoice total

[tbcomputerguy]

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 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, 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, capture the product code and replace it to make it relookup.  Seem 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 over $4.

Any thoughts would be appreciated.

Dave

Edited September 27, 2016 by tbcomputerguy

[comment]

3 minutes ago, tbcomputerguy said:

The second rate kicks in when the gross invoice is = to or under $4.00.

If that is the case, then the line item needs to know what the total of the parent invoice is before it can calculate its tax amount. Alternatively, you could calculate both tax amounts at the line item level, and select the correct field to sum at the invoice level. But then you would not be able to total the tax in a yearly report produced from the line items table.

 

10 minutes ago, tbcomputerguy said:

Or perhaps a script that only kicks in when the total is over $4.

There is no script trigger that would respond to a change of value in an unstored calculation (or a summary) field.