Selecting records from multiselect parameter with Discrete and Range

[Former Member]

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.

[former_member11696 · Accepted solution]

Hello,

The problem is if you make the Selection formula to complex then CR can't convert it into valid SQL and therefore as you have seen Cr returns all records and filters client side.

Another option would be to use your original filter, "PO 456 and 458, it also returns 4570." and then filter that to remove the values outside of the range. At least if does most of the filtering Server Side, maybe.

Another option is to use a stored Procedure and convert them to a number so filtering can be done by value rather than strings. C++ rules for strings, so your ordering isn't going to work either.

Be aware also, because CR is built with C++ there is a 15 digit plus one for the sign of the number limit and no way around it other than converting to a string or breaking it into multiple fields.

Don

[Former Member · Accepted solution]

Thanks Don.

Now I know why I've always used 15 when converting to strings for comparison.

So the basic idea I had of going through each of the elements of the parameter needs to be expanded for client-side filtering.  Then do some boolean comparison with the real PO number in the conditional suppression formula.

I do not have access to the database to do stored procedure but I believe I have worked out some programming on the client-side conditional suppression now.

I had hoped to do something at the select level but because there will not be more than 100 or so records returned and I am not doing summary totals on this report, client-side conditional suppression is easy enough to handle.

Thanks for the pointers.