I recently had a requirement to display a list of opportunities in a gallery in a canvas app screen. This was easy to achieve but my opportunities were in multiple currencies so how could I show money values with the correct currency symbol? In this post I will explain what I did.

You might not know that money values in the Dataverse involve three columns. The first (as you’d expect) holds the value of your currency amount. The second field always exists as soon as you add a money value to a table, it is a lookup to the currency. Meaning we then know what currency our value is in. Finally we always have a second currency amount for each money field. This is the base currency equivalent to our local currency value. (Calculated using an exchange rate that is held again each currency record.)

Each organisation will have one base currency. This is defined when we first create our environment and cannot be changed. My Dynamics 365 instance, for example, has a base currency of Pounds Sterling. As I live in the UK!

Consider the estimated value field on an opporuntity! For this we have three related columns …

• Currency (transactioncurrencyid) – which is a lookup to the currency table. This holds the currency for this opportunity.
• Est. revenue (estimatedvalue) – this is the field you’ll typically see in views and forms. It holds the money value in the selected currency.
• Est. Revenue (Base) (estimatedvalue_base) – this is a read-only value which represents the “estimatedvalue” expressed in your base currency.

In model driven apps we don’t often have to worry about how currency values display. As they will automatically show correctly. Below, for example, you can see that on my opportunity I have a currency of “US Dollar”, and therefore all my currency values are displayed correctly with a dollar sign.

Great! However, Canvas apps might not always be so easy!

As a very simple example I have created a gallery including my opportunities. You can see below that I have added a text label and set it to ThisItem.’Est. revenue’. This does display my value, but it has no formatting! Meaning I just get “96500”.

Next, I wanted to show the currency of each row! So you can see I have added a second text label. This time my label is ThisItem.Currency.’Currency Name’. Meaning I can now see that the currency for this opportunity is Dollars, Pounds etc.

Tip:
Other useful fields exist such as ThisItem.Currency.’Exchange Rate’, ThisItem.Currency.’Currency Code’, ThisItem.Currency.’Currency Symbol’.

That is fine but my value still has the wrong format! So how can I use the currency field to format my number?

Below you can see that I have added the estimated revenue field to my gallery again. But this time I have used a different formulae.

ThisItem.Currency.’Currency Symbol’ & Text(ThisItem.’Est. revenue’,”#,##0.00″)

I am basically showing the currency symbol from my currency lookup. And also using the “Text” function for format my value. (Using “#,##0.00”.) Meaning I now have a value with the correct formatting.

Finally, I wanted to also show the base amount for each of my opportunities. My base amounts are always in pounds!

For this I can make use of a third optional parameter with my “Text” function. That being location. Below you can see that I used a similar formatting. But this time I included a $ symbol! “$#,###0.00”.

I also that added an extra parameter of “en-GB”. This means any formatting will assume the GB location. Meaning my money values will show with the £ sign.

Text(ThisItem.’Est. Revenue (Base)’,”$#,###0.00″,”en-GB”)

Aren’t currencies fun??

Hopefully this post will have given you a few tips on understanding currency values and also formatting the way they are displayed. Enjoy!