formatting multiple value parameter for record selection

[amcgreal]

Hi All,

Using XI, I am trying to do record selection using a dynamic cascading prompt that allows multiple values. The problem (I think?) is that the database field is right justified. Some of the database values are 7 long with 5 preceeding spaces , some are 8 with 4 preceeding spaces. The field is always 12 long. The problem is that even when giving the user the list of values, it left justifies it. So, I'm assuming that Crystal is seeing it as "1234567" and not " 1234567". This prevents the data from coming in. Here is the record selection that works when the user entered the job number with the preceeding zeros. It currently works but the users want to pull more than one job at a time.

Current record selection:
{F0006.MCMCU} = {?job number}

I need to ltrim the F0006.MCMCU somehow or pad the {?job number} which is a multi value parm. I just don't know how to format the multi value (array) parameter.

Any help or suggestions are very much appreciated!

 

[synapsevampire]

Please remember to at least post your database type and connectivity.

Try changing the parameter to a numeric and try this:

val({F0006.MCMCU}) = {?job number}

A SQL Expression would be more likely to pass the SQL though.

If the field is padded with zeros, which you stated later in your post, contrary to the earlier example and statements, then we'll need another approach, but I'm guessing that you meant spaces.

-k

 

[lbass]

Since this is dynamic, I don't think you have the ability to change the display. You could try using a record selection formula of:

{F0006.MCMCU} = totext(val({?job number}),"000000000000")

Or you could trim the field, maybe using a SQL expression:

{fn ltrim(F0006.`MCMCU`)}

...and then set that equal to {?job number}.

-LB

 

[amcgreal]

Sorry, forgot the database.....SQL Server 2000. The database field is actually string though it represents a number to our users. Sorry if I misled you.

I tried doing an ltrim but it didn't work?
This is the ltrim that I tried in record selection but it didn't work:
ltrim({F0006.MCMCU}) in {?job number}


will using the {fn.......} make a difference? I'm not sure what the curly brackets and fn mean? I'm kinds of new to Crystal.

Thanks so much.

 

[lbass]

Try:

trim({F0006.MCMCU}) = {?job number}

My other suggestion referred to creating a SQL expression {%MCMCU} to replace the field. Go to the field explorer->SQL expression->new and enter:

{fn ltrim(F0006.`MCMCU`)}

Functions and punctuation vary by datasource, therefore this might not be exact. Then use the expression in the record selection formula:

{%MCMCU} = {?job number}

-LB

 

[synapsevampire]

in and = should be the same here.

I'm curious why you ignored my solution, seems like the obvious one.

You can create a SQL Expression and CAST the data to a numeric, or do as my original formula suggested.

-k

 

[amcgreal]

Thanks to both of you for the help. I'll try both and see what happens!

K - Sorry if you thought I ignored your post.....I didn't mean to. I was just trying to clarify and make sure I understood both. I totally appreciate the help!

Thanks again to both of you-
I'll try things out and let you know what happens.

Cheers!