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?