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?
3 Answers
-
Former MemberPosted on Jan 09, 2008 at 04:17 PMPost 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....
Add a comment
-
Former MemberPosted on Jan 09, 2008 at 05:30 PM
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.
Add a comment
-
Former MemberPosted on Jan 11, 2008 at 04:29 PM
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.
Add a comment
Add a comment