Help Applying a formula to the last payment in a clients recordset.

[Former Member]

Post Author: Bpilot

CA Forum: Formula

Can someone help me write a formula that would allow me to apply the following formula to the LAST payment a client makes.The formula is initially designed to look at a customers payment against their amount owing. If their payment is greater then their balance the report only shows the amount necessary to pay off the customers account. This works fine if the report only shows 1 payment for the client in the report, as soon as the client has 2+ payments and the last one overpays then all payments show what is required to pay off the account. Not ideal as you can understand.I am thinking the best way to do this is with a For loop but I cannot write one and I was hoping someone could help me out with this. The code is below, the logic on the fields is below, I am just at a loss and will provide any and all help I can towards getting help.

My original fomula is this:

If {@Display Selection} = "Display" then

if {AFACCOUNT.AFACCURBALINT} >= 0 then

{AFSPLIT.AFSPAMT}*(-1)

else

({AFSPLIT.AFSPAMT}-{AFACCOUNT.AFACCURBALINT})*(-1)

else

0.00

-

---

{@Display Selection} is a formula that evaluates each financial

transaction to determine if it should be Displayed on the report or

Not-Displayed based on date ranges and transaction type. This works

fine.

{AFACCOUNT.AFACCURBALINT} is where we store the accounts current

balance with interest. Despute appearing in our system as a positive

number, it is really a negative number (it is money owed to us)

{AFSPLIT.AFSPAMT} is where we store the payment amount. It appears in a

negative value to subtract from the {AFACCOUNT.AFACCURBALINT} but is in

fact a positive number.

{ARACCONT.ARACID} is the unique customer identifier.

{AFTRANSACTION.TRKEY} is the unique transaction ID (each payment for {AFSPLIT.AFSPAMT} has a unique TRKEY)

-

---

What I am thinking the best way to do this, if its possible is this.

A For loop that counts the number of times the {ARACCONT.ARACID} appears in the crystal report.

It then selects the last payment which will also have the highest

{AFTRANSACTION.TRKEY} . I don't know if you can just say to select the

last payment or if you would need something to relate to, which could

be the {AFTRANSACTION.TRKEY}.

Once that is selected then it would apply the following formula.

If {@Display Selection} = "Display" then

if {AFACCOUNT.AFACCURBALINT} >= 0 then

{AFSPLIT.AFSPAMT}*(-1)

else

({AFSPLIT.AFSPAMT}-{AFACCOUNT.AFACCURBALINT})*(-1)

else

0.00

all else transactions would display the following:

If {@Display Selection} = "Display" then

{AFSPLIT.AFSPAMT}*(-1)

else

0.00

-

---

Can someone help me write this report? It would be greatly appreciated and help me save what little hair I have left?

[Former Member]

Post Author: Bpilot

CA Forum: Formula

I resolved this issue, but not as suggested.To do so, I created three additional formula's to monitor certain aspects of the payments and accounts. Once one of those reached the specified criteria, then the payment (@positive) would apply the appropriate formula. I would still like to know how to do this another way as using 5 formula's to do 1 thing is messy.

[Former Member]

Post Author: Bpilot

CA Forum: Formula

It does. {AFTRANSACTION.AFTREFFDTE} is the effective date of the payment.I have included a screen shot.The report has to keep its same display format. Accounts are grouped by the date they were listed in our system, then sorted their TREFFDTE .@positive is where the formula needs to go, or eventually will reference from.There is a running total in the footer of each group that totals @positive.

[Former Member]

Post Author: V361

CA Forum: Formula

if your field {AFTRANSACTION.TRKEY} has a date associated with it, you could just select the {AFTRANSACTION.TRKEY} by maximum date (The latest payment), not sure if this was what you were looking for or not. If not, Please post some sample data....