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

    Please help debug my (fairly simple) formula

    captainllama

      Title

      Please help debug my (fairly simple) formula

      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?

        • 1. Re: Please help debug my (fairly simple) formula
          philmodjunk

          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.

          • 2. Re: Please help debug my (fairly simple) formula
            raybaudi

             "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 )
            )

            • 3. Re: Please help debug my (fairly simple) formula
              captainllama

              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.

              • 4. Re: Please help debug my (fairly simple) formula
                mgores

                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?

                • 5. Re: Please help debug my (fairly simple) formula
                  philmodjunk

                  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 ?.