8 Replies Latest reply on Jul 27, 2013 1:11 PM by philmodjunk

    Testing for Zero



      Testing for Zero


           I have an calculated account balance field.  This is the calculation:  Round ( TotalFees - Payments::DepositTotal ; 2 )

           When the account balance is zero, the field displays:  $0.00

           I need to test it for zero, but I get no records found when I use this criteria:  =0

           I even tried "=0.00" and it still didn't work.

           If I use "<.01" it does work.

           Unfortunately, I need to be able to exclude negative balances (refunds) and positive balances (unpaid) so I really need to be able to actually test for zero.

           Anyone know what the problem might be?






        • 1. Re: Testing for Zero

               I don't understand your question.  Do you want to search for all records with 0 or are you creating conditional formatting.

               To find the records

               Enter Find Mode []

               Set Field[YourCalculationField;0]

               Perform Find []




               Condititonal Formatting

               If Valaue is Equal to 0


          • 2. Re: Testing for Zero

                 That's the problem.  When I do a Find for this field when it equals zero, it doesn't find any records.  Even though when I look at the field, I see $0.00.



            • 3. Re: Testing for Zero

                   Could be because you are rounding it, you see zero but it's not. Try using     >=0   and   < .01

              • 4. Re: Testing for Zero

                     I'd click into the field and make sure that the field in which I am entering the criteria is really your calculation that is rounding to two decimal places and not an unrounded field with data formatting set to display a fixed number of decimal places. If the value changes to show an unrounded result when you enter the field, it is not using the round calculation you have reported.

                     Also, check to make sure that your calculation specifies number--not text as the result type.

                • 5. Re: Testing for Zero

                       When I click into the field, it changes from the layout formating ($0.00) to just 0.

                       The calculation does specify a number.

                  • 6. Re: Testing for Zero

                         I can match any other value in this field, positive or negative, so I know I'm looking at the right place.

                         But when I do a Find on 



                          >=0   and   < .01


                         I find no records.

                         When I look at the field, I see "$0.00".  When I click in the field, I see "0"

                    • 7. Re: Testing for Zero

                           OK.  That was weird.  I tried it one more time with the original test of "0"  and now it works.  Go figure.

                      • 8. Re: Testing for Zero

                             Just to be on the safe side, I suggest using advanced recovery options to make sure that your indexes are not messed up. Flaky find or sort results can sometimes be due to a corrupted index.

                             If you have FileMaker 11 or newer, you can use Advanced Recovery options to rebuild your file's indexes:

                        1.           With the file closed, select Recover from the File Menu.
                        3.           Select "Use advanced Options"
                        5.           Select only: "Copy File Blocks as-is" and "Rebuild Field Indexes Now".
                        7.           The recovered copy of the file will be identical to the original copy except that it has completely rebuilt indexes.