Skip to Content
author's profile photo Former Member
Former Member

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

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?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 09, 2008 at 04:17 PM

    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....

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted 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
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted 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
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.