This discussion is archived
6 Replies Latest reply: Sep 29, 2013 7:09 AM by user476771 RSS

multi-choice parameters with "like" condition

user476771 Newbie
Currently Being Moderated

Hi,

 

I defined a parameter, multi-choice, using a "like" condition.  It always works for the first value of the LOV and ignores the rest of the values chosen.

 

Is there any way that the "like" condition can be used for more than one parameter value?

 

In other words, if someone chooses values A,B,C from the LOV, I would like the condition to be interpreted as:

 

Field like 'A' or Field like 'B' or Field like 'C'.

 

--Further explanation--->

The reason that I need to use LIKE is that the field contains, e.g. A-1, A-2, A-3, B-1,B-2...

Instead of having the LOV list the variations, it would be much easier for the user to choose A and then get all the A's that exist.


Thanks.

Leah

  • 1. Re: multi-choice parameters with "like" condition
    clifford_d Newbie
    Currently Being Moderated

    From what I have experienced, sql does not like multiple options for LIKE.  An option would be to create a new calculation on the field so it only grabs the first letter of the field.  Then your parameter can become a regular '=' or IN condition and would allow the user to get A,B,C values.    Could use Index values to show the true list. 

  • 2. Re: multi-choice parameters with "like" condition
    user476771 Newbie
    Currently Being Moderated

    I am not sure that I am understanding your suggestion.  (By the way, right now we changed the parameter so that it is not multi-choice).

     

    Let's say the list of values would be "permit medical", "permit seniority".  "maternity leave"

    Examples of the field in the table: "permit medical-1", "permit medical-2","permit seniority-4", "maternity leave-2"

     

    If the user chose "permit medical" I would like to retrieve the first two records above.

     

    Some of the beginning letters are the same and they are different lengths.  Would you please give an example to help me understand your explanation?

     

    Thanks.

    Leah

  • 3. Re: multi-choice parameters with "like" condition
    clifford_d Newbie
    Currently Being Moderated

    Take your field that has the values "permit medical-1", "permit medical-2","permit seniority-4", "maternity leave-2" in it.  Going to call it Leave_type.. Create a new calculation called Type_of_leave substr(leave_type,1,14).    Create a new condion using the type_of_leave field = :leave.   You could use a LOV item class to show the real values, if they select Permit Medical, both of the first ones will be selected or any that start with permit medical.

     

    Little clearer ? 

  • 4. Re: multi-choice parameters with "like" condition
    user476771 Newbie
    Currently Being Moderated

    What I am still understanding is defining the calculated field as a predefined length which I don't think will work since some of the values are shorter and some longer.

     

    However, you gave me another idea (unless this is what you really meant).  I can define the calculated field similar to how I defined the list of values, i.e. take the value of the field until the '-' sign (if it exists).  If the actual field value is "permit medical-2", then in the LOV it only says "permit medical".  If the original field is "surgery" then the LOV  is "surgery.  I can do the same thing in the calculated field.  Then the condition would be calculated field = LOV.

     

    Thank you!  Now my only problem is that I will not be at work for 10 days so I will have to wait until then to try it.

     

    I will let you know how it worked out.

    Thanks.

    Leah

  • 5. Re: multi-choice parameters with "like" condition
    user476771 Newbie
    Currently Being Moderated

    Hi,

     

    I defined an item in the sql select folder which takes the field without the "-".  I see the field in the report and it seems fine.  However, when I define the item class for this calculated item, it does not appear in the parameter that I am defining in the report, i.e. there is no LOV icon next to the parameter.

     

    (I defined the field as mandatory).   What can I be doing wrong?  I have used LOV on calculated items before without a problem.

    Thanks.

    Leah

  • 6. Re: multi-choice parameters with "like" condition
    user476771 Newbie
    Currently Being Moderated

    Thank you!  All is well.   I don't know why I was not seeing the LOV icon, but I refreshed the folder and redefined the parameter and now everything is working.

     

    The condition that I used was checking the parameter value against the calculated field.

     

    Thanks again.

    Leah

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points