Selecting the last record

[bpilot]

How would I write a formula that would only select the last financial transaction a client made.

I have access to a number of unique identifiers I was looking at using.

I would think the best way to do this would be to identify all transactions made by the ClientID (these will not be sequential) and then based off that, select the highest transaction key to apply my formula to.

I cannot make this happen, could someone more experienced help me out?

[jggtz]

An easy way could be to define a ClientID group and work at group footer section supressing detail section. If you put fields at group footer section they will be from the last record read. Also you can use the Sort expert to arrange the records to a proper way.

[JaganEllis]

Or use the group expert. Group by client, group selection formula is then
{table.tran_id} = maximum({table.tran_id}, {table.client_id})

[bpilot]

I do use groups already, but the formula is being applied at the details line.
I am thinking the best way to do this is with a For loop but I cannot write one and I was hoping people could help me.

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?

[JaganEllis]

Use this in the details formula then
if {table.tran_id} = maximum({table.tran_id}, {table.client_id}) then
...
else
...

[bpilot]

Thanks I tried doing what you said and it looks like this.

if {AFTRANSACTION.AFTRKEY} = maximum({AFTRANSACTION.AFTRKEY}, {ARACCOUNT.ARACID}) then
If {@Display Selection} = "Display" then
if {AFACCOUNT.AFACCURBALINT} >= 0 then
{AFSPLIT.AFSPAMT}*(-1)
else
({AFSPLIT.AFSPAMT}-{AFACCOUNT.AFACCURBALINT})*(-1)
else
0.00
else
If {@Display Selection} = "Display" then
{AFSPLIT.AFSPAMT}*(-1)

when I try to save it, Crystal highlights maximum({AFTRANSACTION.AFTRKEY}, {ARACCOUNT.ARACID}) and says there must be a group that matches this field.

How do I resolve this? Due to client restrictions I cannot create new groups as the layout has to be specifically what I have now.

[JaganEllis]

When you wrote "I do use groups already" I kind of assumed that you were grouping on the ClientID as that is what we were discussing...
What groups do you have? You can add groups without affecting the layout, but it may affect record ordering - what do you have now?

Maybe use SQL Expressions to get the most recent transaction info for a client so that the info is available on every record.
Or maybe create a view to show this and join to it.