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.
ASKER
I hadn't seen that option. To the best of my knowledge it can't be turned off.
mlmcc
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
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
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
ASKER
When I had uppercase({field}=?lotnumb er 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}=?lotnumb er 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
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}=?lotnumb
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
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
ASKER
I did try that :
uppercase({BAX_MV_GEMS_LOT _DISPO_RPT .LOT_NUMBE R}) 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
uppercase({BAX_MV_GEMS_LOT
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
ASKER
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_NUMBE R} like uppercase({?Lot Number})
{BAX_MV_GEMS_LOT_DISPO_RPT
DId you set the parameter to allow multiple values?
mlmcc
mlmcc
ASKER
Yes I need multiple parameters to be allowed
In that case the uppercase idea won't work.
mlmcc
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_NUMBE R}) 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_NUMBE R} 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
You said that you used:
uppercase({BAX_MV_GEMS_LOT
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
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
ASKER
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Good thought James.
Another way that works is to use a loop
mlmcc
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
mlmcc
Would a test like that, with a loop, get passed to the server?
James
James
ASKER
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
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
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
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
ASKER
Other options
ASKER
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
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
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
ASKER
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
IAC, I'm glad that we were finally able to get this sorted out. :-)
James
You can use UCase or UpperCase in the selection statement or when you want to use/display the parameter
mlmcc