Skip to Content
0
Apr 30, 2012 at 04:34 PM

Selecting records from multiselect parameter with Discrete and Range

19 Views

I have a report that works completely fine for discrete parameters. However, when the user uses a range, it is having a problem. I know the problem and why, I just don't know the solution for a proper selection formula.

Crystal 8.5 and MySQL.

I am trying to select different PO numbers. I have no control over the structure of the database. The PO number field is string.

As an example, the client wants to select PO #'s 450-460 but while it returns PO 456 and 458, it also returns 4570. I am assuming this is due to the matching alphanumerically instead of by number. I have tried to turn the selection into a number array but I am having problems with the range itself.

I have code that will select each individual element of the parameter so I can print the selected elements of the parameter but I cannot figure out how to get the record selection forumla to work based on the numbers.

I do not know if they ever use alpha characters in the PO number field but I will not rule it out at this point.

My latest attempt is:

(Local StringVar sPrompt;
Local NumberVar x;
Local StringVar sPrefix := "000000000000000";

sPrompt := "";
For x := 1 to Count({?BonDeCommand}) Step 1
Do
(sPrompt := "" ;
Iif(Minimum( {?BonDeCommand} [x] ) = Maximum( {?BonDeCommand} [x] ) ,
({Inv.Type} in [1, 2]
and {Inv.Rev} = 0
and Right (sPrefix & {Inv.PO}, 15) = Right (sPrefix & ToText(Minimum( {?BonDeCommand} [x])), 15)), //discrete item

({Inv.Type} in [1, 2]
and {Inv.Rev} = 0
and (Right (sPrefix & {Inv.PO}, 15) >= Right (sPrefix & ToText (Minimum ( {?BonDeCommand} [x] ) ), 15) //range
and Right (sPrefix & {Inv.PO}, 15) <= Right (sPrefix & ToText (Maximum( {?BonDeCommand} [x] )), 15))))));

Right now that just selects everything, way more than my sample data indicates should be returned. The fields Type and Rev are just other parts to the selection that on their own, work fine. What I am attempting above is to take each element and if it is discrete add it to the selection but if it is a range, then using greater than and less than etc. I am cycling through each element. However, I am assuming Crystal will only send the command to the database once and when I tried to put the final selection into a string array, the program ignored it as it does with the above code. I've attempted to use the prefix to standardize the length of the string which works great as a tool for sorting numeric strings but doesn't seem to work here.

So I am hoping someone else has seen this kind of thing before and has an idea of what to do to correctly account for it. Any help is appreciated.