subscript a numeric field

[staceyn]

Hello,
I am using Crystal Pro version 10 with an informix datbase.
I am creating a purchase order form for case packs. One of the requirements is to take the first 3 characters of the Purchase Order Number with no padding. So if the po number is 9 we only print 9, otherwise we print the first 3 characters of the PO number. Then we want to concatenate that PO number value with the line number of the Purchase order and have it display as one field.
I can't seem to find how to subscript a numeric field. If I use to text, it adds a comma separator that I don't want to have displayed.

Any ideas on how to accomplish this?
Many thanks in advance.
Stacey

 

[vidru]

The ToText function can take multiple arguments. This will return the first 3 numbers (or less, depending on the number), without any decimal places (the 2nd argument), and no comma separator (the 3rd argument):

Left(ToText({Table.Field}, 0, ""), 3)

If you plan on using the special variable RecordNumber for the line number, or if you're using a variable, you would also need to use the ToText function to be able to concatenate the two:

Left(ToText({Table.Field}, 0, ""), 3) + "-" + ToText(RecordNumber, 0, "")

-dave

 

[staceyn]

Dave,
Thanks for the reponse. That worked great for the first part. The other part I have to figure out is the padding.
So as an example, if the PO number is 9 we print only 9 otherwise, we print the first 3 characters.

thanks for all of your help.
Stacey

 

[lbass]

Are there more than three characters in a PO number? If so, I think you should supply sample PO numbers that are representative of any variation in the field.

-LB

 

[staceyn]

The PO number is a straight numeric field in the database.
For a new customer it can start at 1 and be as many as 10 characters long. We are using an informix database.
Thanks,
Stacey

 

[lbass]

What constitutes the first three characters if the length can vary? Is the following accurate?

PO First 3
1 1
23 23
456 456
7891 789
111213 111

If so, you could use:

if len(totext({table.po},0,"") <= 3 then
totext({table.po},0,"") + totext({table.lineno},0,"") else
left(totext({table.po},0,""),3) + totext({table.lineno},0,"")

-LB

 

[staceyn]

Thanks that worked perfectly!
I appreciate all of your help.