Running Balance

[vsimpkins]

Hi there,
I've searched the forum for this but I'm not finding an answer for my situation. I need to have a running balance for customer invoices and payments. However, there's no opening balance.

Here's what I have...
formula: amount
invoiceamt - paymentamt

running total: transamount
sum -> amount, Evaluate every record, Reset every Group #2 (CustomerID)

balance: ??? - this is where I'm stuck

Please help.



Valerie Simpkins

http://www.pacetraining.com

 

[synapsevampire]

You'd be better served to supply technical information than descriptions.

Crystal version
Database/connectivity
Example data
Expected output

If you have an Invoice amount, then that is opening balance, isn't it?

So if you mean that you have something like:

invamount payment
1000 100
1000 200

And you want to show the differences, use the 3 formula method, as in:

Group#2 header formula:
whileprintingrecords;
numbervar Balance:={table.invamount}

Details forula:
whileprintingrecords;
numbervar Balance:=Balance - {table.payment};
Balance

Group #2 footer formula:
whileprintingrecords;
numbervar Balance

I'm guessing at what you want of course, if you need further assistance please post specifics.

-k

 

[vsimpkins]

Using Crystal 9 with Peachtree

Report sorted and grouped by Transaction Date and then my Customer ID

Formula Fields:
BegBal (Placed in Group #2 Header - Customer ID)
whileprintingrecords;
numbervar Balance:={JrnlHdr_1.MainAmount}

RunningBal (Placed in Details)
whileprintingrecords;
numbervar Balance:=Balance - {JrnlHdr.MainAmount};
Balance

EndBal (Placed in Group #2 Footer)
whileprintingrecords;
numbervar Balance


Results...
Customer ID: ABC
Invoice Info Check Info Balance Remaining
Date Ref. # Amount Date Chk# Amount
9/2/2005 9045 $3,600.00 11/1/2005 7541 $12,728.40 -9,128.40
9/2/2005 9047 $60.00 -21,856.80
9/8/2005 9052 $8,852.40 -34,585.20
9/8/2005 9055 $216.00 -47,313.60
-47,313.60

Customer ID: XYZ
Invoice Info Check Info Balance Remaining
Date Ref. # Amount Date Chk# Amount
10/25/2005 9195 $900.28 11/1/2005 91518CR $0.00 900.28
11/1/2005 9151CM $-10.50 900.28
900.28


*Please note, a customer may often send 1 check to cover multiple invoices.



Valerie Simpkins

http://www.pacetraining.com