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.
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.
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.
Yes, of course. Thanks for the correction. The update would need to be triggered somehow.
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.
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.
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,
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.)
1 of 1 people found this helpful
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.