5 Replies Latest reply on Aug 12, 2011 8:33 AM by philmodjunk

### Post

I just cannot see where I have gone wrong. I have the following formula to calculate Sales Tax:

Round( Sales Tax Rate * ( Sum( Line Items::Taxable Amount  ) - Discount i + Shipping ) - Credit VAT ; 2 )

For example the "Sales Tax Rate" is 20%,

"( Sum( Line Items::Taxable Amount  )" is £541,

"Discount i" at 5% (calculated elsewhere) is £27.05,

Shipping £12.

"Credit VAT" is zero.

541 - 27.05 +12  = 525.95.

525.95 x 20% = 105.19.

However the formula above gives 99.78.

I renamed "Discount i" from "Discount 1" thinking I had found the flaw by using a reserved character but it made no difference.  What's going on?

I don't spot any errors in how the expression is set up. Have you tried the following to see if this narrows down the error:

If you have FileMaker Advanced, open the data viewer while on the above record and check the value of each field or if you don't have advanced:

Define several calculation fields as:

Sum ( LineItems::taxable Amount )

Sum ( LineItems::taxable Amount ) - Discount i

Sum ( lineItems::taxable Amount )- Discount i + Shipping

Sales Tax Rate ( Sum ( lineItems::taxable Amount )- Discount i + Shipping )

It might be that by examining each of these fields, you'll spot a situation where the wrong value is appearing in one of the referenced fields.

"Discount i" at 5% (calculated elsewhere) is £27.05,

I suspect that that calculation returns wrongly the double ( 54.10 ).

541 - 54.10 + 12 = 498.90
498.90 x 20% = 99.78

Try;

Let([
TA =  Sum ( Line Items::Taxable Amount  ) ;
D = 5/100 * TA
];
Round ( Sales Tax Rate * ( TA + Shipping - D ) - Credit VAT ; 2 )
)

Thanks for your answers. PhilModJunk I tried your suggestion butthe first and second calculation fields both produced a big fat "?". So I don't know where to go from there.

Well spotted raybaudi, although the calculation elsewhere shows the correct amount in its own field, and the 5% is not fixed globally but varies from record to record, so the formula has to take "D" in your example from that calculation field rather than just as 5/100.

So I just omitted " - Discount i" from the formula and lo -it came up correct. For this record. Records where no discount is applied were then out of whack. Grrr.