7 Replies Latest reply on Mar 28, 2014 10:05 AM by hanstrager

# I can’t figureout how to use the Case/If statement. I have a calculation, which depends on d...

### Post

I can’t figureout how to use the Case/If statement.

I have a calculation, which depends on different parameters. And been trying to fool around with the case statement which I can’t get working.

I’m trying to figure out how to get the following to work:

Case (Investment VAT switch = "with VAT" ; TOTAL AMOUNT;

Investment VAT switch = "without VAT" ; TOTAL AMOUNT - (TOTAL AMOUNT / 6);

Investment VAT switch = "without VAT" & VAT_noVAT = "noVAT" ; TOTAL AMOUNT)

Can get the first 2 cases to work, but the last one is giving me the result TOTAL AMOUNT - (TOTAL AMOUNT / 6); how do I use multiply parameters..?

• ###### 1. Re: I can’t figureout how to use the Case/If statement. I have a calculation, which depends on d...

You need to use AND not &. & is the concatenation operator used to "glue" two text values into a single string. "Apple " & "Orange" produces the value "Apple Orange".

• ###### 2. Re: I can’t figureout how to use the Case/If statement. I have a calculation, which depends on d...

http://filemakermisc.wordpress.com/2013/09/11/the-case-function-in-a-calculation/

Case (
Cost < ”5″  ;   Cost * “4″   ;
Cost > ”5.01″ and Cost < “15″  ; Cost * “3.5″ ;
Cost > “14.99″ and Cost < “25″  ;  Cost * “3″  ;
Cost > “24.99″ and Cost < “40″  ;  Cost * “2.75″ ;
Cost > “39.99″ and Cost < “60″  ;  Cost * “2.5″ ;
Cost > ”59.99″ and Cost < “100″ ;  Cost * “2.25″ ;
Cost > “99.99″ and Cost < “200″  ; Cost * “2″ ;
Cost > ”199″; Cost * “1.6″)

• ###### 3. Re: I can’t figureout how to use the Case/If statement. I have a calculation, which depends on d...

It's still not working, what am I missing...?

I got the following:

Case (
Investment VAT switch = "with VAT" ; TOTAL AMOUNT;
Investment VAT switch = "without VAT" ; TOTAL AMOUNT - (TOTAL AMOUNT / 6) ;
Investment VAT switch = "without VAT" and VAT_noVAT = "noVAT" ; TOTAL AMOUNT)

Investment VAT switch = "without VAT" and VAT_noVAT = "noVAT" ; TOTAL AMOUNT) still gives me: TOTAL AMOUNT - (TOTAL AMOUNT / 6)

• ###### 4. Re: I can’t figureout how to use the Case/If statement. I have a calculation, which depends on d...

I should have noticed that detail sooner.

Case selects the result for the first expression to evaluate as True.

In the case of

Investment VAT switch = "without VAT" and VAT_noVAT = "noVAT"

Investment VAT switch = "without VAT"

will also be True and is evaluated first. Thus, the last Boolean expression is never evaluated. I think that was the Point to DavidAnders' post but I didn't see it either.

Rewrite the function to test for the more specific condition first:

Case (
Investment VAT switch = "with VAT" ; TOTAL AMOUNT;
Investment VAT switch = "without VAT" and VAT_noVAT = "noVAT" ; TOTAL AMOUNT ;
Investment VAT switch = "without VAT" ; TOTAL AMOUNT - (TOTAL AMOUNT / 6) )

• ###### 5. Re: I can’t figureout how to use the Case/If statement. I have a calculation, which depends on d...

It works... Thank you.

It there a way to tell this calculation that if 'Investment VAT switch' or 'VAT_noVAT' is empty then the then the calculation shouldn't calculate anything and just show up empty..?

• ###### 6. Re: I can’t figureout how to use the Case/If statement. I have a calculation, which depends on d...

If the Investment VAT switch field is empty, this result should already be the case. To force an empty result if the VAT_noVAT field is empty, use:

Case (
IsEmpty ( VAT_noVAT ) ; "" ;
Investment VAT switch = "with VAT" ; TOTAL AMOUNT;
Investment VAT switch = "without VAT" and VAT_noVAT = "noVAT" ; TOTAL AMOUNT ;
Investment VAT switch = "without VAT" ; TOTAL AMOUNT - (TOTAL AMOUNT / 6)
)

• ###### 7. Re: I can’t figureout how to use the Case/If statement. I have a calculation, which depends on d...

Thank you