5 Replies Latest reply on Sep 6, 2016 4:30 PM by BruceRobertson

Alright, my nested IF statement was ugly so I thought to try CASE instead... Here's a snippet of my calculation field:

...

(Length  ≤ "120") and (Diameter  ≤ "6") ; "Standard Box, Thick/Thick" ;

(Length  ≤ "120") and (Diameter  ≥ "6.1" and Diameter  ≤ "10") ; "Standard Box, Thick/Thin" ;

(Length  ≤ "165") and (Diameter  ≤ "5") ; "Long Thin Box, Thick/Thick" ;

(Length  ≤ "165") and (Diameter  ≥ "5.1" and Diameter  ≤ "7") ; "Long Thin Box, Thick/Thin" ;

...

Now, I have a record that has a Length of 100 and a Diameter of 6.35 yet it's skipped "Standard Box, Thick/Thin" and landing on true for "Long Thin Box, Thick/Thin" ... Huh?? Does anyone have any idea why this might be? Do I have a backwards less than/greater than symbol and I'm just going mad?

Thanks for the double check.

• ###### 1. Re: A Question About Case

Hi,

I can only guess, considering that I can't see above the snippet, that at least one field might be defined as text instead of number.  Otherwise, it looks good.

Not that this is part of the 6.35 problem, but you should probably modify your formula to use >6 instead of >= 6.1.  Just sayin'.

• ###### 2. Re: A Question About Case

For fun I took your suggestion and changed the 5.1, 6.1, etc, fields to be simply > 5, > 6, etc. The entire calculation is as follows ...

Case (

(Length  ≥ "72") and (Diameter ≤ "7") ; "Tiny Box, Thick/Thick" ;

(Length  ≥ "72") and (Diameter > "7" and Diameter  ≤ "8") ; "Tiny Box, Thick/Thin" ;

(Length  ≤ "120") and (Diameter  ≤ "6") ; "Standard Box, Thick/Thick" ;

(Length  ≤ "120") and (Diameter  > "6" and Diameter  ≤ "10") ; "Standard Box, Thick/Thin" ;

(Length  ≤ "165") and (Diameter  ≤ "5") ; "Long Thin Box, Thick/Thick" ;

(Length  ≤ "165") and (Diameter  > "5" and Diameter  ≤ "7") ; "Long Thin Box, Thick/Thin" ;

(Length  ≤ "165") and (Diameter  > "7" and Diameter  ≤ "8") ; "Long Thin Box, Thin/Thin" ;

(Length  ≤ "160") and (Diameter  ≥ "8" and Diameter  ≤ "23") ; "Fat Box, Thick/Thin" ;

(Length  ≤ "160") and (Diameter  > "23") ; "Fat Box, Thin/Thin" ;

(Length  ≤ "220") and (Diameter  < "8") ; "Wide Flat Box, Thick/Thick" ;

(Length  ≤ "220") and (Diameter  ≥ "8" and Diameter  ≤ "10") ; "Wide Flat Box, Thick/Thin" ;

(Length  ≥ "160" or Length  ≥ "220") and (Diameter > "10") ; "Wooden Box" ;

"Special / Other "

)

... and it still isn't grabbing the first correct result. I checked and Diameter and Length are both Number fields. Hmm. My only other thought was maybe it doesn't like all my parenthesis around the criteria? Does that matter with Filemaker?

• ###### 3. Re: A Question About Case

Stop using quotes in numeric expressions.

I have a record that has a Length of 100 and a Diameter of 6.35

That's entirely different from:

I have a record that has a Length of "100" and a Diameter of "6.35"

The second expression is text; the first is numeric.

• ###### 4. Re: A Question About Case

Aaahhh, see, I thought any piece of data the calculation looked for had to be in "".

So, I've removed the double quotes from around all my criteria and... get a completely different result. Now I get Tiny Box, Think/Thick which I now see is actually due to an error. I should have had Less Than where I had Greater Than.

It works now! Thank you for the clarification on "text" versus numbers.

• ###### 5. Re: A Question About Case

(Length  ≤ 120  and Diameter  ≤ 6) ; "Standard Box, Thick/Thick" ;

(Length  ≤ 120) and (Diameter  ≥ 6.1 and Diameter  ≤ 10) ; "Standard Box, Thick/Thin" ;

(Length  ≤ 165) and (Diameter  ≤ 5) ; "Long Thin Box, Thick/Thick" ;

(Length  ≤ 165) and (Diameter  5 and Diameter  ≤ 7) ; "Long Thin Box, Thick/Thin" ;