multiple value parameter to pass into formula 1

[Hinfer]

I have a parameter called Choose a competency and I have allowed multiple values. I have entered the default values in for this parameter. My record selection formula will look at the string selected and then select the records belong to the right group number. If user selects multiple values, example, the user selects both the National Certificate of Competence and Specialized Training Endorsement, the user should see all records in group number 0,1,150.

However it appears it only selects 0,1 and does not get 150. This is happening because the formula below evaluates the National Certificate of Competency first. If it holds true then it exits the entire if then else statement and will not evaluate the second value (Specialized Training Endorsement). Only one parameter value will be evaluated and the one that will get evaluated is the first one that holds true in the following select statement. Is there an alternative way to do this? I do not want to create every possible combination with an if then else statement

if {?Choose a competency group} ='National Certificate of Competence' then {pw001p05.groupno} in [0,1] else
if {?Choose a competency group} ='National Certificate of Endorsement' then {pw001p05.groupno} in [50] else
if {?Choose a competency group} ='Flag State Endorsement' then {pw001p05.groupno} in [100] else
if {?Choose a competency group} ='Specialized Training Endorsement' then {pw001p05.groupno} in [150] else
if {?Choose a competency group} ='Education' then {pw001p05.groupno} in [300] else
if {?Choose a competency group} ='Courses' then {pw001p05.groupno} in [200,201,250] else
if {?Choose a competency group} ='All' then {pw001p05.groupno} in [0,1,50,100,150,200,201,250,300]

 

[synapsevampire]

I would approach this entirely differently.

When creating the defaults, do so from a file (Import Pick List) and use the "National Certificate of Competence" portion as the descriptioon, and the code as the value, then select Display Description.

The user will see only the descriptions, yet your record selection formula can just have:

{pw001p05.groupno} = {?Choose a competency group}

and Crystal will do the work for you.

-k

 

[vidru]

k,

I was thinking the exact same thing, but what about the 'Courses' group that is 3 different group numbers?

I think you'd first have to check if 'Courses' was in the selected list, then set up the selection formula.

I'd do exactly as sv suggested for setting your default parameters, with the exception of the 'Courses' entry, for which you can only set up a single value (try setting that one to 200). Then, the record selection formula would look something like:

if {?Choose a competency group} ='All' then {pw001p05.groupno} in [0,1,50,100,150,200,201,250,300]
else if 'Courses' in {?Choose a competency group} then
{pw001p05.groupno} in {?Choose a competency group} or
{pw001p05.groupno} in [201,250] // didn't include 200 because planning on setting the default to 200
else {pw001p05.groupno} in {?Choose a competency group}

-dave

 

[vidru]

Er, jumped the gun a bit there.

Didn't address the 'all' situation or the 'Courses' quite correctly, completely missed the fact that Nat'l Cert. of Endorsement had multiple groups, and that now we're dealing with numbers. Here is what the parameter list should look like (note the -99 for All):

Value Description
-99 All
0 National Certificate of Competence
50 National Certificate of Endorsement
100 Flag State Endorsement
150 Specialized Training Endorsement
300 Education
200 Courses


Final formula would be more like this:

if {?Choose a competency group} = -99 then {pw001p05.groupno} in [0,1,50,100,150,200,201,250,300]
else if 0 in {?Choose a competency group} and 200 in {?Choose a competency group} then
{pw001p05.groupno} in {?Choose a competency group} or
{pw001p05.groupno} in [0,1] or
{pw001p05.groupno} in [200,201,250]
else if 0 in {?Choose a competency group} then
{pw001p05.groupno} in {?Choose a competency group} or
{pw001p05.groupno} in [0,1]
else if 200 in {?Choose a competency group} then
{pw001p05.groupno} in {?Choose a competency group} or
{pw001p05.groupno} in [200,201,250]
else {pw001p05.groupno} in {?Choose a competency group}

-dave

 

[Hinfer]

This was the problem that I had when using snapsevampire's method. I could not enter more than one value for each description. Will try vidru's suggestion (although have not fully understood this method yet).

 

[synapsevampire]

Sorry, you're right, Dave, I didn't examine the formula closely, just responded to the question.

I'm not to sure about your formula though...

I'd use something like the following:

(
if {?Choose a competency group} ='National Certificate of Competence' then {pw001p05.groupno} in [0,1]
)
and
(
if {?Choose a competency group} ='National Certificate of Endorsement' then {pw001p05.groupno} in [50]
)
and
etc...

-k

 

[Turkbear]

Hi,
Try this variant ( or something like it) :

(
if 'National Certificate of Competence' IN {?Choose a competency group}
 then {pw001p05.groupno} in [0,1]
   else False;
)
OR
(
if 'National Certificate of Endorsement' IN {?Choose a competency group}
 then {pw001p05.groupno} in [50]

else
  False;
)
OR

That should apply the multiple conditions and return all that match any of them, since it will result in a critera like
Where
{pw001p05.groupno} in [0,1]
OR
{pw001p05.groupno} in [50]
OR
etc..

Its the OR that does it, I think..


PS: I found the base of this method on this site, but can't remember from which very skilled poster..I would like to give credit where credit is due..

 

[synapsevampire]

Turkbear: Careful, youy may not get the SQL to pass there, adn you may have a logic bomb.

-k

 

[Turkbear]

Hi,
Not sure, but you are usually right, so:...
This is a variant of the code I found and it may be misapplied in this instance, but the logic I was trying for is to take each element of the passed parameter ( since the OP indicated a multiple value select) and create a selection criteria for it. I then would OR the results together to create the 'actual' final criteria since they all apply to the same field in the data.

I was not able to test it since none of my reports ( or my data) would let me set up a good test case..

 

[Turkbear]

Hi again, I was able to build a test case that seems to indicate it would work.
I have a report that shows an alpha list of names..I created a parameter the accepts multiple string values and I set the following as the Record selection criteria:

( If '5' in {?Codelist}  then
  {HR_PUBLIC.EMPL_NM} startswith 'G'
   else
   false;
   )

 OR

 (If '6' IN {?Codelist} then
   {HR_PUBLIC.EMPL_NM} startswith 'D' 
    else
     False;
     )
 
 OR

(If '7' in {?Codelist} then
   {HR_PUBLIC.EMPL_NM} startswith 'X' 
    else
     False;
     )

When run, if a provide 5 and 7 as the parameter values I get all employees whose name starts with 'G'or 'X'.

If I use just 6 I get the 'D's
If I use any other or none at all, I get no records - I did not add an 'All' handler..

 

[Hinfer]

Thank you all for all your help. I used vidru's suggestion and it seems to work. Thank you

I also decided to try synapsevampire's suggestion (since it also looks simpler) but it did not retrieve any values though I am not sure why?

I also decided to try Turkbear's suggestion and did run into a "logic bomb" (the report just keeps requerying continously).

This was a good learning experience for me. Thanks everyone

 

[vidru]

Happy to help, Hinfer.

If you don't understand why it works, just ask and I'll break it down for you.

-dave

 

[Hinfer]

Thanks vidru. I understood it as I implemented it. I've got to stop thinking so linear! Thanks again!