I would like to have a Table of Transactions as follows:
TransactionID field is Autonumber
Date, Description, Amount, Currency are entered
Year is the formula Year(Date)
In addition, I have exchange rates between my business currency (HKD) and other currencies, by year: one set of exchange rate for 2019, one for 2020, in the future for 2021, 2022, etc.
I would like to have a table that contains the exchange rates for each year, for example like this:
Finally, the RateForthisTx Field in the Transaction Table should be the result of a lookup(Currency, Year), e.g., lookup(AUD, 2019), or lookup (CHF, 2020), whatever corresponds to the actual values in a given transaction.
I must be thick, but I can’t find a solution Airtable, unless, perhaps, we’d have to resort to Scripts.
Once again, a big thank you to anyone who can help.