1 Reply Latest reply on Jul 27, 2010 8:52 AM by philmodjunk

    Exchange Rates... again

    JeremyP

      Title

      Exchange Rates... again

      Post

      I have searched the forum and haven't found an answer to my challenge.

      In my FileMaker database, I have created a table called ExchangeRates that holds a currency code, an exchange rate and an effective date.  In my transactional tables, I want to pick up the Exchange Rate from my table based on the date of the transaction - as the transaction might be historic, I need to be able to hold multiple rates.  

      I've seen the solutions to Auto Enter or Script the Exchange Rate into the transactional fields, but this won't work for my scenario.

      Any ideas how I might achieve this?  Any help much appreciated.

      Many thanks

      Jeremy

        • 1. Re: Exchange Rates... again
          philmodjunk

          The Looked up Values auto-enter setting can indeed work for this. You just need to set up the details correctly.

          Define your relationship like this (substitute your table and field names for mine):

          MainTable::CurrencyType = ExchangeRate::CurrencyType AND
          MainTable::TransactDate > ExchangeRate::EffectDate

          Sort this relationship by EffectDate in descending order.

          Now a looked up value will copy from the matching record with the most recent effectivie date less than or equal to the transaction date.