MR doesn't seem to translate balances in non-functional currencies the way I thought it would. I had assumed that MR looks to the multicurrency setup and the account/currency record to determine how to translate.
In setup>multicurrency, we have set the translation table for currency MXN to use MXN-USD SPOT as the table for Current and for Average translation types. The Average Calculation Method is set to 'Rate Average.'
In cards>financial>account currencies, balance sheet accounts are set to a currency translation type of 'Current' and P&L accounts are set to 'Average.'
In the rate table, there are rates for various dates.
In MR, I have set two FD columns, for month only. The first column shows a currency filter of MXN, and currency display of NATURAL/ORIGINATING. This column displays the correct MXN amount for the month. The second column has a currency display of FUNCTIONAL. (It doesn't seem to matter if I choose FUNCTIONAL, or 'reporting currency = USD' in this column)
The translated (USD) column shows the USD amount at the translation rate that was entered originally with the transaction(s). The translation appears to have nothing to do with the rate tables that are set in the setup>multicurrency window.
When a transaction is entered in MXN, the rate is taken from the rate table for the date of the transaction; for example at mid-month the rate may be .065. At month-end, we enter the rate for the last day of the month - which might be, for example, .060
The assumption is that we can translate at the appropriate spot (current) rate in MR at month-end. However, the only number that shows in the translated column is the equivalent USD amount from the transaction itself - it doesn't matter that we have updated the rate table at month-end. From the example above, the balance shows at a rate of .065, even though the spot (current) rate has been updated to .060
Anyone have any suggestion or idea what I might have missed? Or is my assumption wrong - does MR not look at rate tables when translating, but only at the rate on the transaction(s) when they were entered?
