In Need of a formula or Solution 2

[mulema]

I am using Crystal 8.5
Hi folks,
I am trying to come up with an easy to use formula to compute my Open Balance field.

My client needs to see the details of Invoice Payment history.

The table below shows the detail transaction of an Invoice# 112234, ( Amt= 30,000) that was paid
on three separate installmments. On each detail line on my report, I want to calculate the Open Balance.

The Invoices are grouped together. (GH1 and GF1 are suppressed. I use the Detail Lines.)

The example table shows how i want the report to look like

First Payment (Inv_Amt - Amt_paid) = Open_balance
Second Payment previous(Open_Balance - Amt_paid) = Open_Balance
Third Payment previous(Open_Balance - Amt_paid) = Open_Balance.

I tried using the Previous() function, but its picking up the previous Balance even when my Invoice# changes. Is there a better approach to calculating my Open_Balances?

Thanks in adavance.

Invoice# Inv_Amt Amt_paid Paid_Date Open_Balance
-------- ------- --------- --------- --------
112234 30,000 12,000 8/4/04 18,000
112234 30,000 10,000 8/6/04 8,000
112234 30,000 8,000 8/7/02 0.0
---
923456 15,000 15,000 8/2/04 0.0

 

[lbass]

Try:

//{@reset} to be placed in the invoice group header:
whileprintingrecords;
numbervar openbal := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar openbal;

if {table.invoiceno} <> previous({table.invoiceno}) then
openbal := {table.inv_amt} - {table.amt_paid} else
openbal := openbal - {table.amt_paid};
openbal;

-LB

 

[bdreed35]

Try creating a formula like this, but modify the fields to use yours:

global currencyvar open_bal;
If onfirstrecord or previous({table.Invoice#}) <> {table.Invoice#} then
    open_bal := {table.Inv_Amt};

open_bal := open_bal - {table.Amt_Paid};

open_bal;

~Brian

 

[lbass]

After reading Brian's (simultaneous) post, I realized my {@accum} formula should have read:

whileprintingrecords;
numbervar openbal;

if onfirstrecord or
{table.invoiceno} <> previous({table.invoiceno}) then
openbal := {table.inv_amt} - {table.amt_paid} else
openbal := openbal - {table.amt_paid};
openbal;

-LB

 

[bdreed35]

LB -

I think you need to check for the first record as the previous function won't work properly when there is no previous value. Of course we know that it should work that way, but it wouldn't be the first time one of these functions defy logic.

I believe your Accum formula will work properly like this:

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar openbal;

if onfirstrecord or {table.invoiceno} <> previous({table.invoiceno}) then
openbal := {table.inv_amt} - {table.amt_paid} else
openbal := openbal - {table.amt_paid};

You even have to check "onfirstrecord" first, otherwise it won't evaluate to True.
openbal;

~Brian

 

[bdreed35]

Sorry LB, I was in the middle of my post while you were correcting yourself.

I guess we posted simultaneous(twice)!

~Brian