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.