6 Replies Latest reply on Mar 18, 2016 10:43 AM by alecgregory

    Testing for empty date field

    BillPlunkett

      I have a "calculation" field that is set to YES if both of 2 dates fields do not contain dates and NO if either field contains a date.  Both date fields are in the same table as the calculation field.  (my background is more in SQL and C-like languages).  Since there does not seem to be a nil or null test and testing for "" doesn't work.  How do I test for an empty date field?

       

      The calculation I currently have for the field "OnRanch" is:   If ( DeathDate = ""  & SaleDate = "" ; "Yes" ; "No" )

       

      The table contains records for livestock that has ever been on the ranch.  If they haven't died or been sold, then they must currently be "On the Ranch".  Of course, I haven't handled rustling but... 

       

      thanks,

      bill

        • 1. Re: Testing for empty date field
          dtcgnet

          Use IsEmpty.

           

          If ( IsEmpty ( DeathDate ) and IsEmpty ( SaleDate) ; "Yes" ; "No" )

           

          I prefer Case statements. To me, they are just easier to read.

           

          Case (

             IsEmpty ( DeathDate ) and IsEmpty ( SaleDate) ; "Yes" ;

             "No"

          )

           

          Also, the "&" is used to concatenate other things. Like FirstName & " " & LastName. Use And instead.

          • 2. Re: Testing for empty date field
            BillPlunkett

            thanks so much.  I can always depend on this forum for solutions for my problems....

             

            b

            • 3. Re: Testing for empty date field

              And, for SQL, use a slightly different method:

               

               

              The SQL could be part of a calculation or other decision logic.

               

              HTH

               

              - m

              • 4. Re: Testing for empty date field
                alecgregory

                It's actually quite interesting to dissect the original calculation. Although it's not best practice, why wouldn't it work?

                 

                If ( DeathDate = ""  & SaleDate = "" ; "Yes" ; "No" )


                As pointed out, in FileMaker & is the concatenation operator, so if DeathDate is blank and SaleDate is blank, it might seem like you would get 1 & 1 = 11, which would evaluate to true and therefore return "Yes". However, it seems that when concatenating two boolean calculations FileMaker always returns 0, even if both are true. Wrapping each boolean calculation in either GetAsNumber, GetAsText or GetAsBoolean does return "Yes" if both fields are blank:


                If ( GetAsBoolean ( DeathDate = "" ) & GetAsBoolean ( SaleDate = "" ); "Yes" ; "No" ) // Returns "Yes"

                If ( GetAsText ( DeathDate = "" ) & GetAsText ( SaleDate = "" ); "Yes" ; "No" ) // Returns "Yes"

                If ( GetAsNumber ( DeathDate = "" ) & GetAsNumber ( SaleDate = "" ); "Yes" ; "No" ) // Returns "Yes"


                So it looks like there's something going on in the backend that makes the original calculation behave somewhat unexpectedly.


                • 5. Re: Testing for empty date field
                  dtcgnet

                  Interesting observations.

                   

                  It looks to me as though the original is being interpreted something like this:

                   

                  DeathDate = "" & SaleDate     //This evaluates as "Is blank equal to nothing concatenated with nothing?". Yes, it is, so it evaluates to 1.

                  Then that is being compared to the rest of the equation, = "". In other words, is 1 equal to blank? No. So the complete equation evaluates as 0.

                   

                  Or...

                  ( DeathDate = ( "" & SaleDate ) ) = ""

                   

                  The other examples are all evaluating as 11, which is above 0, and is therefore true.

                  However, if DeathDate is empty and SaleDate is not, those other examples would evaluate as 01, which would be true.

                  If DeathDate is not empty and SaleDate is, the other examples would evaluate as 10, which would again be true.

                   

                  As long as "&" is used, there is a concatenation going on.

                  • 6. Re: Testing for empty date field
                    alecgregory

                    Ah yes, I'm pretty sure you're right. The concatenation isn't really an operator in the mathematical sense, which is what I was thinking.