CASE 'Else If' Logic

[kpcollier]

I'm in need of some help with some CASE logic. 

First, let me lay down how this is suppose to go in my app. There are 4 fields in question: SQFT, PPSQFT, SQFT_Override, PPSQFT_Override. 

The most common case is going to be (SQFT * PPSQFT), which is a value for sqft * price per sqft. These values are automatically calculated from other fields. However, the user can 'Override' these values to output another number. So,

• it would only be (SQFT * PPSQFT) if 'SQFT_Override' and 'PPSQFT_Override' both are blank, or null. 
• If 'PPSQFT_Override' is not null, it would be (SQFT * PPSQFT_Override). 
• If 'SQFT_Override' is not null, it would be (SQFT_Override * PPSQFT)
• If 'SQFT_Override' and 'PPSQFT_Override' are both not null, it would be (SQFT_Override * PPSQFT_Override)

In formula terms, this is the closest I got:

CASE

WHEN [@field:PPSQFT_Override] is not null AND [@field:SQFT_Override] is not null

THEN [@field:SQFT_Override] * [@field:PPSQFT_Override]

ELSE WHEN [@field:PPSQFT_Override] is not null AND [@field:SQFT_Override] is null
  
THEN [@field:SQFT] * [@field:PPSQFT_Override]
  
ELSE [@field:SQFT] * [@field:PPSQFT]

END

Then I obviously found out that you cannot use 'Else When' or 'Else If' in CASE statements. 

What is the best way to rewrite a CASE statement to include a third condition?

[kpcollier]

I'm a rook. Figured it out. Just list the other conditions and it will sort through 

CASE

WHEN [@field:PPSQFT_Override] is not null AND [@field:SQFT_Override] is not null

THEN [@field:SQFT_Override] * [@field:PPSQFT_Override]

WHEN [@field:PPSQFT_Override] is not null AND [@field:SQFT_Override] is null
  
THEN [@field:SQFT] * [@field:PPSQFT_Override]
  
WHEN [@field:PPSQFT_Override] is null AND [@field:SQFT_Override] is not null
  
THEN [@field:SQFT_Override] * [@field:PPSQFT]
  
ELSE [@field:SQFT] * [@field:PPSQFT]

END

 

[TellMeWhy]

I just want to add Caspio's How-To Article Link here: https://howto.caspio.com/function-reference/

Case When is tackled in this article, in case anyone is looking for slightly more information

[GoodBoy]

Hello! I just want to add this informative video about SQL Statements in Calculated Fields: https://www.youtube.com/watch?v=15zAruBT3LA