"I want to be able to specify a date at the top of the table"
Define a field, gCurrenyDate and specify global storage in Field Options.
"...and then have my calculation evaluate the exchange rate table and provide the appropriate exchange rate by currency code and date for each record. "
This relationship should work for that:
MainTable::gCurrencyDate = Currency::CurrencyDate AND
MainTable::Currencycode = Currency::CurrencyCode
Thank you for your response.
I have set up the global field and created the relationships you suggest but when I change the date in the global field nothing is populated into my exchange rate field.
Do I also need to write a calculation in the exchange rate field to look it up from the currency table?
Thanks for your help!
Do you need to physically copy the rate or can you just refer to it?
If you just need to refer to it, you can discard the Exchange Rate field in your main table and replace references to it with references to Currenct::ExchangeRateField.
If you have a calculation in your main table like this:
Amount * ExchangeRate
Change it to:
Amount * Currency::ExchangeRate
When you want to display the exchange rate on your MainTable's layout, just add the Currency::ExchangeRate field to it.
Note: with the global field for your date, changing this date will cause all such calculations in your current found set of records to recalculate as they will now link to different records in the currency table.
Thank you so much for your help, I just need the reference so that works perfectly.