Returning a field from latest record

[William Lindsay]

Hello

I have 2 tables linked by pk and fk. They are jobs and quotes. A job can have a few versions of a quote, the last one being the most up to date.

I can find the latest related record and return that field, but I want to return the cost of that single related record too.

I have tried using If (latest related record=pk of record ; cost; 0) but it doesn't work. Any help appreciated - I am sure it is pretty simple.

Thanks

W

[comment]

Have a look at the Last() function. Note that the order of related records can be changed by sorting the relationship. If you sort the related records by date, descending, the latest record will become the first related record and its fields can be referenced simply by their names (or displayed directly on the layout of the parent table).

[William Lindsay]

Thanks. I was trying to overcomplicate things by trying to reference via the last ID to get to the field I wanted. I just use Last(field I wanted) and it did the trick.

Cheers

[rwoods]

If you wanted to make this independent of any sorting order on your relationship, you could use some SQL to get the value you need. It would look something like this :-

Set Variable ( $lastestCost ; ExecuteSQL ("

SELECT cost

FROM quotes

WHERE fk = ?

SORT BY dateOfQuote DESC

FETCH FIRST 1 ROW ONLY " ; "" ; "" ; pk ) )

The 'DESC' in the SORT BY line means 'descending', so that the matching quotes are sorted newest first, and the FETCH line means you will only return the 'cost' from the most recent quote.

Love a bit of SQL!

Edited September 29, 2017 by rwoods