Checking for Empty (Blank) Field Values for a Calculation Updated: May 30, 2012
FileMaker Pro 5.5, FileMaker Pro 5, FileMaker Pro 4.x, FileMaker Pro 3.x
Effective with FileMaker Pro 3.0, we include a new IsEmpty function that enables you to test whether a field is empty (blank). A sample calculation would look like this:
TestCalc(Calculation,text)=If (IsEmpty(TestField),Result if True,Result if False)
In FileMaker Pro 2.x and earlier, many methods for verifying empty (blank) field values will yield the correct result in some situations, however, there is only one reliable method.
Calculations do not calculate when the field that they are dependent on is blank. So if you define:
TypicalTest(Calculation) = If(length (TestMe)=0,Result if True,Result if False)
TypicalTest(Calculation) = If(TestMe = "", Result if True,Result if False)
FileMaker will not calculate this unless there is something in the TestMe field. Not very useful if you are trying to see if it is blank!
In order to trigger the calculation, you need to have a field that will always have a value in it. Sometimes these are referred to as 'dummy' or 'constant' fields. A good way to do this is to create a calculation that has a value in it. It can have any value - a date, some text, a number, etc. In this example we will use text:
Check4Empty (Calculation, Text result) = "XYZ"
So here is the test that will be reliable:
ReliableTest(Calculation) = If (TestMe & Check4Empty = Check4Empty, Result if True, Result if False)
This test says, 'if you concatenate (combine) the contents of the TestMe field and the Check4Empty field, is that equal to the Check4Empty field?' If they are equal, then there is obviously nothing in the TestMe field.