Real Time Exchange Rates using a Calculated Field in Dynamics CRM
By Joe Gill
Published On 11th February 2015
Each currency field in Dynamics CRM actually consists of three fields the currency amount, the base currency amount and the exchange rate. When a record with a currency field is saved the corresponding amount in the system’s base currency is calculated using the current exchange rate and all three values are saved.
While this is fine for some scenarios where you want to stamp and lock the FX rate on the record it is not appropriate in others. A typical scenario is when you have multi currency opportunities and you want to report on these using the latest exchange rate. If you use the base amount then you will get the base currency value at the time the record was saved. Prior to the release of Dynamics CRM 2015 using the current exchange rate was difficult to achieve without custom code.
The introduction of calculated fields in Dynamics CRM 2015 will allow us to calculate the estimated revenue of an opportunity in the base currency using the latest exchange rate. This technique can also be used for many other scenarios where you want to lookup the latest price or rate to calculate the latest value of a record.
Follow these steps to create a calculated field that will show the opportunities estimated value using the latest exchange rate. Create a new field on the opportunity entity of data type decimal and field type set to calculated.
While this is fine for some scenarios where you want to stamp and lock the FX rate on the record it is not appropriate in others. A typical scenario is when you have multi currency opportunities and you want to report on these using the latest exchange rate. If you use the base amount then you will get the base currency value at the time the record was saved. Prior to the release of Dynamics CRM 2015 using the current exchange rate was difficult to achieve without custom code.
The introduction of calculated fields in Dynamics CRM 2015 will allow us to calculate the estimated revenue of an opportunity in the base currency using the latest exchange rate. This technique can also be used for many other scenarios where you want to lookup the latest price or rate to calculate the latest value of a record.
Follow these steps to create a calculated field that will show the opportunities estimated value using the latest exchange rate. Create a new field on the opportunity entity of data type decimal and field type set to calculated.
Click the Edit button to enter the calculation for the field. Add a condition to check the estimated value contains data and in the action enter the following
estimatedvalue / transactioncurrencyid.exchangerate
The simplest way to test this is to add create a new view containing the estimated revenue, the estimated base revenue and the new estimated revenue current FX fields.
Now goto Settings-> Business Management -> Currencies and change the currency exchange rate.
Refresh the view and you will see the new rate is being used in the calculated field and you can see the difference between it and the base estimated revenue.
The benefit of using calculated field is that they are calculated when the records are retrieved so all your views, reports and dashboards will use the latest exchange rate without the need for any custom development.