Using the CASE Function
by Robert Tishkevich
Posted at www.workings.com January 22, 2001

Most users are very familiar with and feel very comfortable using the IF function. For example, if your business only has clients from two different states, you could write a field rule that determines the tax rate as follows:

If(State = “MD”, .05, .06)

In plain English, the above formula says if the customer’s state is MD, the tax rate is 5% otherwise the rate is 6%.  That’s pretty simple and fairly easy to set up. The IF function is a viable option if you are dealing with only 2 or 3 tax rates. But once you get up to four or more options, the IF function starts to get very confusing and cumbersome. You may want to go with a cleaner, easier to set up alternative, I strongly recommend the CASE function.

What is the CASE function? The Case function begins with the word SELECT and guess what, it ends with the words END SELECT. It is a decision making process that examines a group of conditions and/or corresponding expressions, returning the value of the first expression whose corresponding conditions evaluates to TRUE. 

Once the CASE function finds the first true statement, it is finished. In our example above, assume you are looking for the tax rate from several different states. You could use the IF function, but it would take some heavy-duty concentration, too many parentheses and testing to get it working properly.

However, if you choose to utilize the CASE function, it is a piece of cake. Here is how it would look:

Ask the user to enter the state abbreviation. AlphaFive will then put the answer in a variable called Response, which is an arbitrary name made up by the programmer.

Dim Response as C   [C stands for Character type field]

SELECT

          CASE RESPONSE = “MD”

                    RATE = .05

          CASE RESPONSE = “PA”

                    RATE = .06

          CASE RESPONSE = “NJ”

                    RATE = .07

          CASE RESPONSE = “NY”

                    RATE = .08

          CASE RESPONSE = “FL”

                    RATE = .055

          CASE ELSE

                    RATE = .04

END SELECT

As stated above, all of the Case statements come between the SELECT and END SELECT statements. The maximum number of CASE statements allowed in a single expression is 16. If that's not enough, you can just write consecutive CASE statements. The above example was pretty basic, but you can use more complicated statements and/or expressions. It is not required, but I highly recommend that you use a CASE ELSE option as the last Case Statement to cover other possibilities.

There are many other ways you could use this function, let me give you one more example where you would utilize the CASE function. In a checkbook / ledger application, you would use the CASE function to decide whether the transaction is a deposit, withdrawal, bank fee, check cashed, etc. and have alpha turn the transaction into either a DEBIT or CREDIT.

I think you will agree the CASE function is relatively simple to set up and gives you an easy way to write scripts that offer the end user multiple options without thoroughly confusing the programmer.

###