How to display the 3rd Bill Date?

[SaraJB]

Hi

I'm using Crystal 8.5 and I need to create a formula to show the 3rd bill date from the accounts table which contains both credit and debit fields. The report needs to display a formula, ThirdPaidBill, which will be the date when the 3rd payment (credit) was made assuming all 3 bills have been made.

Here's an example of the neat data.

CustomerID Credit Debit Date InvNo
1000000020 NULL 20.00 01/05/2002 50123
1000000020 10.00 NULL 03/05/2002 50123
1000000976 NULL 43.00 04/05/2002 50144
1000000020 10.00 NULL 06/05/2002 50123
1000000020 NULL 20.00 08/05/2002 50161
1000000020 20.00 NULL 14/05/2002 50161
1000000020 NULL 20.00 15/05/2002 50172
1000000976 43.00 NULL 04/05/2002 50144
1000000020 20.00 NULL 16/05/2002 50172

I am attempting to generate a report and would like to show the following for customer 1000000020.

CustomerID 1stCreditDate 2ndCreditDate 3rdCreditDate
1000000020 03/05/2002 06/05/2002 14/05/2002

The following shows the formula I am using to try and get the FirstCreditDate

WhilePrintingrecords;
numbervar FirstCreditDateCount;
If not isnull({Accounts.Credit}) then
FirstCreditDateCount:=FirstCreditDateCount+1;
If FirstCreditDateCount = 1 Then
{Accounts.Date}

I can't seem to modify for the 2nd and 3rd Bill dates.
What is wrong? Is there an easier solution?

Thanks in advance
Sara

 

[HowardHammerman]

I assume that you are already grouping by CustomerID number.
I also assume that you sort by transaction date.

I would do the following:

Create a formula called 'Reset' with the following expression:

whileprintingrecords;
shared datevar firstcredit := date(1950,1,1);
shared datevar secondcredit := date(1950,1,1);
shared datevar thridcredit := date(1950,1,1);
shared numbervar creditkount := 0;

Place this in the group header for the customer.

Create a second formula, called "accum" with the following expression:

whileprintingrecords;
shared datevar firstcredit ;
shared datevar secondcredit ;
shared datevar thridcredit ;
shared numbervar creditkount;

if {acounts.credit} > 0 then creditkount := creditkount +1;
if creditkount =1 then firstcredit := {account.date};
if {acounts.credit} > 0 then creditkount := creditkount +1;
if creditkount =2 then secondcredit := {account.date};
if {acounts.credit} > 0 then creditkount := creditkount +1;
if creditkount =3 then thirdcredit := {account.date};

place this in the detail section.

Create three more formula, one for each date with the following expression:

whileprintingrecords;
shared datevar firstcredit;
firstcredit

whileprintingrecords;
shared datevar secondcredit;
secondcredit

whileprintingrecords;
shared datevar thirdcredit;
thirdcredit

Place these as needed in the group footer section.




Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[Ngolem]

Howard;
I like your approach .... but this formula won't work

whileprintingrecords;
shared datevar firstcredit ;
shared datevar secondcredit ;
shared datevar thridcredit ;
shared numbervar creditkount;

if {acounts.credit} > 0 then creditkount := creditkount +1;
if creditkount =1 then firstcredit := {account.date};
if {acounts.credit} > 0 then creditkount := creditkount +1;
if creditkount =2 then secondcredit := {account.date};
if {acounts.credit} > 0 then creditkount := creditkount +1;
if creditkount =3 then thirdcredit := {account.date};

The reason is because:
1. you don't kick out of the formula after the first
assignment...so the first record date will be assigned
3 times as it works its way throught the formula.
2. Nowhere in your formula do you check for a null value so
the formula will crash where it hits the debits.

This formula should be written

whileprintingrecords;
shared datevar firstcredit ;
shared datevar secondcredit ;
shared datevar thridcredit ;
shared numbervar creditkount;

if not isnull({accounts.credit} then
(
creditkount := creditkount +1;
if creditkount = 1 then
firstcredit := {account.date};
else if creditkount = 2 then
secondcredit := {account.date};
else
thirdcredit := {account.date};
);

This formula is placed in the Details section as you suggested....but suppressed
ALSO I recommend that the report be grouped by CustomerID and then by {account.date} this makes the maintenance easier months later (I always forget to check out the "sorts" ) The Date grouping would be suppressed in the header and footer.
Jim Broadbent

 

[Naith]

Nice.

But I'm not sure about the "else thirdcredit" catchment.

Sara described a 3 credit scenario, but the data excerpt illustrates 4 separate credits for 1000000020. The 3 credit's aren't confined to Invoice numbers or anything, so it's not clear why the 4th and/or subsequent credits are ignored.

I agree with Ngolem's points about checking nulls rather than 0s and multiple assignments, but you might want to base the 3rd (and 4th) credits on the creditkount variable equalling a specific value, rather than just catching the remainder, and using that.

Naith

 

[SaraJB]

Thanks for your help, I will try all the suggestions.

There's no reason why I've ignored the 4th/5th credit dates, the only reason being that it wasn't required for the report only the first 3 bill dates.

Sara

 

[Naith]

Fair enough. Just try to remember that 'else third...' will give you the latest date, regardless of if it's the third credit date or not.

Naith

 

[Ngolem]

point well taken Naith Jim Broadbent

 

[SaraJB]

It works! Thanks for all your help.

Sara