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

32 Views

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?