Use Select with prompt

mtrout
mtrout used Ask the Experts™
on
Hello,
I would like to take a very long IF-THEN-Else statement and make into a case statements if possible.

What I have:
(if uppercase({?Location})='ALL'
then
true
else
if {?Location}='Washington, DC'
then
{Table_A.citystate} in ["Washington Area, DC","Washington, DC","Washington Navy Yard"]
else
if  (tons of more else if's)
else
{?Location}={TABLE_A.citystate})

The report prompts for a location and other prompts, but for this one (location), I was hoping to make case statements for the location.  Can you please assist?  Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Michael CarrilloInformation Systems Manager
Top Expert 2012

Commented:
I am not sure I understand your request.

You could create a variable (YourVariable) and set it equal to uppercase({?Location}).
Then use it in your case statement:

select YourVariable
Case 'ALL'
...
Case 'WASHINGTON, DC'
...
Case '....'
...
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Close.  Need : after the case 'x' :

select YourVariable
Case 'ALL' :
...
Case 'WASHINGTON, DC' :
...
Case '....' :
...
Default :

mlmcc

Author

Commented:
this is what I tried and it's not working.  received the message that the remianing text does not appear to be part of the formula.  
 I have other if than statements above, it flaggs all of the statements that are commented out and I checked them all.  Have I messed up the Default statement?  What did I do wrong?  Thank you.  Oh, I just did two out of the many I will need to do.  Thank you.
and
select {?Location}
Case "ALL":
true
Case "Washington, DC":
"Washington Area, DC","Washington, DC","Washington Navy Yard"
Case "Clearwater, FL":
"Clearwater, FL","Clearwater , FL"
Default:
{?Location}= {TABLE_A.citystate}
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Try this one

select {?Location}
Case "ALL":
true
Case "Washington, DC":
{TABLE_A.citystate}  IN ["Washington Area, DC","Washington, DC","Washington Navy Yard"]
Case "Clearwater, FL":
{TABLE_A.citystate}  IN ["Clearwater, FL","Clearwater , FL"]
Default:
{?Location}= {TABLE_A.citystate} 

Open in new window


mlmcc
Michael CarrilloInformation Systems Manager
Top Expert 2012

Commented:
I am not sure if the Crystal Reports Case statements require a break or not.  If you have problems with the syntax, try a break as follows:

select {?Location}
Case "ALL":
  true
  break
Case "Washington, DC":
  {TABLE_A.citystate}  IN ["Washington Area, DC","Washington, DC","Washington Navy
  break
Case ...
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
As far as I know it doesn't require the break.

mlmcc
You were using UpperCase ({?Location}) = 'ALL' in your original formula.  If the "ALL" may not be in all caps, you could add the possible values to the Select-Case.  Something like

Case "ALL", "all", "All"  <etc., etc.>:


 But it might be easier to test for "all" outside of the Select-Case.  For example:

(
UpperCase ({?Location}) = "ALL"
or
(
select {?Location}
Case "Washington, DC":
  etc., etc.
Default:
{?Location}= {TABLE_A.citystate}
)
)


 James

Author

Commented:
Hello There,

 Thank you so much.  I used a combination of two responses:
I like the usage of IN, that's what I was using and I separated the ALL so that if someone used aLL or AlL and it goes on...I will not need to worry.  I prefer using the uppercase function.  Thank you again mlmcc and James.

and
((if uppercase({?Location}) = 'ALL'
then
true)
or
(select {?Location}
Case "Washington, DC":
{TABLE_A.citystate} in ["Washington Area, DC","Washington, DC","Washington Navy Yard"]

case "Clearwater, FL":

{TABLE_A.citystate}  in ["Clearwater, FL","Clearwater , FL"]

case "Pittsburgh, PA":
{{TABLE_A.citystate}  in ["Pittsburgh, PA","Pittsburg, PA","Pittsuburgh, PA"]

Default: {?Location}= {TABLE_A.citystate}))
You're welcome.  Glad I could help.

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial