Running balance for customer statements

[leftfldr]

Using Crystal Reports 10 and SQL2008 DB (Dynamics GP backend accounting software). I am trying to recreate customer statements that are produced within Dynamics GP.
I want it to look similar to this:

Document # Code Date Debit Credit Balance
517020 Sale 12/12/08 1900.00 1900.00
000001 Payment 1/1/09 196.21 1703.79
000002 Payment 2/1/09 350.00 1353.79
517021 Sale 3/15/09 750.00 2103.79

Table RM20101 contains all the open receivable transactions and then RM20201 contains the applied payment information to the transaction in RM20101.

In the above example document 517020 and 517021 are in RM20101 as open transactions and 000001 and 000002 are in RM20201 as applied payments against 517020.

I am grouping on Rm20101 document number field and then the detail section is the applied payments. The problem is the balance field. How can I calculate the balance as a running total since the sales portion is in the header and the applied payment information is in the detail and then it moves back to the header for the next document number?

Could I arrange this a different way to accomplish it easier?
Thanks!

 

[lbass]

The following assumes a group #1 on Customer and a group #2 on doc#. Create a formula like this for the detail section:

whileprintingrecords;
numbervar bal;
numbervar db;
numbervar cr := cr + {table.credit};
if onfirstrecord or
{table.doc#} <> previous({table.doc#}) then
db := db + maximum({table.debit},{table.doc#});
bal := db - cr;

Add a reset formula per customer group:
whileprintingrecords;
numbervar bal;
numbervar db;
numbervar cr;
if not inrepeatedgroupheader then (
bal := 0;
db := 0;
cr := 0
);

If you want a final balance in the customer group footer, then use a formula like this:
whileprintingrecords;
numbervar bal;

-LB

 

[leftfldr]

Thanks for the reply. OK. I tried your suggestion. The problem is that the sale document numbers are in the GH2 and the applied payments are in the details. So if I place the formula you suggested in the details it does not take those into consideration. Here is what the balance looks like using the formula:
Document # Code Date Debit Credit Balance
GH2 17020 Sale 12/12/08 1900.00 1900.00
D 000001 Payment 1/1/09 196.21 1900.00
D 000002 Payment 2/1/09 350.00 1900.00
GH2 517021 Sale 3/15/09 750.00 2650.00

Suggestions? Thanks!

 

[lbass]

Please place both debit and credit fields in the detail section and then report back with how they display.

-LB

 

[leftfldr]

OK. Well the formlua works great in teh details now, but then when it has to move back to the next GH2 I get stuck. I think the problem might be because the dr and cr amount formulas for the GH2 have to be different than the dr and cr formulas on the details section. The details section pulls the apply to amounts and the GH2 pulls the original transaction amount.

In GH2 I have:
Document @debit @credit @balance

In Details I have:
apply doc number @applyamtdr @applyamtcr @runningbalance
with @runningbalance being your formula

Example: Here is what it looks like now:

Document # Code Date Debit Credit Balance
GH2 17020 Sale 12/12/08 1900.00 1900.00
D 000001 Payment 1/1/09 196.21 1703.79
D 000002 Payment 2/1/09 350.00 1353.79
GH2 517021 Sale 3/15/09 750.00 750.00

Notice the last line moves back to 750.00 instead of 2103.79.

 

[lbass]

I was asking you because I need to see what happens to the fields if placed in the detail section.

Please also show the content of all of your formulas--I thought you were working directly with fields.

-LB