
1. Re: Calculation bug
philmodjunk Jun 8, 2012 8:46 AM (in response to R.Schmidt)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 Jun 8, 2012 10:51 AM (in response to 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 Jun 8, 2012 12:01 PM (in response to R.Schmidt)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 Jun 8, 2012 1:02 PM (in response to 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?