How to enter a Like parameter

I have a report whereby I have a parameter to select ALL or a task
I have created the following forula
if {?Task Number} ='ALL' then 1 else
if {?Task Number} = {TASK.TASKNUM} then 0 else 1
How can i modify this to be able to select some tasks by inputtin example RS%

Gordon
Gordon HughesDirectorAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
I don't see the need for the TaskNumber formula but that may make it easier for you to understand.  One issue with it is the selection criteria won't be passed to the database for filtering so the report could run slower.

To me I think you have the formula reversed onthe last test
I think it should be
{Task Number} :-
if {?Task Number} ='ALL' then 
     1 
else  if {?Task Number} = {TASK.TASKNUM} then 
     1
else 
     0

Open in new window


Do you expect the user to enter the wildcard?  
If so this should work.  User should enter * for the wildcard.  Crystal will convert it to the appropriate value for the database.

{Task Number} :-
if {?Task Number} ='ALL' then 
     1 
else  if  {TASK.TASKNUM} LIKE {?Task Number} then 
     1
else 
     0

Open in new window


If you want to handle the wildcard rather than require the user to enter  *RS*  or *RS  or RS*
you will need to decide if to always add the wildcard to what the user enters or to try to determine if it should be added.

mlmcc
0
 
Rgonzo1971Commented:
Maybe

if {?Task Number} like 'RS*'

Regards
0
 
Gordon HughesDirectorAuthor Commented:
Hi
Tried
if {?Task Number} ='ALL' then 1 else
if {?Task Number} like 'RS%' then 0 else 1

But does not work

Gordon
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Rgonzo1971Commented:
and with *
0
 
Gordon HughesDirectorAuthor Commented:
Hi

Does not work with the * either
I think it needs to know what field it is looking at so I think the formula should include a variant
of
if {?Task Number} = {TASK.TASKNUM} then 0 else 1

Gordon
0
 
Rgonzo1971Commented:
Sorry can't help further
0
 
Gordon HughesDirectorAuthor Commented:
Someone please help

Gordon
0
 
vastoCommented:
I believe it should be the opposite

if {?Task Number} ='ALL' then 1 else
 if {?Task Number} like 'RS*' then 1 else 0

if you are trying to find partial matches in  {TASK.TASKNUM} try

 if {TASK.TASKNUM} like {?Task Number} +'*' then 1 else 0


You may need to replace * with %
0
 
Gordon HughesDirectorAuthor Commented:
Hi

What I am trying to do is give an option to see ALL the tasks or select part of the task number
The current formula works ok if you select ALL or a complete task number
Does this make sense

Gordon
0
 
vastoCommented:
then try

if {?Task Number} ='ALL' then 1 else
if {TASK.TASKNUM} like {?Task Number} +'*'  then 0 else 1

but this will return 1 if the parameter value is ALL or NOT like {?Task Number}

If you want just to filter the data add this to the Record Selection Formula

{?Task Number} ='ALL' OR
{TASK.TASKNUM} like {?Task Number} +'*'
0
 
mlmccCommented:
Assuming you want it to match the first characters then it is

{Product.Product Name} LIKE {?ProductName} & "*"

Where are you using the formula?

If it is in the Select Expert then the formula has to return TRUE or FALSE

mlmcc
0
 
Gordon HughesDirectorAuthor Commented:
Hi mimcc

I have a parameter field {Task Number} that says ALL  and allows Custom Values, Multiple values and Discrete values

To make this work I have a formla {Task Number} :-
if {?Task Number} ='ALL' then 1 else
if {?Task Number} = {TASK.TASKNUM} then 0 else 1

Then in the Select Expert @Task Number say is equal to 1.00

This all works fine if I want to see all the tasks or I can add the complete task numbers as a selection

What I would like to be able to do is to either select ALl or Enter the complete task number or select some of the task number i.e. RS% or %RS% etc

Hope this makes sense, so what do I need to modify?
Gordon
0
 
James0628Connect With a Mentor Commented:
Some of this has already been covered, but if the parameter allows multiple values, but _not_ ranges, then a test like ( {field} like {?parameter} ) should work, but the user will have to enter any wildcards (*).  You can't use something like {?parameter} + "*" to add the wildcard, because CR treats a multi-value parameter like an array, so {?parameter} + "*" just adds the "*" as another element in the array, and you end up with the last test being {field} like "*", which matches everything.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.