Using the CASE() Expression
by Robert Tishkevich
Many Alpha users are familiar with IF() in expressions or Xbasic.1 For example, if all of your clients reside in only one of two states where the tax rate is either 5 or 6%, you could write an if expression that determines your customer's tax rate as follows:
If(State = "MD", .05, .06)
In plain English, the above formula says if the customer's state of residence is MD, the tax rate is 5%, otherwise the rate is 6%. Using IF() is a viable option if you are dealing with only 2 or 3 choices, as in our example above. In my opinion, once you go above three choices, IF() can become extremely confusing and cumbersome.
I've seen questions posted on Alpha's outstanding discussion board asking for help with complicated, multi-line IF() statements, as people understandably get confused when there are numerous parentheses and commas in one statement. And once you have to include .and. or .or. functionality in your expression, it can become infinitely more complex.
For example, here is an IF() expression that does not utilize .and. / .or. expressions. Nonetheless, it still reaches the point of unnecessary complexity (and I've seen far more complex ones):
If(State = "MD", RATE = .05, If(State = "PA", Rate = .06, If(State = "NJ", RATE = .07, If(State = "NY", RATE = .08, If(State = "FL", RATE = .055, .04)))))
When writing IF() expressions, I'm never sure whether I have all the commas where they belong or the correct number of parentheses. Why add unnecessary clutter and complexity to your formulas when there is a cleaner, easier alternative? When you are working with an Xbasic script, and you want to present the users with a list of choices that goes beyond three, I strongly recommend using CASE(). Let's re-write the above expression using what I think is a clearer, easier methodology:
SELECT
CASE vResponse = "MD"
RATE = .05
CASE vResponse = "PA"
RATE = .06
CASE vResponse = "NJ"
RATE = .07
CASE vResponse = "NY"
RATE = .08
CASE vResponse = "FL"
RATE = .055
CASE ELSE
RATE = .04
END SELECT
CASE() begins with the word SELECT, and ends with the words END SELECT. It is a decision-making process that examines a group of conditions, returning the value of the first expression whose corresponding condition evaluates to TRUE. Once CASE() finds the first true statement, it stops evaluating statements because it's job is finished.
There are many other ways you could use CASE(). Let me show you another example. After a user clicks on a form button, we want to provide the user with a variety of choices for viewing a report. In this scenario, he/she will choose to see a report where the members reside in a particular city, state, or zip code. When making their selection, the user can elect to choose from a list that pops up or type in the information manually. We could use a complicated IF() statement, but CASE() makes this task much easier.
Note:
Since we're concerned only with CASE() in this article, I removed all of the lines in the script that set up the query that will run, based upon the user's response. I replaced the original information with the lines, "do other things, run a query" to keep the script short and easier to read.
I left my script notes intact which set up and explain the goal / scenario.
'-----------------------------------------------------------------------
'Goal: Preview a Report that is based upon a Query of tblMembers
'Allow the user to search for members by City, State, or Zip
'and then Preview a Report based upon the appropriate query
'The user can manually type the name of the City, State, or Zip
'or the user can make a selection from a pop up list.
'That means the user will have a total of six [6] choices,
'two [2] each for City, State, & Zip
'When the user presses a form button, a radio box pops up
'The user's choice becomes the variable vResponse
'The variable vResponse will be evaluated by the Case()
'Written by Robert Tishkevich 10-04-2000, updated 01-18-2001
'-----------------------------------------------------------------------
DIM vResponse as C
vResponse = ui_get_radio("",1,"Choose City from a List","Type a City Name",\
"Choose State from a List","Type the State's 2 Letter Code",\
"Choose ZipCode from a List "," Type a FIVE Digit ZipCode ex. 47354")
On Error Goto Error_Handler
SELECT
CASE alltrim(vResponse) = " "
ui_msg_box("Blank Choice", "NO Selection Made ")
Goto Script_Ends
'-----------------------------------------------------------------------
CASE alltrim(vResponse) = " Choose City from a List "
do other things
run a query
:report.preview("rptMembCity")
'-----------------------------------------------------------------------
CASE alltrim(vResponse) = " Type a City Name "
do other things
run a query
:report.preview("rptMembCity")
'-----------------------------------------------------------------------
CASE alltrim(vResponse) = " Choose State from a List "
do other things
run a query
:report.preview("rptMembStates")
'-----------------------------------------------------------------------
CASE alltrim(vResponse) = " Type the State's 2 Letter Code "
do other things
run a query
:report.preview("rptMembStates")
'-----------------------------------------------------------------------
CASE alltrim(vResponse) = " Choose ZipCode from a List "
do other things
run a query
:report.preview("rptMembZip")
'-----------------------------------------------------------------------
CASE alltrim(vResponse) = "Type a FIVE Digit ZipCode ex. 47354"
do other things
run a query
:report.preview("rptMembZip")
'------------------
END SELECT
'------------------
Script_Ends:
END
Error_Handler:
msg = error_text_get(error_code_get())+chr(10)+chr(13)+"Error occurred at
line "+str(error_line_number_get(),4,0)
ui_msg_box("Error",msg)
Goto Script_Ends:
-----------------------------------------------
Note:
CASE() will examine each response until it finds one that is true, then it will stop. I think you will agree that CASE() is relatively simple to set up and gives you an easy way to write scripts that offer the end user multiple options without being forced to use numerous parentheses and commas.
1 It should be noted that many methods, such as IF() and CASE(), can be used two different ways, and their use is slightly different. For example, when using CASE() in an expression (such as a calculated field), the syntax is:
CASE( case1, result1, case2, result2, ... caseN, result N)
where the maximum number of cases (N) is 16.
When used in a script (as shown in the examples above), the syntax is slightly different. More importantly, there is no limit to the number of cases.
[PRINTER FRIENDLY VERSION]
|
