1 2 Previous Next 18 Replies Latest reply on Jan 31, 2012 10:22 AM by kaostika

    Why is zero not zero?

    datadesigner

      I have a database in FMP 11 where a parent table is calculating balance due with the subtotal of order items billed from the line items table less the subtotal of payments from the payments table. All of my calculations are correct and I have rounded all to two decimal places in the field calculations. Where payments equal order items, balance due is zero.

      Here's the issue... when I find for greater than 0 (>0), or less than zero (<0), I get balance due fields with zero. I thought I had a rounding issue. But when I have FileMaker not show records with a value of zero in the balance due field, these records do not display a zero. So it obviously seeing these fields as zero. I have set the unstored number calculation fields to global storage and then back to unstored again but to no avail. Does anyone have a clue what is going on? I've never seen this before in any version of FileMaker. I'm running FMP 11v3 on a MacBook Pro with 10.5.8.

      Thanks for any help.

       

      ----

      Joan

      admin@datadesigngrp.com

        • 1. Re: Why is zero not zero?
          datastride

          Joan,

           

          I’m sure you’ve already checked to be absolutely certain the field on which you are performing the find is defined as “number”. I only mention this because I’ve made this error before, and wasted hours looking for a logic flaw when I had simply failed to specify the correct field type.

           

          Peace, love & brown rice,

          Morgan Jones

           

          FileMaker + Web:  Design, Develop & Deploy

          Certifications: FileMaker 9, 10 & 11

          One Part Harmony 

          Austin, Texas • USA

          512-422-0611

          • 2. Re: Why is zero not zero?
            ninja

            Wish this were an answer rather than a "you're not alone"...

             

            I face the same issue performing finds on a calc field of the same {Sum(ChildTable::qty)} type.  The find works perfectly on FMP8 but finds non-zero results in FMP11.

            It isn't the 'fire farthest up the pant-leg' so I haven't dug deep into it yet.  I've tried a few simple things, but haven't really delved in yet.

            I look forward to some of the wisdom and solutions offered by others.

            • 3. Re: Why is zero not zero?
              datadesigner

              Morgan, thanks. Yes I have checked all fields and all are calculation, number fields.

              Still stumped. Wondering if this is a bug. I've not seen it before in any prior version.

               

              ----

              Joan

              admin@datadesigngrp.com

              • 4. Re: Why is zero not zero?
                comment

                Can you reproduce the problem in a new file? If so, could you post it here?

                • 5. Re: Why is zero not zero?
                  Mike_Mitchell

                  I'm wondering if this isn't a problem where summing up rounded fields retains some fractional part. There was a previous thread in the archive with a similar problem (https://fmdev.filemaker.com/message/13850#13850). Perhaps using the original number and incorporating the rounding into the final calculation instead of using the rounded number?

                   

                  Just throwing out an idea.

                   

                  Mike

                  • 6. Re: Why is zero not zero?
                    LyndsayHowarth

                    datadesigner wrote:

                     

                    Here's the issue... when I find for greater than 0 (>0), or less than zero (<0), I get balance due fields with zero.

                     

                    Are all the fields calculation results and the fields upon which you are calculating set to Numbers? Searching a Text field (or calculation with a text result) will give you anything that has a character in it including zero.

                     

                    I assume you are searching in Balance Due field... What does it give you when you find =0 ?

                     

                    - Lyndsay

                    • 7. Re: Why is zero not zero?
                      datadesigner

                      Lyndsay, Thanks.

                      Yes, all are calculation results and all are number fields. All fields 

                      throughout the calculations are number fields as are the number fields 

                      they are built from. A find in the Balance Due field of =0 yields all 

                      records with a zero. When i select the field in Inspector to not 

                      display those records with zero value, the zeros disappear so I know 

                      FileMaker is seeing the zero value. I had thought it might be a 

                      rounding issue but when I display that field with 20 decimal places, I 

                      get all zeros.

                       

                      --

                      Joan

                      admin@datadesigngrp.com

                      dataDesign Group

                      • 8. Re: Why is zero not zero?
                        RayCologon

                        datadesigner wrote:

                        ...I had thought it might be a rounding issue but when I display that field with 20 decimal places, I get all zeros.

                         

                        Hi Joan,

                         

                        FileMaker supports a *lot* more than 20 decimal places, so I'd be more convinced it isn't a rounding issue if you had checked, say 400 decimal places. ;)

                         

                        Notwithstanding that, I'm curious what value shows in the data viewer for the fields on those records where you're seeing the problem?

                         

                        Also, I'd echo Michael's excellent suggestion that you see if you can reproduce the issue in a new file, then post it here.

                         

                        Regards,

                        Ray

                        ------------------------------------------------

                        R J Cologon, Ph.D.

                        FileMaker Certified Developer

                        Author, FileMaker Pro 10 Bible

                        NightWing Enterprises, Melbourne, Australia

                        http://www.nightwingenterprises.com

                        ------------------------------------------------

                        1 of 1 people found this helpful
                        • 9. Re: Why is zero not zero?
                          IanJempson

                          We've seen this happen before. The solution is to go through the calculated fields and make sure that the "Do not evaluate if all referenced fields are empty" checkbox is unchecked for the field in question and any of the fields referenced by the calculation.

                           

                          regards

                          Ian

                          1 of 1 people found this helpful
                          • 10. Re: Why is zero not zero?
                            Stephen Huston

                            Back to basics: are you sure you are using the >0 and <0 instead of ≤0 or ≥0 (greater OR equal, and less than OR equal) ?

                             

                            Not likely you'd miss that, I admit, but we've seen simpler gotchas drive people here for help.

                             

                            Another idea I have found useful in a few cases where I need to be sure that the calc is returning a Zero instead of Null is to define the number-result calculation as a case statement that starts with

                             

                            Case (abs(number1 - (minus) number2) <= 0.005 ; "0" ; etc

                             

                            So you are first testing that if the difference between the two numbers is less than 0.005, you get exactly 0 as the calculated result.

                             

                            It seems like a bother in what should be a simple numeric calc, but returning a literal text of Zero "0" sometimes forces the issue to resolve as intended. And Ian's reminder to make sure that the evaluation is completed even if  fields are empty is a must.

                             

                            Stephen Huston

                            • 11. Re: Why is zero not zero?
                              LyndsayHowarth

                              Yes... I thought about that initially... but then again all the referenced fields are not empty... the result is zero.

                               

                              >0 is 'lazyman's' way of asking for 'not isempty()'... or isempty()>0

                               

                              What does it give you if you search for >0.0000 or  >0.00000001 ... ?

                               

                              I think Ray's suggestion that there is a tiny fraction as a result in your result that is not displaying is the most likely.

                               

                              - Lyndsay

                              • 12. Re: Why is zero not zero?
                                MaidaSussman

                                What happens when you export the field to Excel and view it there? Do you see more decimal places that you expected?

                                • 13. Re: Why is zero not zero?
                                  andrewshirts

                                  Are these finds scripted or manually performed?  If the finds that are returning balances of zero when they shouldn't be are scripted, you may want to verify that the find criteria are using the correct relationship instance - i.e., the displayed field and the field specified in the scripted find may be the same field but using different relationship criteria.

                                  • 14. Re: Why is zero not zero?
                                    datadesigner

                                    I have used many of the suggestions here but cannot get a resolution within my solution. I have checked that all fields are number fields. I have made sure that "Do not evaluate if..." is unchecked. I have run manual finds. I have checked the values in the data viewer. I have checked lots of decimal places. Zeros are indeed zeros.

                                    I am suspecting at this point some corruption. I can get the correct results in the calculated balance field for >0 but not for <0. In that case I get no records found. And there are records with a negative balance... quite a few. I have even tried <.01, and <.000001 and still no luck. I exported the OrderItemsTtl field and PaymentsTtl field as a merge file. I imported those records into a newly created file and created the balance field calculation (OrderItemsTtl - PaymentsTtl) and wouldn't you know it, all finds work as expected. Bummer!

                                    I've got work to do.

                                     

                                    Thanks for all the help.

                                     

                                    ----

                                    Joan

                                    admin@datadesigngrp.com

                                    1 2 Previous Next