Changes to Microsoft Dynamics NAV Change Log System: How to Revert its Behavior
The change log functionality in Microsoft Dynamics NAV has existed within the product since the old “Navision” days. 3.01.A was the first version that I worked with and it has been generally a valuable and sometimes overlooked (and misused) tool for our clients. With Microsoft Dynamics NAV 2013, the system was changed in a subtle manner that had adversely impacted some of our clients and allowed me to take some time to dig into the details of the new system and what can be changed.
How does Microsoft Dynamics NAV change log work?
As with any product, improvements to base functionality occurs with most major releases, and change log had been an overlooked feature for some time. There is basically two different (stock) change log systems that are now in the RTC: the pre-NAV 2013 change log (Classic/RTC) and the NAV 2013+ change log system. In order to understand how this change can impact you and your users, we need to start with where you are possibly coming from (upgrading from a pre-NAV 2013 environment or below).
Pre-NAV 2013 Change Log
The change log system up to NAV 2009 R2 (pre-NAV 2013) is exposed to the end-user in Administration > Application Setup > IT Administration > General Setup > Change Log Setup (Task). When opening the page, it allows for the end-user to enable the change log system by checking the “Change Log Activated” value in the system.
Figure 1 – Change Log Setup in Microsoft Dynamics NAV 2009 R2
With change log activated, it still requires end-user setup to specify what tables are logged and what type of events are logged in the system. In this example, the system has been setup to log the Payment Terms table with specific values, with the Some Fields setup, when inserting (creating) or modifying (updating) data. In the case of a delete event, the system logs all fields as the All Fields value was setup in the Change Log Setup (Table) List page.
Master data is a common set of data that end-users log, and we generally recommend to do this type of setup to track changes made by end-users that could impact a business process flow within the system. It’s generally a safe rule to log All Fields on master data tables (i.e. Customer, Vendor, Item, Resource, G/L Account, Dimension, Dimension Value, et cetera) as the volume of data changes should be minimal. We’ll explore this in a bit – why transaction volume matters to change log and how it impacts your system and performance.
Transactional data is another story and generally, must be handled on a case-by-case basis because of the potential impact on your systems performance. Transactional tables (i.e. Sales Header, Sales Line, Service Header, Service Line, et cetera) are continually being inserted into, updated, and then deleted in the system as a business process flow is executed. With these types of tables, we generally log specific values or ensure that the business processes used in the environment do not adversely affect the performance of the environment.
Ledger data is the final table type to log (GL Entry, Cust. Ledger Entry, Vendor Ledger Entry, et cetera). End-users generally do not have access to modify these tables, and if they do (i.e. the Due Date on a Customer Ledger Entry can be modified by an end-user), there are hooks installed in these modifications that explicitly log the event through the change log system.
In the pre-NAV 2013 Change Log system, there are 2 ways that events are captured in the system:
1. An end-user change on a Page/Form in the system.
a. This change calls a function in Codeunit 1 (ApplicationManagement) in the OnGlobalInsert(), OnGlobalModify(), OnGlobalDelete(), and OnGlobalRename() functions.
2. An explicit call to Codeunit 423 (Change Log Management to either the LogModification(), LogInsertion(), LogDeletion(), or LogRename() functions.
To test, I have enabled the Sales Header and Sales Line tables to be full logged and we’re going to create a Sales Invoice – the resulting number of change log entries (132 total entries) includes all of my end-user changes, but does not include the “Release” function as there is not an explicit call in Codeunit 414 (Release Sales Document) to log the change. Therefore, only the user specific changes and no the actions are logged in the system.
Figure 2 – Change Log Entries in NAV 2009 R2 (Sales Invoice Creation)
For the next example, I created a simple example in code that creates a Sales Invoice that looks as follows:
Figure 3 – Example Codeunit to create a Sales Invoice through C/AL
When looking at the change log in the system, with the aforementioned code being run before-hand, the system does not log any of the transactions in the system as they are not considered “User” changes and there is no explicit call to the Change Log Management codeunit in the system.
This allowed one of our existing clients to then use the change log system to track all manual user changes in the system. All changes in the system that were handled by an action (or business process, such as posting a document or releasing a document) were not tracked. Any of these events that needed to be checked had the Change Log reference added in the process and was then logged.
For some environments, this could be a deal-breaker (meaning code based changes should be logged by default in the system), while others actually prefer this behavior in the system. This design has been prevalent since the 3.0 product launch of Dynamics NAV (NAV 2013 is version 7.0) and many end-users have grown accustomed to this design and behavior.
NAV 2013 and Beyond Change Log
Microsoft Dynamics NAV 2013 aimed to close this gap by tracking all changes to the database, even though changes initiated by code. This is a more accurate reflection of a true change log system , where each field, whether impacted by a process or directly by an end-user, is logged in the system for visibility when the setup is enabled.
Enabling Change Log in NAV 2013 is exactly the same as prior versions where the end-user experience was not altered. The biggest change occurs in Codeunit 1 (ApplicationManagement). The default location for the change log logging mechanism now occurs in different functions:
Figure 4 – ApplicationManagement (Codeunit 1) Triggers
Instead of the OnGlobal…() type functions in the system, they have now been replaced with the OnDatabase…() functions – the out of the box ApplicationManagement codeunit does not have the functions used by the older design. Setting up the change log similarly to what we used in pre-NAV 2013 example, the manual creation of a Sales Invoice results in 130 change log entries to be created. It also catches the Release action for the status change from Open to Released:
Figure 5 – Change Log Entries in NAV 2013 – Captures Code Related Changes
You may have also noticed that the Old Value is now called Old Value (Local) – the Change Log in NAV 2013+ now stores the Option type fields as integer values instead of the formatted option string and then it evaluates this dynamically on the page using the …(Local) field. This allows for change log readability with option fields when in a different language but may introduce readability issues if you’ve integrated change log externally (i.e. user audit reports).
Just like in our prior example, systems may be integrated or automated through the use of codeunits and/or external services (i.e. Web Services) that then in-turn create a Sales Invoice in the system. When running the same set of code in a NAV 2013+ environment, we can see the change that can cause performance issues in your environment:
Figure 6 – NAV 2013 Change Log Entries with code related creation of a Sales Invoice
The same example generates 129 change log entries (one less than the manual example because the Release action was not called in code). As you can see, the new change log system logs every change in the system as opposed to the explicit calls and/or user changes in the system. In some environments, this change could log many additional entries in Change Log Entry table depending upon what other tools are used.
For a real-world example of this, some of our clients integrate with EDI and even have customizations that automatically generate Sales Invoices in the system. Because the change log system is called after an INSERT into the database, and it does not spawn a separate thread to log the change log, for each INSERT/UPDATE/DELETE in your system there is now a check to see if the table is logged, and if so, entries are added to the Change Log Entry table as changes are made to the system. This can increase the amount of time a transaction takes to process, and can cause the Change Log Entry table to balloon substantially in size.
The comparison for one client was substantial. In NAV 2009 R2 they would have around 400,000 change log entries that needed to be audited per month. In NAV 2013+, this turned into 8,000,000 entries – a 20 times increase in the number of rows! This increased processing time and caused the system to become fairly slow when processing month-end activities for this particular client.
Reverting the NAV 2013 Change Log to the “pre-NAV 2013” Change Log
During our upgrade process, we generally have a discussion on the impact of this type of change. Many end-users have grown comfortable with the change log design before NAV 2013, and also feel that this additional “noise” in the change log is not beneficial to identify changes made by end-users. What can be done to help this problem in which the investment cost to re-design the business processes to cut down on change log entries is not justifiable? Secondarily, what can be done to make it so that the user experience is also not negatively impacted? The answer for us was simple: revert the change log to the prior version behavior.
By default, NAV 2013, NAV 2013 R2, and NAV 2015 do not have the OnGlobal…() functions inside of Codeunit 1 (ApplicationManagement), you need to copy these from a prior version and make sure that the ID’s of each of these functions matches to the prior version (20 to 24):
Figure 7 – Adding OnGlobal…() functions back to ApplicationManagement
You must also take the GetTableTriggerMask() function from Codeunit 423 (Change Log Management) and copy it to the NAV 2013+ Change Log Management (or create your own codeunit) – you must also copy the LogModification() function from a prior version and rename it to something like LogModificationPreNAV2013() as shown below and above:
Figure 8 – Adding functions to Codeunit 423 (Change Log Management)
The final change is to disable the ChangeLogMgt codeunit calls in the OnDatabase…() actions in Codeunit 1 (ApplicationManagement) as shown in Figure 7.
After all of this has occurred, you will need to restart the service tiers and log into the system. Doing the same type of transaction (manual creation of a Sales Invoice), the Change Log captures 126 changes and does not include the Release action:
Figure 9 – Change Log Entries after reverting to pre-NAV 2013 design
When running the automated Sales Invoice creation we can see that the change log system did not capture the creation of the sales invoice after we have reverted the change log behavior:
Figure 10 – Change Log Entries after reverting to pre-NAV 2013 design (no code changes logged)
Microsoft Dynamics NAV Change Log Observations
The Change Log functionality in Microsoft Dynamics NAV 2013 and beyond is much more inclusive in terms of what it captures. No longer does it just capture user and/or code explicitly integrated changes, it also tracks changes made in code that have not been explicitly defined in the system. For most environments, this change will have little impact and the added visibility of the change log system is a welcome improvement.
For others though, the intent change of the Change Log can have a negative effect on performance and/or usability of the NAV system. Luckily for end-users, there is an option (so far up to NAV 2016) that allows for the system to be reverted to the prior behavior with minimal changes. Future versions of NAV may eventually deprecate this functionality (more specifically, the OnGlobal…() triggers), but nothing has been announced (to my knowledge) about the timing of this type of change.