Problems showing latest payments

[Iain75]

I am trying to create an object on our v5.1.7 Universe to show the Users the 'LAST PAYMENT' made by a customer.

Each transaction for a customer is given a unique, ascending reference number.

In a Program, I could use

SELECT
FT.CUST_NO,
FT.TRANSACTION_AMOUNT,
FT.TRANSACTION_DATE
FROM
FINANCIAL_TRANSACTION FT,
WHERE
FT.TRANSACTION_CODE = 'PAY' AND
FT.CUST_NO = :CUST-NO
ORDER BY
TRANSACTION_ID DESC
LIMIT TO 1 ROW;

How can I create an object so that a User can create a report showing the latest payment for each customer?

Using 'Calculations' in conditions, appears to be very confusing to the users and is a bit long-winded to repeat in Multiple Queries. Effectively, what I am looking for is a way of defining an object which becomes a calculation condition.

 

[kishleo]

is yoru transaction date is same as yoru payment date? or do you have separate payment date? if it is same why dont you select the max payement date group by customer???

Kishore MCDBA

 

[johnherman]

select cust_no, max(transaction_date) from financial_transaction where transaction_code = 'PAY'
order by cust_no

Sometimes the grass is greener on the other side because there is more manure there - original.