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.

This is my new EDT

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

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.

Here is what my table fields look like.

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.

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

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.

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

After I exit the field we have .12

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

What about the real type with no EDT. Before entry

After entry .12.

In SQL the same.

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

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.

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.

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.

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

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.

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.

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.

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.

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

This matches what I see in SQL

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.

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.

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.

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

If I look in SQL the rest are still there

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.

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.

But the full 16 is stored in SQL

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

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

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

Then it would go in ok.

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

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

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

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

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.

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.