If ( IsEmpty ( DeathDate ) and IsEmpty ( SaleDate) ; "Yes" ; "No" )
I prefer Case statements. To me, they are just easier to read.
IsEmpty ( DeathDate ) and IsEmpty ( SaleDate) ; "Yes" ;
Also, the "&" is used to concatenate other things. Like FirstName & " " & LastName. Use And instead.
thanks so much. I can always depend on this forum for solutions for my problems....
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.
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.
( 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.
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.