Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: report failing after first 2 records Post Reply Post New Topic
Author Message
bwsanders
Senior Member
Senior Member


Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
Quote bwsanders Replybullet Topic: report failing after first 2 records
     Posted: 27 Dec 2012 at 4:45am
I'm not sure why and I'm not seeing anything obvious. When I run this report it stops after returning only the first 2 fields. However, if I block everything out and test each record by itself they work.

Any help or suggestions would be great.

Here is a copy of my code in the report. It is just exporting out to a .txt file so the client can use it with quickbooks.

WhilePrintingRecords;

Shared StringVar OutputFile;

Stringvar sOutput := "";

sOutput :=

//1 Period End Date mm/dd/yyyy
Totext({EPayHist.endDate}, "MM/dd/yyyy") + "," +

//2 Check Date mm/dd/yyyy
Totext({EPayHist.checkDate}, "MM/dd/yyyy") + "," +

//3 Company Code "086B"
REPLACE({EInfo.co}, "CAMD", "086B") + "," +

//4 Employee ID 9 9igits zero fill
{EPayHist.id} + "," +

//5 Employee Name "Lastname, Firstname"
{EInfo.lastName} & "," & {EInfo.firstName} + "," +

//6 BLANK
"" + "," +

//7 BLANK
"" + "," +

//8 Department 6 digits zero fill
{ELaborDist.cc1} + "," +

//9 Job Code 6 digits zero fill
{ELaborDist.jobCode} + "," +

//10 Class no format
{CDept1.cc1} + "," +

//11 Blank
"" + "," +

//12 Payroll Code no format
{ELaborDist.detCode} + "," +

//13 Amount no format
Totext({ELaborDist.amount}) + "," +

//14 Hours no format
Totext({ELaborDist.hours}) + "," +

//15 Check Number 7 digits zero fill
Totext({EPayHist.checkNumber}) + "," +

//16 Voucher Number 7 digits zero fill
Totext({EPayHist.voucherNumber}) + "," +

"";

// Carriage Return / Line Feed
sOutput := sOutput + Chr(13) + Chr(10);

// Write line to file
mioMPAYIOAppendToFile (OutputFile, sOutput);

sOutput;
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 28 Dec 2012 at 3:24am
I wouldn't do this all in a single formula.  Instead,  I would do the following:
1.  Add a text object to the report - probably in the details section - and make it wide enough for all of the data.
2.  Drag the first field INTO the text box.
3.  Select the field in the text box, right-click and format, if necessary. 
4.  Place the cursor after the field and type in a comma. 
5.  Repeat steps 2-4 until all of the fields are in the text box and properly formatted. 
6.  Export the report to a text file.
 
-Dell


Edited by hilfy - 28 Dec 2012 at 3:24am
IP IP Logged
bwsanders
Senior Member
Senior Member


Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
Quote bwsanders Replybullet Posted: 28 Dec 2012 at 3:34am
I have found that is is failing due to 'NULL' records. I changed the report options and unticked the change to default for NULL records. however it is still failing to return based on the final 2 records now. which are the check number or voucher number.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 28 Dec 2012 at 3:55am
Take a look at the steps that I outlined - this will automatically handle null values without you having to code for them.
 
-Dell
IP IP Logged
bwsanders
Senior Member
Senior Member


Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
Quote bwsanders Replybullet Posted: 28 Dec 2012 at 4:01am
if i am able to follow the steps that you have outlined will i be able to format the records like zero filling and concatenate records?  
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 28 Dec 2012 at 4:09am
Yes - you can create a formula for each field that will do the zero filling or concatenation.  You then place the formulas in the text box instead of the fields.
 
When using fields in formulas, you will probably need to check for null values (by using "If IsNull({field}) then... else <format field>"  in your formula) and take appropriate action when the value is null.  You don't have to do the null check if you're just using fields in the text box.
 
-Dell
IP IP Logged
bwsanders
Senior Member
Senior Member


Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
Quote bwsanders Replybullet Posted: 28 Dec 2012 at 4:29am
hmmm, this sounds like it may be a bit easier. off hand do you happen to know how to zero fill a field?

like if a field returns "59" but the client wants the field to be zero filled to 9 spaces returning "000000059". i was trying to figure out how to do:

if len({field}) > 9 then "0000000" & {field} else {field}

that wasn't working out.

thank you for your help!!
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