Link to home
Start Free TrialLog in
Avatar of Nicole McDaniels
Nicole McDaniels

asked on

Mask on Parameter CR2008

Is there a way to put a mask on the parameter to force the entry to be uppercase? BUT the string that will be entered is a combination of letters and numbers and no max length.
Avatar of Mike McCracken
Mike McCracken

Not that I am aware of.

You can use UCase or UpperCase in the selection statement or when you want to use/display the parameter

mlmcc
Avatar of Nicole McDaniels

ASKER

It takes too long to run. I was able to enter >a>a >a>a >a>a >a>a >a>a >a>a >a>a in the Edit Mask field and it works. The only problem is it displays it to the user which I wish I could turn off
User generated image
I hadn't seen that option.  To the best of my knowledge it can't be turned off.

mlmcc
FWIW, I don't think that you need to repeat the ">" in the mask.  It applies to the characters that come after it (not just a single character).  So your ">a>a >a>a >a>a >a>a >a>a >a>a >a>a" mask could be written as ">aa aa aa aa aa aa aa".  The effect seems to be the same either way.

 However, I wouldn't expect that ">" to make any difference, as far as performance goes.  If you use UpperCase ({?parameter}) in the record selection formula, CR should convert the parameter value to uppercase before passing it to the server.  At least, I think that's the way it normally works (assuming that the report is reading tables directly, and that you're just using something simple like {field} = UpperCase ({?parameter}) in the record selection formula).

 I'm assuming that the field in the db contains upper and lower case characters and that you're converting that field to uppercase too.  If so, the performance hit is presumably from converting the db field to uppercase.  If that field is indexed, the conversion probably keeps the db from using the index.  If the db has a case-insensitive option (as mentioned in another of your questions), then that would probably be your best solution.  Other than that, I don't know if there's any easy way around the performance issue.

 James
Agree with James.

The UCase if used strictly on the parameter should be done one time with no hit to the performance.

If you are also converting the fields from the database then there will be a hit.

As far as the mask, when added the user will be required to enter the data in uppercase.  Are all values the same length?
The user will be required to enter a string of length 14 for the mask given.  Any shorter string will require the user to reenter to add characters

mlmcc
When I had uppercase({field}=?lotnumber the report just ran and did a whole table scan.
I was actually able to enter just "tlc" and it accepted it and converted it to TLC. The most characters we'd ever have is between 8-10 but I added a few just in case. It says in the help the "a" is alphanumeric and not required so seemed to work.
The goal is to ensure the user enters an uppercase as our DB admin is adding the upper to the database view field. Initially the intent was to allow the report to query for upper and lower case but even with the uppercase({field}=?lotnumber it just ran and ran. So, we're forcing the field in the DB to be uppercase and forcing the user to only be able to enter uppercase
Interesting.

If you use  >aaaaa then 5 characters are required and they are converted to uppercase
If you use >a>a>a>a>a  then you can enter any string of length up to 5.

My suggestion was to use this as the selection formula.  You put the upper case function on the field.

{YourField} = UCase({?Parameter})

mlmcc
I did try that :
 uppercase({BAX_MV_GEMS_LOT_DISPO_RPT.LOT_NUMBER}) like {?Lot Number}

and the report just endlessly runs and never ends.

I also changed the mask to >aaaaaaa.... and it still had no requirement on the number of values. I thought the purpose of the "a" was that it's alphanumeric and not required? See photo..
mask2.png
And this tells me "the array must be subscripted" I need the "Like" to be able to use wildcards
{BAX_MV_GEMS_LOT_DISPO_RPT.LOT_NUMBER} like uppercase({?Lot Number})
DId you set the parameter to allow multiple values?

mlmcc
Yes I need multiple parameters to be allowed
In that case the uppercase idea won't work.

mlmcc
We're talking about 2 different situations with the UpperCase function.

 You said that you used:

uppercase({BAX_MV_GEMS_LOT_DISPO_RPT.LOT_NUMBER}) like {?Lot Number}

 That uses UpperCase on the db field, which probably means that the db can't use the index on that field (if there is one), and that slows down the query.

 mlmcc and I were talking about using UpperCase on the parameter, like:

{BAX_MV_GEMS_LOT_DISPO_RPT.LOT_NUMBER} like UpperCase ({?Lot Number})

 That should not give you any performance hit, because CR would convert the parameter to upper case before it passed it to the db (and the field is used as is, so any index on that field can be used).  For a single value parameter, that would have the same basic effect as using the ">" mask.

 However, if the parameter allows multiple values, then you can't use UpperCase on it.  I don't think you mentioned the multiple values before.

 But there's another question here:

 Is the db field all uppercase, or does it include lowercase letters too?  The fact that you tried to use UpperCase on the db field implies that it includes lowercase letters.  If so, then having the parameter in only uppercase won't match those db values that include lowercase, unless you can set the db to ignore the case.  And if you can do that, then there's no need to convert the parameter to uppercase in the first place, because the db will match the letters either way.


 Also, you mentioned using wildcards, but if you use the "a" mask, the user won't be able to enter wildcards.  The "a" mask seems to be limited to letters and numbers.

 James
I changed it to >CCCCC thank you!!

So the issue is twofold. Initially, we wanted the user to be able to enter in upper and lower case and pull the associated lots regardless of case (I had a previous post on this for help but no luck).

So, if I enter LAV and tox, I want to see all variables (lav, LAv, LaV, TOX, tox, etc). I wasn't getting that. I would only get what I would literally type. So if I entered LAv, I'd only get LAv. (I have the checkmark for case insensitivity checked, also)
In the database, it's free entry so the user can enter all cases even though it SHOULD be uppercase only. So, the workaround was, to add upper to the field in the view thereby forcing all fields that will be queried to be UPPERCASE, AND force the Crystal parameter to only allow UPPER. That way, the view is only upper and the user can only enter upper. If that makes more sense. (We also can't set the DB to ignore case; it's a validated DB (we're pharma) so making the change to affect the whole DB won't fly)
ASKER CERTIFIED SOLUTION
Avatar of James0628
James0628

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Good thought James.

Another way that works is to use a loop
Local numbervar index;
Local booleanvar found;
found := false;

For index := 1 to ubound({?prod}) do
    found := found OR ({Product.Product Name}) like UCase({?prod}[index]);

found

Open in new window


mlmcc
Would a test like that, with a loop, get passed to the server?

 James
Wow, thanks James! I must say now I feel a little silly. I was recreating an existing report but for our new DB and the old report had this formula and I had NO clue what it's doing. Is this similar to what yours is doing? I'm not THIS advanced!
User generated image
James - That test probably wouldn't get passed to the database.  Your solution may since the creation using the split and join could be done before passing to the database.

mlmcc
Nicole-  That formula is concatenating the parameters into a single string separated by carriage returns (chr(13)) and displaying it.

The output of that formula if uppercased could be used to do the compares.

mlmcc
mlmcc already covered part of this.  First of all, no, that formula is not doing what we described (note that it doesn't use the UpperCase/UCase function).  But, as mlmcc said, if you used UpperCase on the result from that formula, then it might be part of the same process.  But it's really doing a lot more than that.  What that formula does is:

 Puts the values from the parameter in an array variable named input.  FWIW, that seems pretty pointless, since you could just as easily reference the parameter values directly (eg. instead of using input [ i ], you could use {?Lot Number(s)} [ i ]).  But it's possible that the values in the input array are altered in some other formula (you can't alter the values in a parameter).

 Copies every value from the input array into a single string (the output variable), separated by carriage returns.  The output variable is used at the end of the formula, so if you put that formula on the report, you would get a list of the values in the parameter, one per line.  Note that the field format would need to have the "Can Grow" option checked, to allow for the variable number of lines (depending on how many values were entered for the parameter).

 Copies every value from the input array, with any leading and trailing spaces removed (the Trim function), into another array named LotArray.  This serves no purpose at all in this formula.  There may be another formula in the report that uses the LotArray variable.


 Also, just a reminder, but if you now feel that mlmcc or I helped you get your question(s) resolved, you might want to cancel/change your pending "close" request.

 James
Other options
Thanks James, I think that worked! One other question, the formula that I shared above, maybe I'm over simplifying it but isn't it just a join with a carriage return? Similar to Join ({?Lot Number},", " ). Just seems like a lot for displaying a parameter.
Yes the join is just joining using a carriage return to put each value on its own line

mlmcc
As I explained in my last post, that's not all that that formula does.  It also copies the parameter values into 2 different array variables, and, in one case, it also removes any leading and trailing spaces from the values when it copies them.  Filling those arrays may be vital, or it may serve no purpose at all.  It depends on whether or not those array variables are used in some other formula in the report, and how they're used.

 FYI, you can also do a Join with a carriage return.  Just use Chr (13) in the Join, instead of ", ".
 
Join ({?Lot Number}, Chr (13))

 So, if those array variables aren't being used somewhere else, you could probably just replace that formula with a Join.

 James
Used formula instead of edit mask which would appear in the parameter prompts
For the record, mlmcc first suggested using UpperCase back in his first post.  It turned out that it wasn't quite that simple, since the parameter allows multiple values, but it's the same idea.

 IAC, I'm glad that we were finally able to get this sorted out.  :-)

 James