4 Replies Latest reply on Jun 8, 2012 1:02 PM by R.Schmidt

    Calculation bug

    R.Schmidt

      Summary

      Calculation bug

      Product

      FileMaker Pro

      Version

      12.0v1

      Operating system version

      MacOS 10.6.8

      Description of the issue

      I have a calculation field in which I show the VAT content of a number in a data entry field, i.e. a 1 / 6 fraction of the number from the data entry field.  So £6.00 (i.e. £5 + 20% VAT) entered in the data entry field should show £1.00 in the calculation field. Instead, the result of the calculation is £100000000000, i.e. the decimal point is missing / incorrectly positioned.

      Steps to reproduce the problem

      This appears to be a calculation bug associated with the use of a fraction (i.e. 1 / 6) to perform the calculation. If £5.99 or £6.01 is entered in the data entry field, the calculation is correctly performed. The calculation is also correct for other whole numbers EXCEPT FOR multiples of 6.  So 24 in the data entry field produces £400000000000 in the calculation whereas 24.00000001 in the data entry field produces £4.000000001666666700000000000, which shows as £4.00 to 2 decimal places.

      Workaround

      The workaround (for me, because I am calculating a 1 / 6 fraction) to is simply to enter a number that is not 6 or a multiple of 6.  So I enter, for example, 6.0000001 instead of 6

        • 1. Re: Calculation bug
          philmodjunk

          Can you post the exact expression using 1/6 that you used?

          That will help the TS folks to reproduce the issue.

          • 2. Re: Calculation bug
            R.Schmidt

            The calculation is done using the following expression in a Summary page:

            (Apr Miscellaneous * 1 / 6) & (May Miscellaneous * 1 / 6) & (Jun Miscellaneous * 1 / 6) & (Jul Miscellaneous * 1 / 6) & (Aug Miscellaneous * 1 / 6) & (Sep Miscellaneous * 1 / 6) & (Oct Miscellaneous * 1 / 6) & (Nov Miscellaneous * 1 / 6) & (Dec Miscellaneous * 1 / 6) & (Jan Miscellaneous * 1 / 6) & (Feb Miscellaneous * 1 / 6) & (Mar Miscellaneous * 1 / 6)

            This picks up values from a Miscellaneous data entry field in each of 12 monthly accounts pages, multiplies each value by 1 then divides each result by 6, and then displays the individual values in the Summary page.

            VAT calculations are typically performed using fractions rather than decimals. Elsewhere in the spreadsheet, values are multiplied by 5 / 6 . That calculation is performed correctly.

            So, 6 or 6.00 entered in the data entry field should generate 1 in the calculation field. Instead, the result is 100000000000 on the first occasion and 010000000000 on a second occasion and 001000000000 on a third occasion.  I would expect to see 1.00000000000 on each occasion.

            6.001 entered in the data entry field generates the result 1.000166666666666700000000000

            • 3. Re: Calculation bug
              philmodjunk

              The expression, as written, will "glue" all the results into a single term as you have an ampersand (&) with no " " or "," or a Char (9) [tab character] to separate each value.

              If you intended to add the values, you should use + instead of &.

              What do you get if you just use:

              Apr Miscellaneous * 1 / 6

              This can be simplified, BTW, to: Apr Miscellaneous / 6

              To get the same result.

              • 4. Re: Calculation bug
                R.Schmidt

                My accounts database was originally created in an earlier version of FileMaker.  It has been recently imported into FileMaker Pro 12 via FileMaker Pro 11 from FileMaker Pro 6.  The problem I describe does not occur in FileMaker Pro 6.  So, something has changed in the move from v6 to v12.

                If * 1 / 6 is changed to / 6, the problem persists.

                If * 1 / 6 is changed to * 2 / 12, the problem persists

                If * 1 / 6 is changed to * 1 / 2, the problem persists

                If * 1 / 6 is changed to * 0.166667, the problem resolves

                This suggests that there is a bug associated with the use of fractions.  But I need to use fractions rather than decimals for VAT calculations.  If the VAT rate changes to 17.5%, I'll need to edit these fractions from 1/6 and 5/6 to 7/47 and 40/47.  These are horrendous numbers if expressed using decimals.

                I observed the problem after entering the number 6 in the data entry field.  The problem resolves if I enter 6.0001 or 6.000001 (etc).  Interestingly, the problem did not occur if I entered 5 or 7.  Only with 6.

                So, I tried other whole numbers in the data entry field.  The problem occurs with 2, 4, 6, 8, 10, 12.  The problem does not occur with 1, 3, 5, 7, 9, 11

                Any number containing a decimal point other than a whole number with trailing zeros, e.g. 2.00, 4.00, 6.00, etc is handled correctly, presumably because FileMaker Pro strips 6.00 down to 6

                You guys with the spanners should surely now be able to work out what is happening here?