How do I relate to the last related record?

[genious]

I need a little bit of help.  I am trying to relate an invoice table to my currency rates table, I want to always get the last rate rather than all rates.  How do I do this?

I have two tables Invoices and Exchange rates.

An Invoice exchange rate would be €1.00 is 1.1006 31st October 2015

                                                              €1.00 was 1.1201 in 1st October   (this might be the last time it was updated)

 

How do I get the system to always default to the last value entered? So if I enter a new invoice today the rates will always take the rate entered today rather than the one entered on 1st October.

Edited October 31, 2015 by genious

[comment]

If you sort the records on the Exchange rates side of the relationship by Date, descending, then the latest record will become the first related record and the value will be looked up from there.

Alternatively, you could auto-enter a calculated value = Last ( Exchange rates::Rate ) instead of a lookup.

A more elaborate solution would add the Date field as a second matchfield in the relationship - so that the lookup finds the correct rate even if the record is backdated.

Edited October 31, 2015 by comment

[genious]

Yes that sounds like what I will do.  The relationship will be based on the Identifier of the currency and it should be less than or equal to the date of the invoice, sorted descending by date.  Perfectly logical!  Many thanks for your help.