I have a pledge reminder that has installments. I want to display the schedule of installments along with the amount paid beside each installment. How do I keep the running total to manipulate each installment that was paid. Final result will look like this:
Inst Date Inst Amt Amt Paid
6/30/2004 10.00 10.00
6/30/2005 15.00 15.00
6/30/2006 25.00 0.00
6/30/2007 35.00 0.00
6/30/2008 45.00 0.00

So the total paid was 25 which I have a field for. I have a field for the installment amount also. I just have to calculate the amt paid for each installment based on how much was already paid

I might be missing something since this seems pretty straightforward. Are you just wanting a running total of amount paid? Something like this:

TABLE FILE CAR
PRINT
CAR
RCOST
COMPUTE RUN_TOT=RUN_TOT + RCOST;
BY COUNTRY
END

Say a person has equal installments of 1000 each for 5 years. They are past due for the past 3 years, but has only paid 1500 total. The report will show the 5 installments, with the amount of each installment that was paid beside each, when it gets to the second installment it would say 500 beside it instead of 1000. And the rest would say 0.

Date INST AMT AMT PAID
6/6/03 1000 1000
6/6/04 1000 500
6/6/05 1000 0
6/6/06 1000 0
6/6/07 1000 0

Amt Due Now is $2500
I just want to know how to display the amt paid since its not a real field in my database.

And I know to display that total correctly I would have to compare the installment with the total amount paid, then decrement the amount paid by the installment if the installment is less than the amt paid. But when I go to the next installment, I get confused on how to hold that decremented amt paid value. So by the time I get to 0 for the amt paid I would know to display zeros for the installments left.

the question is for me a bit confusing, but would you be able to do this in excel?
can you work that out and display the results here?
that might be more desciptive.
Maybe you need FML here to get this done??

Frank

It's just a matter of figuring out the logic. This example should give you what you need. The amt_pd and inst_due columns are what you're going to display. The others are just so you can see how the logic is working.

DEFINE FILE CAR
DCOST1/D15.2C=5000;
END
TABLE FILE CAR
PRINT
CAR
RCOST AS INSTALLMENT
DCOST1 AS PAID
COMPUTE TOT_DUE_RUN/D15.2C=TOT_DUE_RUN + RCOST;
COMPUTE TOT_PD_RUN/D15.2C=TOT_PD_RUN + DCOST1;
COMPUTE AMT_PD/D15.2C=IF TOT.DCOST1 GT TOT_DUE_RUN THEN RCOST ELSE
IF TOT_DUE_RUN - TOT.DCOST1 LT RCOST THEN TOT_DUE_RUN - TOT.DCOST1 ELSE 0;
COMPUTE INST_DUE=RCOST - AMT_PD;
COMPUTE TOT_UNPD_RUN/D15.2C=TOT_UNPD_RUN + INST_DUE;
BY COUNTRY
END

 
Inst Date Inst Amt Amt Paid

6/30/2004 10.00    10.00
6/30/2005 15.00    15.00
6/30/2006 25.00     0.00
6/30/2007 35.00     0.00
6/30/2008 45.00     0.00 

I'm still trying to understand what it is that you have.

For each date indicated do you have the pledge amount and the (running) total paid to that date? Then, you calculate the amount paid at the date of each pledged installment?

 
Inst Date Inst Amt Total Paid Amt Paid

6/30/2004 10.00    10.00      10.00
6/30/2005 15.00    25.00      15.00
6/30/2006 25.00    25.00       0.00
6/30/2007 35.00    25.00       0.00
6/30/2008 45.00    25.00       0.00 

I'm assuming you have the first three fields in your db and are calculating the last one. Is that correct? What does your master file look like?

To calculate the last column you'd need to consider the sorting and have something like:

COMPUTE INST_TOT/F8.2 = IF LAST Pledge_ID EQ Pledge_ID THEN INST_TOT + Inst_amount
ELSE Inst_amount;
COMPUTE Amount_Paid/F8.2 = IF LAST Pledge_ID EQ Pledge_ID THEN
(IF (INST_TOT - Total_Paid) LT 0 THEN 0 ELSE INST_TOT - Total_Paid) ELSE
Inst_amount - Total_Paid;


BY Pledge_ID
BY Inst_date


I hope that makes sense.

I am just getting back from Vegas (Banner Summit) . I will try these solutions this weekend if I get a chance. But yes, I am trying to calculate the last column. Just by looking at the code and the extra column you added, it makes sense....can't wait to try...stay tuned....

Tracie,

Here is another contribution. I created a MASTER and some data to represent your problem:

MASTER
FILENAME=TRACIE, SUFFIX=FIX,
  DATASET=C:\IBI\APPS\FOCALPOINT\TRACIE.DAT
 SEGNAME=CUST, SEGTYPE=S0
  FIELDNAME=CUSTNO, FORMAT=I4, ACTUAL=A4, $
  FIELDNAME=PAID_AMT, FORMAT=D9.2, ACTUAL=A10, $
 SEGNAME=INST, PARENT=CUST, OCCURS=VARIABLE
  FIELDNAME=IDATE, FORMAT=MDYY, ACTUAL=A10, $
  FIELDNAME=I_AMT, FORMAT=D6.2, ACTUAL=A6, $
  FIELDNAME=I_NUM, ALIAS=ORDER, FORMAT=I3, ACTUAL=I4, $

DATA
1234 000025.00 6/30/2004 10.00 6/30/2005 15.00 6/30/2006 25.00 6/30/2007 35.00 6/30/2008 45.00 
1235 000040.00 6/30/2005 15.00 6/30/2006 25.00 6/30/2007 35.00 
1236 000075.00 6/30/2003 10.00 6/30/2004 10.00 6/30/2005 15.00 6/30/2006 25.00 6/30/2007 35.00 6/30/2008 45.00 

The program follows:

TABLE FILE TRACIE
PRINT 
I_AMT PAID_AMT NOPRINT 
COMPUTE T_AMT/D9.2=IF CUSTNO EQ LAST CUSTNO THEN LAST T_AMT + I_AMT ELSE I_AMT; NOPRINT
COMPUTE D_AMT/D9.2=PAID_AMT - T_AMT; NOPRINT
COMPUTE R_AMT/D9.2=IF D_AMT GE 0 THEN I_AMT ELSE IF LAST D_AMT GT 0 THEN LAST D_AMT ELSE 0; 
BY CUSTNO SUBTOTAL 
BY PAID_AMT
BY IDATE
ON TABLE NOTOTAL
END

Good luck!

Thanks Danny! I ended up using yours but I had to add an if statement to the last compute because it didn't account for a partial payment of the 1st installment. So if someone paid 1500 on a 3000 installment and that is all they paid, the first amt paid is 1500, yours was giving me 0. The rest worked perfectly! Thanks so much!

Tracie,
You are most welcome.