Real data types in Dynamics AX

When developing in AX, the application designer and the developer choose the right data type of the functionality they are providing. When considering decimal places and rounding the real data type is the one we need to consider. How a real type is stored and behaves is what I wanted to explore here.

For the purpose of explanation I’ve created a really simple table in AX called ATable, very creative name but not interesting for the discussion. What I’ve done is created three fields:

  • One called Nodatatype which was created by creating a new field directly on the table based on the Real data type, not a best practice for maintenance and you will see why.
  • Second call ARealType in this case I followed the best practice of creating a Real extended data type (EDT) first, then basing my field on that EDT.
  • Third I created a field based on the Amount EDT.

If you look at the properties of Amount you will see that it inherits from a type called Money.

clip_image001

This is my new EDT

clip_image002

You will see this doesn’t inherit from another EDT it’s just a real type.

clip_image003

If we look at the type hierarchy browser then Amount EDT is a real type but is special because it inherits from a type called Money. You won’t find Money in the AOT because it’s implemented in the Kernel and has some special characteristics.

clip_image004

Here is what my table fields look like.

clip_image005

If I use the table browser and enter a value in the fields let’s have a look at the effect. First the Amount. You can see here I’ve taken a screen shot of the entry before I’ve moved off the field. So the number is 123456789.123456789 which is 9 decimal places.

clip_image006

When I move off the field what is captured? 123456789.12 it’s now 2 decimal places. What happened to the other 7 places?

clip_image007

If we go to SQL and have a look what is stored in the DB we can see Amount is only .12 but there are 16 places trailing the decimal.

clip_image008

Let’s have a look at the other types. The Real EDT before I exit the field we have 9 places after the decimal.

clip_image009

After I exit the field we have .12

clip_image010

In SQL we have the same .12 stored. So what happened to the other decimals?

clip_image011

What about the real type with no EDT. Before entry

clip_image012

After entry .12.

clip_image013

In SQL the same.

clip_image014

Ok so we have some consistency at least. But let’s look at what affects this.

First if I got to the regional settings on the machine. We can change the date and time formats. If I click the Additional settings button

clip_image015

I can change the number format details. One of the options is “No. of digits after decimal”. This is set to 2. So I’ll change this to 9 for our exercise.

clip_image016

9 is the largest option I have. So I’ll have to click apply and then re-start the AX Client for this to take effect.

clip_image017

So when I come back in you can see the display effect on the different types. I didn’t enter new data just opened the table browser again to the record we created earlier. So my Nodatatype field picked up the change but the two others based on the EDT didn’t.

clip_image018

This time I’m going to go back and change the Currency regional settings and make this to 9 decimal places.

clip_image019

You will see no effect when I come back in and have a look at the records. There in highlights why EDTs and best practices in development are good because if all the users randomly changed their regional settings we would be storing different values per users which could be quite un-predictable.

clip_image020

What this does highlight is that there are other properties that control this behavior. If we look at the properties of the data type that was created called ARealType which is based on real you see you have some control over those display options as here I have the number of decimals. If you were to look at the field that was created directly you have not properties like this to control the data so another reason why not to create the field without an EDT.

clip_image021

So if I change this to match out example then I have 9 decimal places. In effect you could go up to 16 to match what is stored in SQL. While you could put something larger than 16 the precision would be lost and it would be rounded to 16 places as this is what SQL stores.

clip_image022

So after I make the change, sync and restart the client I’m back in and entering a number. You can see before entry the number is much bigger than 9 decimal places, it’s 15 decimal places.

clip_image023

What we can see is that 9 decimal places are stored because that is what we essentially asked it to do.

clip_image024

This matches what I see in SQL

clip_image025

Now in a scientific application decimal precious up to 16 or more decimal places might be important but for a business applications this I believe would be unlikely perhaps if you were extending the quality module and storing more information about a chemical analysis this might be something you are interested in but commonly up to 4 or 5 decimal places if probably the typical limits usage for business applications. What happens when we set the decimal places to something larger ?

Now 16 decimal places.

clip_image026

You can see I’ve got the full 16 places. Notice I don’t have something before the decimal I’ll come back to that.

clip_image027

clip_image028

What happens when I change to 17? Or something larger than 17. Remember anything larger than 16 is going to be lost anyway because SQL won’t store anything beyond 16.

clip_image029

In AX I see only now 2 decimal places. What happened to the rest?

clip_image030

If I look in SQL the rest are still there

clip_image031

What happens past the 16 is that AX will now go an use the regional settings to change what is displayed. So if I change the regional settings “No. of digits after decimal” to 4.

clip_image032

AX will now show me 4 of those decimals. So another factor you need to consider is storage precision vs display as there are settings in the form that change the display we’ll cover those in another post.

clip_image033

But the full 16 is stored in SQL

clip_image034

What is stored in SQL is numeric(32,16)

http://msdn.microsoft.com/en-us/library/ms187746.aspx

clip_image035

This is a maximum 32 decimal digits with 16 decimal places which is a number that looks like

9999999999999999.9999999999999999

1234567890123456.1234567890123456

If I was to build a table in SQL and insert this number

clip_image036

Then it would go in ok.

clip_image037

If I try to do this from AX. Before I tab out of the field

clip_image038

After I tab out you will see what is stored is the first 16 digits without the decimals.

clip_image039

This is because internal the numbers are handled in the kernel as a floating point number. http://en.wikipedia.org/wiki/Floating_point

You will see if you do an AX trace and see the number that is sent from the AOS to SQL

clip_image040

This is the SQL trace from the other side in SQL accepting the value and updating it.

clip_image041

You will see that if I try to key the value plus an extra digit into the Amount field which is based on the Money type it will range it expects. Remember Money is a real type implemented in the kernel so has some extra functionality over the real EDT.

clip_image042

Is this an issue, no I don’t believe so from a business application points of view but you need to be aware of it so you can plan how you want to store and manage the data in the application.

This means you can have a number expressed in 1 Quadrillion. For example expressed here as $.

$ 1,234,567,890,123,450.00

Quadrillion, Trillion, Billions, Millions, Thousands

If we factor in we might need to use the 2 decimal places it’s going to be Trillions.

$ 12,345,678,901,234.10

Trillion, Billions, Millions, Thousands

Or as per the information message the range is

(99,999,999,999,999.99)         99,999,999,999,999.99

Trillion, Billions, Millions, Thousands

 

So I’ve been rambling on here for a while but why is this important to understand? Well as I tried to cover in a previous post https://organicax.com/2014/08/16/decimals-prices-uom-currency-rounding/ understanding decimal places can be important in a business application. For example if you designed you app to store 4 decimal places then the number range is going to be

(999,999,999,999.9999)         999,999,999,999.9999

Billions, Millions, Thousands

If you are lucky enough to have a trillion dollar business and for some reason you wanted to see 4 decimal places then you might want to re-consider do you really need 4 decimal places.

What about countries with hyperinflation? Just to digress Hungary apparently holds the record of printing a 1 sextillion pengő note in 1946. http://en.wikipedia.org/wiki/Names_of_large_numbers the point being that currency is a measure for the numbers and has a bearing on what might need to be displayed.

From an application perspective can you change AX to use 4 decimal places? While this is a simple question the answer is complex so I’ll explore that in future posts because this one has become very long, but hopefully helpful for your understanding of AX.

Cheers

Lachlan

PS : I was using AX2012 R3 for these notes but the same would apply to earlier version of AX.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s