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
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.
The ? is usually because the data is too long for the field as displayed on the layout. Clicking on that field will usually show what the actual value is.
Are the records where no discount is applied off by the same percentage?
The other reason for a ? like this is an invalid calculation result such as dividing by zero or taking the square root of a negative number...
If the field is too narrow, you can click or tab into and the field will expand out to display the actual value. If it's an invalid result, it will still show the ?.