4 Replies Latest reply on Apr 30, 2015 2:52 PM by philmodjunk

    IsEmpty Function

    rtolliver

      Title

      IsEmpty Function

      Post

      I'm looking for help in identifying the correct FM function or procedure that is the equivalent to the Access 2010 (NZ Function) Example; the NZ(FieldName.value,0) function evaluates FieldName for null and if true returns 0. 

      I have FM four fields that are part of a sum calculation and I'd like to test each field and provide a 0 for the calculation if the tested field IsNull.

      I'm not quite a newbie, but I've been away from FM for a while and any help is appreciated.

      Thanks

       

       

       

       

        • 1. Re: IsEmpty Function
          RickWhitelaw

          IsEmpty will return 1 if the field is empty and 0 if the field is not empty.

          • 2. Re: IsEmpty Function
            philmodjunk

            So to reverse the values, use:

            Not IsEmpty ( Table::Field )

            • 3. Re: IsEmpty Function
              rtolliver

              IsEmpty returns a boolean value and that value can not be used in a calculation.  What I'm looking for is a function that will first verify if a field is empty and if it is then set the field value to Zero. As it stands, to accomplish what I need my FM script looks something like; If(IsEmpty(Field_A), Set Field[Testing::Field_A; 0]) for the four fields used in my sum calculation.

              Example: Sum(If(IsEmpty(Field_A), Set Field[Testing::Field_A; 0]);If(IsEmpty(Field_B), Set Field[Testing::Field_B; 0]);If(IsEmpty(Field_C), Set Field[Testing::Field_C; 0]);If(IsEmpty(Field_D), Set Field[Testing::Field_D; 0]))

              Basically I'm looking for help finding a way to write a cleaner script if that's possible.

              Thanks

              • 4. Re: IsEmpty Function
                philmodjunk

                IsEmpty returns a boolean value and that value can not be used in a calculation.

                That is not correct. Boolean values in Filemaker evaluate as 1 for true and 0 for False.

                I frequently include Boolean expressions in arithmetic calculations in FileMaker.