Adventures with Dataverse: Fun with Currency and Decimal Data Types

I had a question around what happens to the value of a Dataverse field of type Currency or Decimal when I changed the decimal precision.

The question❓, what happens to the underlying values stored in the Dataverse field when the decimal precision is changed?

Is it automatically truncated? 🤔

Or is the original value retained and just displayed with the correct decimal precision? 🤔

To test things out, I created a table with the following six columns:

• Currency (Currency precision)
  • Data type is Currency
  • Precision source is Currency precision
• Currency (Pricing decimal precision)
  • Data type is Currency
  • Precision source is Pricing decimal precision
• Currency (Precision property is 2)
  • Data type is Currency
  • Precision source is Precision property
  • Decimal places is 2
• Currency (Precision property is 2)
  • Data type is Currency
  • Precision source is Precision property
  • Decimal places is 4
• Decimal (Decimal places 2)
  • Data type is Decimal
  • Decimal places is 2
• Decimal (Decimal places 4)
  • Data type is Decimal
  • Decimal places is 4

A little background on Precision source.

When Precision source is set to Currency precision, the precision is pulled from the Currency table, which is currently set to 2.

The default for Precision source is Currency precision.

When Precision source is set to Pricing decimal precision, the precision is pulled from the System Settings for the Organization, which is currently set to 2.

Whenever I am working with Dataverse tables, I create a Model-Driven App to help with the data entry.

For each of the six fields I entered 9999.1234.

Quick observation shows the fields Currency (Precision property is 2), Currency (Pricing decimal precision), and Decimal (Decimal places 2) automatically truncated the decimal places, setting 9999.1234 to 9999.12.

Just for kicks and grins I looked at values with XrmToolBox. 👀

Everything seemed to check out. ✔️

Pricing Decimal Precision

Now, let’s make some changes, starting with System Settings.

From the Gear ⚙️ icon in the upper right, I clicked Advanced settings.

I clicked Settings, then Administration, and finally, System Settings.

I set Pricing Decimal Precision to 4 and then clicked OK.

Editing Item 3, the field Currency (Pricing decimal precision) is now showing four decimal places, as expected.

I set Current (Pricing decimal precision) to 9999.1234 and look at the saved result both in the Power Apps Studio and XrmToolBox and it shows as 9999.1234.

Let’s change Currency (Pricing decimal precision) to 2 and see what happens.

Looking at the original entry, the value displays as truncated, also when I added a new value, it behaves as expected. 👀

When I look at the values in XrmToolBox, I noticed that the original value of Currency (Pricing decimal precision) is unchanged.

Not sure if this gets adjusted later, but doubt it, for now, good to know, if you change it at the System Settings, it will display correctly, but the original value will be retained.

Currency Precision

Let’s see what happens when we change it in the Currency table, I am expecting similar results to when I changed it in the System Settings.

I set the Currency Precision for USD to 4.

Opening the form back up, I can see the display now shows four decimal places.

I set Currency (Currency precision) to 9999.1234 and click Save and Close.

Interesting, when I view it, it displays the four decimal places, but is still truncating the value, shows 9999.1200, instead of 9999.1234. 👀

Maybe it’s a publishing issue?

I navigate back to my Solution and click Publish all customizations, crossing my fingers in the hope that this will work.

Still didn’t work! 😖 Maybe a caching issue? Will close and relaunch the Model-Driven App.

That seemed to do the trick.

I am now able to set Currency (Currency precision) to 9999.1234, I click Save and Close.

Let’s change Currency Precision for USD back to 2.

Just for kicks and grins, I click Publish all customizations from the Solution and relaunch the Model-Driven App.

Display of the values look good, only showing two decimal places. 👀

When I look at the values in XrmToolBox, I noticed that the original value of Currency (Currency precision) is unchanged.

On adding records and editing existing records, the correct value, with just the two decimal places, was now being captured. ✔️

I did notice that you cannot just click Save and Close to update the field, you have to change it, and then click Save and Close, and the value, with the correct number of decimal places will be stored.

Specific Precision Values or Precision Property

The last thing I wanted to do is look at what happens when you change the Decimal places from 4 to 2 for a field that had Precision source as Precision property and Data type as Currency.

I set the value of Currency (Precision property is 4) to 9999.12341 for three records.

I edited the column Currency (Precision property is 4) and set Decimal places to 2.

I clicked Save.

Because this is a change to a table in a Solution, I click Publish all customizations from the Solution and relaunch the Model-Driven App.

As expected, the display of the values is updated, showing two decimal places, instead of four decimal places. 👀

XrmToolBox still shows the original value, with four decimal changes, being stored.

If I edit a record, change the value, and then Save and Close, the correct number of decimal places persisted to the table. ✔️

So what’s the takeaway?

💡If you change the Precision source or Decimal places property, current records are not automatically updated with the changes.

💡New records and edited records will reflect the changes.

If it was important that the change was reflected in the tables, you would need to leverage a flow to loop through all the records and update them.

Which would I use in my applications?

It would depend.

❌I would tend to stay away from System Settings as that might be outside of my control as an App Maker, one day it’s four decimal places, the next day, it could be two decimal places.

This would apply to all Environments.

This might introduce some calculation errors into my application that I might not be aware of prior to the change.

⚠️If I was an Admin on the Environment, I would be good with managing it in the Currency table.

Keep in mind that if I pushed the Solution from one Environment to another, I would need to make sure the Currency table in the target Environment had the correct Currency Precision in its Currency table.

❤️What I would probably do though, is if a field needed four decimal places, I would set the Precision source to Precision property and Decimals places to 4 for the field in the table.

These settings would then be included in any ALM process, manual or automatic, and ensure source and target Environments were the same.

Quite an exercise, but hopefully worth it!