The last function will return the last related record where the field you refer to is not empty.
If you define a sort order for the relationship that orders the related records such that the most recently created record sorts out as the first record, you can just refer directly to the related record's fields and you will be accessing the most recently added related record.
Thanks for your help, much easier than fiddling around with Calculations/Scripts etc.
It's worth noting that:
Option 2 does not work if I apply a sort to a relationship based on a new TO of the table i.e. I have to apply the sort to the original relationship between the two original tables in the Relationships Graph. This seems like odd behaviour to me, as I thought that the whole point of creating a new TO, is to be able to relate/sort tables based on the requirements of my Layouts, which could easily be different from the original ER type graph. Am I missing something?
Also Option 1 is Ok providing a user doesn't edit a Valuations::Valuation Date entry afterwards, as this function just returns the last record regardless of "Valuation Date".
If you use a new TO, then to access the data in the most recent record, you'll need to refer to the fields of that record by using that new TO as your table name.
IF you add this relationship:
MainTable::PrimaryKey = SortedRecords::ForeignKey
With the needed sort order specified for this relationship.
Then a calculation that refers to SortedRecorsd::DataField will return the value of data field for the most recent record. If you want to display this data on a layout, you can add fields from SortedRecords to your layout and you'll see data from the most recent records. (Though in this case a sorted portal becomes an option when all you need to do is display the data.)
The Last function only works if the order of the related records puts the desired record last. If there is no sort order specified for the relationship, the "last" related record will be the the most recently created record. If creation order of your records doesn't put the "most recent" record last, then you'd need a sort order for this relationship as well.
I should have known better and fully checked my work before posting.
Of course you are absolutely correct, I had forgotten to re-reference the TO in my calculation fields. Basic error and thanks for your help once again.