I am trying to get a simple calc to work but I am having trouble. result is a number.

Field 1 is a text field, field 2 is a date field, and field3 is a number

 

Case

 

(

 

field1 = "a" or "b" or "c" and IsEmpty ( field2 ) ;

 

field3 ;

 

"0"

 

)

 

It only works if "a" is the value. And it is not correctly evaluating whether or not field2 is empty.

 

What is the correct context for this?

 

Thanks,

 

Dave

The first thing I notice: Field1="a" or Field1="b" or Field1="c" is the proper syntax for this part of your calculation.

The first thing I notice: Field1="a" or Field1="b" or Field1="c" is the proper syntax for this part of your calculation.

Thank you, that fixed that part of it. I still have something wrong when it comes to evaluating if field2 is empty. If I have a date in there or not, the result is the same.

 

Dave

Automatic message

This topic has been moved from "The Left Brain" to "Calculation Engine (Define Fields)".

Thanks Lee.

 

I read that IsEmpty returns a 1 if the field is empty.

 

I changed the calc to read:

 

 

Case

 

(

 

field1 = "a" or field1 = "b" or field1 = "c" and IsEmpty ( field2 ) = 1 ;

 

field3 ;

 

"0"

 

)

 

 

This part "and IsEmpty ( field2 ) = 1" still doesn't work. When I put a date in that field the calc does not see it.

 

Thanks,

 

Dave

Hi Dave,

 

Your calc is asking:

field1 = "a"

or

field1 = "b"

or

field1 = "c" and IsEmpty ( field2 ) ...

... then display field3 otherwise display 0

 

Is this really what you want?  It is only counting the third test if field2 is empty (because everything before the AND and after it is a proposition) - explained perfectly by Comment here:  http://fmforums.com/forum/topic/86680-type-cast-order-of-operations/

 

If you instead want to check for empty date on each test similar to this human-talk walk-through:

 

If field1 = "a" and no date show result from field3

or

field1 = "b" and no date show result from field3

or

field1 = "c and no date show result from field 3

otherwise

0

 

One way this calc then could be written is:

 

Case (

not field2

and

( field1 = "a" or field1 = "b" or field1 = "c" ) ;

field3 ; 0

)

 

the blue is one side of the AND test and the green is the other side.

 

* the not field2 is a boolean test same as IsEmpty(a date) - any date returning true.

Edited March 11, 201312 yr by LaRetta

I guess I thought there was a simple way to write this, and there probably is, but solved at least.

 

Case

 

(

IsEmpty ( CommissionDatePaid ) = "1" and

currentStatus = "active" or

IsEmpty ( CommissionDatePaid ) = "1" and

currentStatus = "start" or

IsEmpty ( CommissionDatePaid ) = "1" and

currentStatus = "rejoin" or

IsEmpty ( CommissionDatePaid ) = "1" and

currentStatus = "associate" ;

 

CommissionRate

 

;

 

"0"

 

)

 

Thanks Dave

Thanks Laretta! I appreciate it!!!

IsEmpty ( CommissionDatePaid ) = "1"

 

No please don't do this.  It isn't even logical.