6 Replies Latest reply on Jun 27, 2012 3:56 PM by disabled_JackRodgers

    Filter > 0 Problem in 12


      Filter > 0 Problem in 12


      Just ran into a portal filter problem with 12. I set the filter for Balance Due > 0 and it did not filter out the balance due <= 0 lines. I had to change the calc for Balance Due.

      Old Calc: Balance due = Amount - Paid (sum(payments::amountpaid)

      New Calc:  Balance due = Round ( Amount - Paid (sum(payments::amountpaid) ; 2 )

      Since now division or multiplication is involved and all of the numbers are two digit decimals, the round should not be needed, right?

      Well, this smells like that old problem from 15+ years ago where in the 16th or so decimal places random digits appeared until someone fixed this. Quite noticeable in a spreadsheet.

      4 / 2 = 2.00000000000000023543

      Of course this could be something else...

      Anyway, I suggest re-considering your filters and calculations in such instances.

      Was it Richard Pryor who stared in the movie as the programmer who uncovered this problem in his bank employers program and became quite rich?

        • 1. Re: Filter > 0 Problem in 12

          Amount - Paid (sum(payments::amountpaid) ????

          That can't be right.

          Did you mean:

          Amount - sum(payments::amountpaid) ?

          Have you checked the actual value--either in a data viewer, show custom dialog or a calculation field where you click into the field to see the unformatted value to check and see if this is in fact the case?

          Be useful info to include in a bug report if it is.

          • 2. Re: Filter > 0 Problem in 12

            Got Me!

            Amound = manual entry dollar value

            Paid = sum(payments::amountpaid)

            Old Calc: Balance due = Amount - Paid 

            New Calc:  Balance due = Round ( Amount - Paid ; 2 )


            In my mind, and apparently in my mind only the items in quote after paid where intended to illustrate what the calc field Paid was equal to. Those able to read my mind understood what I meant...

            • 3. Re: Filter > 0 Problem in 12

              Yeah but they weren't in "quote", they were in parenthesis which made them look like part of the expression. I thought I was reading your mind correctly, but wanted to be sure I was.

              Did you ever try to check to see if the value returned did indeed include an error in the digits?

              Why not post this in Report an Issue as a bug so TS folks can confirm it if it is indeed a bug like it looks to be?

              • 4. Re: Filter > 0 Problem in 12

                It's an old bug with Filemaker, I think...

                And it is something that was taught to me in the 9th grade way before computers were even part of sci-fi movies:

                Siginificant digits.

                Rounding should be automatic and of course I forgot...

                Especially with division and multiplication when using currency and now of course any calculation as simple as addition and subtraction.

                If Filemaker had a real money field we could describe as to how many decimals...

                • 5. Re: Filter > 0 Problem in 12

                  It may be an old bug, but not one that has surfaced recently. I'd like to put it in my Known Bugs List, but only if confirmed by FileMaker techs--that's one of my rules for determining what is or is not included in the list.

                  I'm painfully familiar with significant digits. I used to teach it to HS students. And am aware that computers don't do math in base 10, they do it in binary so calculations that would not produce round off errors in base 10 can do so due to the fact that it was done in base 2, but as you have stated here in this thread, simple addition and subtraction should not generate such a round off error.

                  To the best of my imperfect knowledge, this has not been the case with recent releases of FileMaker, so if it's returned in FileMaker 12, it should be reported.

                  There is a known issue with sig digs, however, when importing data from an Excel spreadsheet into a FileMaker table. And this, from what I have read here in the forum, is directly due to the difference in internal storage format for floating point numbers in the two systems(apparently they don't use the same number of sig digs). This issue is long standing and has been confirmed to still occur in FileMaker 12.

                  • 6. Re: Filter > 0 Problem in 12

                    Importing Excel has always been a pain going back 10 years or so. Microsoft hired highly paid mathemeticians to insure that Filemaker could not easily import Excel number fields...