Case Calculation

[faaslave]

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

[Rick Whitelaw]

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

[faaslave]

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

[Lee Smith]

Automatic message

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

[faaslave]

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

[LaRetta]

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, 2013 by LaRetta

[faaslave]

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!!!

[LaRetta]

IsEmpty ( CommissionDatePaid ) = "1"

 

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