Using IsEmpty function in a filemaker Pro (v9) calculation with multiple fields

Question

Seems like a simple question, and it probably is, but for some reason I just can't get it to work for me today! I want to write a simple calculation to return a value based on a heirarchy of fields.

If the first field is empty, I want it to return the second, and if the second is empty, the third. I have tried the following but it only returns the first value.

If (IsEmpty (Field1 = 1) ; Field2; If (IsEmpty (Field2 = 1); Field3; Field1))

I was able to get the first or third value to appear by using:

If (IsEmpty (Field1) & If (IsEmpty (Field2); Field3; Field1))

But of course this doesn't show the Field 2 at all.

Is there something along the lines of:

If (IsEmpty (Field1) & If (IsEmpty (Field2); Field3; Field1, Field2))

That I can use? This obviously doesn't work because there are too many parameters in the function.

Any help would be very much appreciated! :-)

Answer 1

You need to nest your calc a bit more :

Case ( 
IsEmpty ( Field1 & Field2 ) ; Field3 ;
IsEmpty ( Field1 ) ; Field2 ;
Field1
)

In your examples, you had "IsEmpty (Field1 = 1)" which will test "Field1=1", which is either True or False, but never empty. And the "&" is a concatentation operator, if you're wanting logical and then use "and" instead.

Cheers, Nick