• (619) 483-4180
  • info@avantiico.com
Facebook-f Linkedin Instagram Youtube

Quick Guide Dynamics AX 2012 Dta Layer Decimal Precision

Aaron Emde

The Microsoft Dynamics AX/D365 Support Team at Avantiico is focused on solving our client’s problems, from daily issues to large and more complex problems.

See All Posts

We all know that Dynamics AX 2012 uses Microsoft SQL Server as its database backend, but how does AX handle decimal-based data types in the data layer? In this blog, we will be going into detail on how AX handles Real based Extended Data Types, how that data is saved in SQL, as well as some potential pitfalls of bypassing best practices when it comes to integrating with and importing data into AX.

Let’s take the AmountCurDebit EDT as an example. you can see in the Type hierarchy browser that AmountCurDebit inherits its number of decimals ultimately from the Money system data type. The Money System Data Type rounds to two decimal places, therefore, the AmountCurDebit EDT rounds to two decimal places as well (Figure 1).

Dynamics AX debit ledger journal
Figure 1 – AmountCurDebit EDT on the LedgerJournalTrans table

When opening the LedgerJournalTrans table in the AOT, you can see that the amounts are two decimal places (Figure 2). But, what does this data look like in SQL?

Dynamics 365 Table browser
Figure 2 – AmountCurDebit EDT rounds to two decimal places

Here we have selected the same records from within AX and directly from the SQL database. You can see that although the data is displayed with two decimal places in AX, the actual data is saved to a precision of 16 decimal places in SQL. Why is this? (Figure 3)

Dynamics AX table Ledger Journal
Figure 3 – AmountCurDebit data in AX vs SQL

The reason that the data in SQL is showing 16 decimals is that the AmountCurDebit AX EDT is translated to a SQL data type of Decimal with a scale of 32 and precision of 16. This means that a number with a length 32 on the left and 16 on the right can be saved here (Figure 4).

Dynamics Object Explorer
Figure – 4 AmountCurDebit EDT definition in SQL

At this point, you may be asking, “Why should I care?” Good question! We have seen instances during data migrations or data integrations that bypass the AX Application Layer (AOS) that has resulted in values with greater decimal precision than the EDT of the table field. This can cause headaches and sneaky data corruptions that may not surface until there is a big problem. Just what happens in AX when this occurs? Let’s take a look.

In Figure 5, there is example job that is updating ten records in the LedgerJournalTrans table with decimal values with a precision greater than 2.

Dynamics AX ledger journal
Figure 5 – Example of updating LedgerJournalTrans.AmountCurDebit with too high decimal precision

As the script runs, it selects records from the LedgerJournalTrans table and updates the AmountCurDebit field.

AX does not respect the EDT’s decimal precision because it sees the AmountCurDebit value of the LedgerJournalTrans table as a Real type (Figure 6).

Dynamics AX Debugger
Figure 6 – AX Debugger shows AmountCurDebit with decimal precision of three

Now that we have some records that do not respect the EDT’s two decimal rule, how will AX respond when these records are accessed?

You can see in figure 7 that AX rounds the values for us following the usual rounding rules you might expect. When the third decimal place is greater than 5, AX rounds up, otherwise the number is not changed.

It is plain to see how discrepancies like these can cause real havoc in your system. Accounting will not be happy if their general ledger postings do not match up!

Dynamics AX table browser
Figure 7 – Corrupt data in LedgerJournalTrans in SQL vs AX

So, how do you avoid this type of data corruption? Extended Data Type decimal precision is enforced at the UI level. You can see this in the example table we’ve created in figure 7. The table only has one field with the EDT of AmountCurDebit.

If you attempt to enter a value with a decimal precision greater than two, the AX UI will round the value for us when you leave the field (Figure 8).

Dynamics AX table browser
Figure 8 – Example of UI rounding a value in AmountCurDebit EDT field

If you have a requirement to insert/update records from X++, you must make sure to respect the decimal precision of the EDTs we are interacting with. One solution we like to use for EDTs that are saving monetary values is to leverage the CurrencyExchangeHelper class. This class is able to round values based on the currency rounding settings of a given currency. Let’s take a quick example of this in action (Figure 9).

Dynamics AX Currency exchange helper
Figure 9 – Example of CurrencyExchangeHelper class rounding currency values

Here is our script from earlier with a modification to use the CurrencyExchangeHelper class. If we run this code, we can see that CurrencyExchangeHelper class will return a rounded value for us (Figure 10). You see here that the value is rounded as expected.

Dynamics AX Debugger
Figure 10 – Debugger showing CurrencyExchangeHelper class rounding a value

If you choose to use this method to round values, please make sure the currency rounding settings are correct in your AX environment. You can find them in the Currencies form (General ledger → Setup → Currency → Currencies). Figure 11.

Dynamics AX Currency
Figure 11 – Currencies form in AX 2012 R3

In this blog we covered the following items:

  • An EDT with a Real base type is saved on the data layer with a precision of 16 decimal places.
  • The NoOfDecimals property of a Real EDT is not respected in X++ table buffers and we need to account for this when working with them.
  • Corrupt records that do not respect AX EDTs can cause unexpected results in AX.
  • The AX UI is what rounds values for us in respect to the EDT NoOfDecimals property.
  • We can use the CurrencyExchangeHelper class to round monetary values for us in X++ code.

Have you experienced complications with Real EDTs and data corruption in your AX 2012 systems? We love to hear about how others have solved similar problems. Leave a comments below with your problems and/or solutions, and an AXM expert will get back to you shortly!

Facebook
Twitter
LinkedIn

More to explorer

Featured

Microsoft partner logo

Products

Resources

Our Company

Join Our Newsletter

Stay up to date on everything Microsoft. Look forward to thorough articles on all of the latest and greatest happening within Dynamics 365.

Subscribe Now
Linkedin Youtube Twitter Facebook Instagram

600 B Street, Suite 300, San Diego, CA 92101    |    (619) 483-4180    |    info@avantiico.com

Request a free Dynamics 365 meeting

Discover how Avantiico helps you improve business processes, provide customers with a seamless experience and transform the way you do business.