A few years back I wrote a blog post on how currencies worked in Dynamics CRM. This was before the Power Platform and the concept of model-driven Power Apps, but essentially, Dynamics CRM was a model-driven Power App.
The question came up recently on a Power Apps forum (Facebook, I think) and I realized there is a new generation of app makers that might not realize that the Common Data Service has a certain level of partially built in functionality regarding currency fields.
This post is a refresh, but focused purely on a vanilla Common Data Service (CDS) instance, but the same concepts and rules apply for a Dynamics 365 first party app.
When a CDS instance is provisioned, you need to specify a “base” currency. In most cases this will be tied to the geographic reqion to which the app will run. However, there are many businesses (my one-man business included) that will need to transact in different currencies.
Adding Currency Records to the Common Data Service
If your CDS based app (Canvas, Model-Driven or Portal) have a requirement to show money fields in different currencies, you will need to setup currency records in your environment (these are NOT solution aware, so you will need to configure them in your DEV-TEST-PROD).
The way you are supposed to setup currencies in an CDS instance is to navigate to the Power Platform Admin center, choose your specific environment, select settings, the in the business section select Currencies.
BUG ALERT If you are trying to add currency records in a vanilla CDS environment, you will eventually get a white screen. This link appears to work for Dynamics 365-enabled CDS environments. This is a known issue and hopefully will be resolved eventually. Thankfully, Advanced Find to the rescue. From a model-driven app, we can launch advanced find and locate our currency records.
The other option is to add the currency entity to the site map of your model driven app.
Here you can create a currency record by choosing an out of the box currency code or defining your own. Here you can also define the name, precision, symbol and the exchange rate.
NOTE: Exchange rates need to be manually updated or updated by some kind of programmatic process that you need to configure. That will be the topic of another post for another day.
Adding Currency Fields to CDS
When you add a currency field to a CDS entity, there are additional fields that will get created; the money field, a base currency money field, a lookup to the the currency and an exchange rate field.
The money field is where you will store you money value. The base currency money field will hold the amount in your base currency. The lookup will determine the currency for the record (either the base currency or one of the currency records you specified) and the exchange rate used for the record.
Let’s take a look at some records in CDS to see how currency is handled.
In this first example, the record is using the base currency (Canadian Dollar) and we see that the both the Amount Paid and Amount Paid (Base) are the same amount and the exchange rate is 1.
In another record, the currency chosen was US Dollar, and we had set the exchange rate to 77 cents. We see the Amount Paid as $100.01 (US) while the base amount works out to $129.88 Canadian.
In a similar example, the Australian amount is $100, but is converted to $88.49 Canadian.
If a user creates a new record, then the currency will default to the base currency. (It can be changed).
A user can change their personal options and set the default currency they would want to use when creating records with money fields.
Changing Exchange Rates
As mentioned above, out of the box, exchange rates need to be set manually. In addition to this, the exchange rate on the actual record WILL NOT UPDATE, unless the record is active and a money field changes, only then will the record reflect the current exchange rate. In a future post, I will explore using Power Automate to update exchange rates.
Note that if you report on a series of transactions and you are using multi-currency, you will want to report on the base currency, or else you might mix different currency types and get incorrect results.
I hope this post gives you a good understanding of how to use currency fields in Common Data Service projects. Much of this functionality is a hold-over from Dynamics 365 first party apps, but can be very beneficial in building custom Power Apps and Power Automate flows that need to utilized different currencies.
Nick Doelman is a Microsoft Business Applications MVP, a Microsoft Certified Trainer, MVPreneur, Speaker, Blogger, Power Platform Specialist, Powerlifter and has a large stack of Trindad currency worth about 75 cents. Follow Nick on Twitter @readyxrm