Converting a number field to print in specified order 1

[lindagoodwin]

I'm using Crystal 10. I have a number field in the{VISIT.MRUN_VISIT} that prints like this:

35062.00
I need it to print like this
003-50-62

I know it's a formula but I am a beginner in working with Crystal and can not figure it out.
Thanks

 

[CR85user]

Try the following formula:

//Formatnum
picture(
(right(totext({VISIT.MRUN_VISIT},'.00'),2) +
left(totext({VISIT.MRUN_VISIT},'000'),3) +
mid(totext({VISIT.MRUN_VISIT},'00'),4,2))
,'xxx-xx-xx')


I'm sure there is a much more elgant way to do this and can only base the proposed formula on the single example you gave (More info / examples would assist in future).

'J

 

[lindagoodwin]

Thanks so much. That worked perfectly.
This was my first posting, I'm learning everyday.
Thanks again.

 

[CR85user]

I just realised that the formula could be a little more simplified using:

picture(
(right(totext({@123456},'.00'),2) +
left(totext({@123456},'00000'),5))
,'xxx-xx-xx')

Welcome to the forums always a pleasure to see a new face.

'J

 

[lbass]

Or, just:

picture(totext({table.number},"0000000"),"xxx-xx-xx")

-LB

 

[CR85user]

The ordering of the numbers is changed though LB - 1st two digits are the remainder after the decimal point followed then by the 5 1st digits.

I still am not happy with the way in which the solution I posted handles formulae with any value after the decimal greater than .00

Only after recreating and testing the formula locally with different input have I seen issues.

I'll try a couple of solutions and repost if I can offer a better solution.

'J

 

[lbass]

I don't believe that was what the OP intended. A number displays with two decimals by default. My interpretation was that the OP wanted to convert the whole number into a 7-digit display.

-LB

 

[lindagoodwin]

lbass is correct. I did want the whole number into a 7-digit display. the formula you provided worked like a charm.

picture(totext({table.number},"0000000"),"xxx-xx-xx")
I am very new at using Crystal, this is the first time I've seen picture in front of the formula. What does that do? What ever it does it worked.

Thanks so much.

 

[CR85user]

Picture essentialy displays a string in the format you wish
for example picture('banana','xx xx xx') would display:
ba na na

Format is:
Picture(INPUTFIELD,LAYOUT)

Apologies for any misinterpretation of what you were looking for.

The totext converts the number to a string and the ,'0000000' is to state that it is whole 7 digit number required.

'J