Guide to Currencies in the Common Data Service

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.

Money

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.

Provisioning new CDS instance

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.

Settings Area to add new currencies – only works in Dynamics 365 enabled CDS (this is a bug)

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.

Advanced Find to locate Currency records in CDS
Viewing Currency records

The other option is to add the currency entity to the site map of your model driven app.

Add entity to model-driven site map

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.

Create new currency record

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.

Adding a money field to a CDS entity

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.

Base Currency

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.

US Amount

In a similar example, the Australian amount is $100, but is converted to $88.49 Canadian.

Australian Dollar

If a user creates a new record, then the currency will default to the base currency. (It can be changed).

New record.

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.

Reporting Considerations

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.

Wrong and Right way to summary a total from CDS records

Summary

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.

Cover Photo by Geronimo Giqueaux on Unsplash

Coin Photo by Ibrahim Rifath on Unsplash

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

2 thoughts on “Guide to Currencies in the Common Data Service

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s