Knowledge Base

Our Knowledge Base covers a wide variety of technical topics, from the basic to the complex.

I have trouble using the Case function can you give me a few tips on how to use it?

Here is an example of a formula that returns a different price for each of several kinds of fruit. The data type of the result is Number.

case ([Fruit Name], "Apples", .5, "Pears", 1, "Berries", 4)

If the [Fruit Name] doesn't match any of the cases, null is returned. To return a default value when there is no match, add one more Number argument to the end.

case ([Fruit Name], "Apples", .5, "Pears", 1, "Berries", 4, 3)

The way the case function works is that it compares its first argument to each of the even numbered arguments in succession until it finds a match. If it finds a match it returns the value of the odd argument immediately after the matching even argument, otherwise it goes on until it runs out of even numbered arguments that have a following odd argument.

If there is one remaining even numbered argument at the end, it is returned when none of the other values match. If there isn't one remaining even numbered argument at the end, then null is returned when none of the other values match.

The case function takes a minimum of 3 arguments.

The first argument and all the even arguments (except the last one if there are an even number) have to be the same data type.

All the odd arguments other than the first argument (and the last argument if there is an even number) have to be the same data type.

Of course all the arguments could be of the same type.

So the following would work:

case(date, date, text, date, text, date, text, date, text, date, text, text)

The resulting type of the case statement will be the data type of the last argument, which in the above example is text.

Comments

Using "Greater than" in a case statement

I am trying to use a case statement with a "greater than" option.  Basically if my Total Score field is greater than 80% I want to show a Green label.  If between 50%-80% show a Yellow label, and then if it is less than 50% show a Red label.  Quickbase isn't allowing me to use the > and < symbols.  Below is the code it isnt accepting.  Any help?

Case([Total Score],0.8,"<img src=\"https://lpl.quickbase.com/up/bf9dhw8cy/g/rf/ej/va/greenbutton_g.png\">",0.2,"<img src=\"https://lpl.quickbase.com/up/bf9dhw8cy/g/re/ej/va/yellowbutton_y.png\">",0.5,"<img src=\"https://lpl.quickbase.com/up/bf9dhw8cy/g/rd/ej/va/redbutton_r.png\">")

re: Using "Greater than" in a case statement

Hi Nick --

The Case statement requires all the arguments to have the same field type. 

If you created a Formula-Text field to hold the formula and clicked the "Allow HTML" checkbox, then constructed the statement with an If() instead, like so, it should work:

 

If([Total Score]>0.8,"<img src=https://lpl.quickbase.com/up/bf9dhw8cy/g/rf/ej/va/greenbutton_g.png>",
[Total Score]<0.5,"<img src=https://lpl.quickbase.com/up/bf9dhw8cy/g/rd/ej/va/redbutton_r.png>", 
"<img src=https://lpl.quickbase.com/up/bf9dhw8cy/g/re/ej/va/yellowbutton_y.png>"

 

Regards,

--S.

Post new comment

The content of this field is kept private and will not be shown publicly.
Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.

© 1997-2014 Intuit Inc. All Rights Reserved.

Online Database VeriSign Secured Web Based Software TRUSTe Certification Online Database SSAE Audit