Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Creating a text file in Crystal Post Reply Post New Topic
Author Message
JMAO
Newbie
Newbie


Joined: 14 Jan 2014
Online Status: Offline
Posts: 9
Quote JMAO Replybullet Topic: Creating a text file in Crystal
     Posted: 24 Jan 2014 at 8:42am
I need to create a text file in Crystal. It needs to be a fixed file length report. What SQL formula is needed to convert fields to appropriate length? Employee number has to start in position 15, and it must be 18 characters long. The value that I have is only 4 characters in length, and I need to pad it with zeros in the front. Thank you!
IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet Posted: 24 Jan 2014 at 10:42am
i did something similar,
formula
space(15) + (
if len(value) = 4 then "00000000000000" + totext(value)
if if len(value) = 5 then "0000000000000" + totext(value)
)

next create a textbox in details section place the formula there
IP IP Logged
JMAO
Newbie
Newbie


Joined: 14 Jan 2014
Online Status: Offline
Posts: 9
Quote JMAO Replybullet Posted: 24 Jan 2014 at 10:54am

Many thanks.  I will try it. 

IP IP Logged
JMAO
Newbie
Newbie


Joined: 14 Jan 2014
Online Status: Offline
Posts: 9
Quote JMAO Replybullet Posted: 27 Jan 2014 at 9:38am

I need to use a formula that will start in the 15th position and have a field that is 15 characters in length and pad with leading zeros as needed depending on the length of theoriginal  field.  I was not specific enough in the previous example and it is truncatng the value. 

If the employee number is 123, the display has to be 000000000000123
If the employee number is 601111199, the display has to be 000000601111199
 
Thank you
 
IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet Posted: 27 Jan 2014 at 9:59am
just add to the formula

space(14) + (
if len(value) = 3 then "000000000000000" + totext(value)
if len(value) = 4 then "00000000000000" + totext(value)
if len(value) = 5 then"0000000000000" + totext(value)
if len(value) = 6 then"000000000000" + totext(value)
if len(value) = 7 then"00000000000" + totext(value)
if len(value) = 8 then"0000000000" + totext(value)
)
IP IP Logged
JMAO
Newbie
Newbie


Joined: 14 Jan 2014
Online Status: Offline
Posts: 9
Quote JMAO Replybullet Posted: 27 Jan 2014 at 10:31am

Appreciate your help.  I'm sure this is something really simple, that I'm missing, but now I'm getting an error message that "a boolean is required".  If I remove the or condition, it tells me I'm missing a parenthesis ")"  By the way I forgot to mention that EMPLOYEE.EMPLOYEE is a numeric field that I'm converting to text.  Thanks.

space(15) + (
if len(Cstr(ToNumber({EMPLOYEE.EMPLOYEE})))=3 then "000000000000" + totext({EMPLOYEE.EMPLOYEE})
OR
if len(Cstr(ToNumber({EMPLOYEE.EMPLOYEE})))=4 then "00000000000" + totext({EMPLOYEE.EMPLOYEE})
OR
if len(Cstr(ToNumber({EMPLOYEE.EMPLOYEE})))=5 then "0000000000" + totext({EMPLOYEE.EMPLOYEE})
OR
if len(Cstr(ToNumber({EMPLOYEE.EMPLOYEE})))=6 then "000000000" + totext({EMPLOYEE.EMPLOYEE})
OR
if len(Cstr(ToNumber({EMPLOYEE.EMPLOYEE})))=7 then "00000000" + totext({EMPLOYEE.EMPLOYEE})

if len(Cstr(ToNumber({EMPLOYEE.EMPLOYEE})))=8 then "0000000" + totext({EMPLOYEE.EMPLOYEE})
OR
if len(Cstr(ToNumber({EMPLOYEE.EMPLOYEE})))=9 then "000000" + totext({EMPLOYEE.EMPLOYEE})
 )

IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet Posted: 27 Jan 2014 at 11:41am
try this
space(14) + (
if len(value) = 3 then "000000000000000" + totext(value) else
if len(value) = 4 then "00000000000000" + totext(value) else
if len(value) = 5 then"0000000000000" + totext(value) else
if len(value) = 6 then"000000000000" + totext(value) else
if len(value) = 7 then"00000000000" + totext(value) else
if len(value) = 8 then"0000000000" + totext(value)
)

or in you formula change or to else



Edited by kostya1122 - 27 Jan 2014 at 11:41am
IP IP Logged
JMAO
Newbie
Newbie


Joined: 14 Jan 2014
Online Status: Offline
Posts: 9
Quote JMAO Replybullet Posted: 28 Jan 2014 at 3:26am

Appreciate your help, once again, but it just isn't working.  I am in Crystal Reports 8, so maybe it is the version that I have that brings the limitation.  The field only prints for some of the records, and then it prints the employee id with a format of commas and decimals.  For now, I will need to export it and reformat in Excel until I get it figured out.  Thanks again!

IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum