12 Replies Latest reply on Apr 11, 2017 9:01 AM by milefaker76

# Case Statement Question

I am baffled by the Case Statement and curious if anyone has this issue.

The case statement goes through each test in order and returns the first result where a test is true.

However, how do you avoid this if there are multiple conditions to be met?

Below is the statement I am having difficulty with, simple delivery date using greater than less than operators with dates.

Case (

Qty Ship = Order Qty ; "Bullseye" ;

Qty Ship ≥ Minimum ; "In Spec";

Qty Ship ≤ Minimum ; "Below Min Spec" ;

Qty Ship ≤ Maximum ; "In Spec";

Qty Ship ≥ Maximum ; "Above Max Spec" )

I looked through the forum but can't find anything related to this. It seems like a simple thing to fix but the lightbulb just wont't go on.

Thanks!

G

• ###### 1. Re: Case Statement Question

you have to use the AND or OR logical operators to form complex expressions.

• ###### 2. Re: Case Statement Question

Case (

Qty Ship ≤ Minimum ; "Below Min Spec" ;

Qty Ship = Order Qty ; "Bullseye" ;
Qty Ship ≥ Minimum AND Qty Ship ≤ Maximum ; "In Spec";

Qty Ship ≥ Maximum ; "Above Max Spec" )

• ###### 3. Re: Case Statement Question

AND is your friend.

You can place AND for multiple criteria in each 'test' or if you have the one condition then a Case():

Example 1:

=========

Case

( xyz = 1 AND abc < 3 ; "*"

; xyz = "" AND abc = 3 ; "" // nothing

; abc > 3 ; "WHOA THERE NELLY!"

; "" // default )

Example 2:

=========

xyz=1

AND

Case

( abc < 3 ; "*"

; abc = 3 ; ""

; abc > 3 ; "WHOA! TOO MUCH!"

; "" // default)

The "OR" and other operators can be used in the calculations and combined as needed.

beverly

• ###### 4. Re: Case Statement Question

Good point Beverly

Case (

Qty Ship ≤ Minimum ; "Below Min Spec" ;

Qty Ship = Order Qty ; "Bullseye" ;
Qty Ship ≤ Maximum ; "In Spec";

Qty Ship > Maximum ; "Above Max Spec" )

• ###### 5. Re: Case Statement Question

I have tried this a few different ways as described but the statement is returning some bad results.

I changed the default to "Unknown" just to see where the problem lies.

Here is the statement and the result.

Case

(Qty Ship  ≥  Minimum and Qty Ship  ≤  Maximum ; "In Spec" ;

Qty Ship  ≤  Minimum and Qty Ship  ≥  Maximum ; "Out of Tolerance" ;

"Unknown" )

Max = Maximum and Min= Minimum in the statement.

Any thoughts were I am doing something incorrectly?

• ###### 6. Re: Case Statement Question

I would try:

Case

(Qty Ship  ≥  Minimum and Qty Ship  ≤  Maximum ; "In Spec" ;

Qty Ship  ≤  Minimum OR Qty Ship  ≥  Maximum ; "Out of Tolerance" ;

"Unknown" )

beverly

• ###### 7. Re: Case Statement Question

Try what we suggested instead.

A quantity will never be both less than the minimum AND greater than the maximum so at the very least, that part of Case should use OR rather than AND.

Another possible failure point here are data types. The qty, max, and min fields should all be of type number. If they are defined as text, their values will look correct but value comparisons will follow text rules instead of number rules.

Finally, make sure that the field with this calculation is a field of type calculation, not a text field with an auto-enter calculation. If you modify an auto-enter expression, the value of the field in existing records is not automatically re-calculated.

• ###### 8. Re: Case Statement Question

Bet - shouldn't it be like this?

Case

(Qty Ship  ≥  Minimum and Qty Ship  ≤  Maximum ; "In Spec" ;

Qty Ship  <  Minimum OR Qty Ship  >  Maximum ; "Out of Tolerance" ;

"Unknown" )

• ###### 9. Re: Case Statement Question

When I use that statement, this is the result.

This is how I have my fields set up...disregard the summary fields.

• ###### 10. Re: Case Statement Question

What result types have you specified for the maximum and Minimum fields?

What data type did you select for Qty Ship?

My guess is that you have some text values here as that's consistent with the results that I see for the first several records.

When comparing text,

"4" > "100"

Is a true statement for the same reason that

"Big" > "Abracadabra"

is also true.

• ###### 11. Re: Case Statement Question

Bruce is correct. You want to include the max and min as "In Spec".

This could also be written:

Case

( Qty Ship ≥ Minimum AND Qty Ship ≤ Maximum ; "In Spec"

; Qty Ship < Minimum ; "Out of Tolerance"

; Qty Ship > Maximum ; "Out of Tolerance"

; "Unknown" )

You should never get to "Unknown".

beverly

• ###### 12. Re: Case Statement Question

The Qty Ship field was set to text.

I appreciate the help, I probably would have been messing around with this for months.