Understand the benefits of Supply Chain Management Software for your organization by breaking down the key features and taking an in-depth look at the markets
Learn accounts receivables automation benefits and best practices from readiness through to implementation. Introduction As businesses grow and scale, managing accounts receivables (AR) becomes increasingly
Learn how automation for 3PL companies helps them to overcome many of the issues facing the 3PL industry today. Introduction In recent years Third-Party Logistics
Learn how working with a D365 Supply Chain Consultant can improve the way your supply chain operates, from increasing efficiency to reducing operational costs and
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).
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?
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)
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).
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.
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).
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!
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).
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).
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.
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.
In this blog we covered the following items:
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!
Understand the benefits of Supply Chain Management Software for your organization by breaking down the key features and taking an in-depth look
Learn accounts receivables automation benefits and best practices from readiness through to implementation. Introduction As businesses grow and scale, managing accounts receivables
Learn how automation for 3PL companies helps them to overcome many of the issues facing the 3PL industry today. Introduction In recent
Discover how Avantiico helps you improve business processes, provide customers with a seamless experience and transform the way you do business.