Jump to content

How do I relate to the last related record?


genious

This topic is 3035 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by genious
Link to comment
Share on other sites

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 by comment
  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 3035 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.