Friday, May 31, 2019

Business Central: Change Log feature

By Steve Endow

Let's talk about the Business Central Change Log feature!

Just Search for Change Log

The Back Story

While poking around Dynamics 365 Business Central recently, I discovered that you can very easily change the main ID, or Number, of master records.

I can just open the Customer Card page, select a customer, type a different value in the Customer No. field, and just like that, Business Central will update (what appears to be) the primary key for the customer and all related records.  Whoa.

The same trick works for Vendors, Inventory Items, and even GL Accounts!  Just open the card page and change the Number field.  No biggie.  Apparently.

Coming from the Dynamics GP world, this is wild.



The concept of changing a customer ID, or vendor ID, or item number or GL account is nothing new. Customers do occasionally have the need to change IDs or GL accounts. That isn't what surprises me.

What surprises me is the idea that a user who is able to open the GL Account Card page can just change a GL account number, apparently without restriction. It's not tucked away in a Maintenance area or an Admin section.  It's just a typical field that a user can change.

Hey, let's play a little joke on the Controller!

I have yet to find a reason why this feature is so easily accessible in Business Central, but the good news is that the system appears to handle these changes in stride, so it doesn't appear to be an issue or a cause for concern, as far as I can tell.

If someone makes a change, it may cause a little confusion as you try and figure out where a record went, but searching by name is easy enough, and once the record is found, the Number can be sorted out or corrected if a mistake was made.


Track It

After I discovered this interesting feature, Erik Hougaard explained that if such master record Number changes are a concern, you can log them using the Business Central Change Log feature.

Interesting!

For those familiar with Dynamics GP, this is similar to the Dynamics GP Activity Tracking feature, but appears to be much more granular, as it allows change tracking at the table and field level.


In Business Central, search for "change log" and select Change Log Setup.

Click the slider to activate Change Log

This page gives you two simple options.  First, click on the slider to activate change log.

Then click on the Setup link, and click on the Tables link that appears below Setup.

Setup -> Tables

This will open the Edit Change Log Setup Table List page.  If you're curious, there are 1,494 tables listed (for the April 2019 release, as of May 2019).  Given this, you'll want to use the Search field at the top of the window to filter the list, rather than try to scroll through the tables.

Use the Search field to filter the table list

When I type 'customer' in the search field, the list quickly shrinks to a manageable size, and I can easily see the Customer table.

Choose Log Modification -> Some Fields

For my example of logging changes to Customer No. values, I want to Log Modifications to the Customer table, and I only want to monitor changes to one field.

After selecting Some Fields, an ellipsis button will appear in the Log Modifications field.

Select the Fields to Monitor

The Edit Change Log Setup Field List page will appear, listing the 103 available Customer fields.

Select the fields you want to log when they are modified

I'm going to select the No. and Name fields, so that I can see if anyone modifies the customer number or customer name--which I assume should be infrequent.

After you make your selections, they are saved automatically, and you can click on the X in the upper right corner of the page to close the Field list.  You can then close the Table list.

Now let's test the Change Log.

Change the Customer No. value

First, I change the Customer No field value, adding "TEST" to the end.  Business Central then asks me if I want to rename the record, and I click Yes.

Are you sure?

I then change the Customer Name value.

Number and Name have been changed

After tabbing off the Name field, the change is automatically saved.


Monitor Changes

Now let's see what was logged.  I click on Search and type "change log", then select Change Log Entries.

Search is your friend

The Change Log Entries are then displayed.

Change Log Entries

There are two key items I'd like to point out on the Change Log Entries page.

First is the Search field at the top.  In a real environment with Change Log enabled, you may have hundreds or thousands of entries listed, depending on how much logging you have enabled.  Make sure to utilize the Search field to quickly filter results to find what you are looking for.

Second is that you will quickly notice that the table of data shown on the Log Entries page is so wide that it's difficult to scan the data while having to scroll right and left across the columns.

Open the Log Entries in Excel

To help with that, you can click on Page -> Open In Excel to view the log entries in Excel.  This makes it much easier to resize or hide columns, sort and filter, and scan the data.

User, Record, Old Value, New Value

In Excel, I can hide and resize several columns, and easily see the changes that were made to the record.


Go Easy

If you are thinking about using the Change Log feature, use it judiciously.  Consider that the logging will consume some amount of system resources, and it will also fill the database table where the Log Entries are stored.

I would recommend having specific, detailed use cases for your logging.  Perhaps you've noticed that phone numbers are disappearing from some Customer records.  Log changes to the phone number fields on the Customer table.

Maybe a few invoices were apparently deleted, and you can't figure out how or who or why.  Log that.

Don't log inserts, modifications, and deletes to 75 tables "just in case" you might need to see what happened in 2 years.

I covered this topic in my post discussing the Dynamics GP Activity Tracking feature.  The same general questions and concerns I discussed for Dynamics GP are just as relevant for Business Central Change Logging.


Remove Old Entries

On the Change Log Entries page, under Actions, there is a Delete Entries button.

Clean up those log entries occasionally

The Change Log Delete window lets you specify two criteria for selecting log entries to delete.  By default, it shows Date and Time, and Table No.  If you want to use different criteria, you can click on each drop down and choose from 22 different values.

Two selection criteria for Change Log Deletion

Note that the Date and Time value can use the Business Central filter criteria syntax, which is discussed here.

The ".." in front of the date means "up to and including".  So the value shown of "..05/31/18 11:59:59 PM" will select all log entries up to and including those logged on May 31, 2018 at midnight.  The date value defaults to 1 year ago, and the deletion process will warn you if you attempt to delete records less than 1 year old.

After setting your criteria, you can click OK to delete the selected log entries immediately, or you can click on the Schedule button to schedule the deletion for a later date and time.


The Business Central Change Log feature looks pretty cool, and if used properly, looks like it could be very handy to track down issues or oddities that may occur with your data.


If You Need More

After posting this blog entry on Twitter, I received a reply pointing me to this Business Central AppSource Extension:

Change Tracking by Silverware, Inc.

It appears to be a more comprehensive change tracking solution that is integrated into Business Central pages, so you can easily view changes that have been made to a record while working on that record.  Rather than having to sift through a pile of records in a centralized change log, you can simply open the record you care about, and view all changes for that record.

While it looks neat, the ease of use and clean integration with Business Central might encourage one to track all activity in dozens of tables.  In that case, I would definitely want to ask Silverware about how the data is tracked, the performance implications of tracking 25+ tables, and the storage implications of all of that tracking data.



Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics GP, Dynamics 365 Business Central, SQL Server, .NET, Microsoft Flow, and PowerApps.

You can also find him on Twitter and YouTube





No comments:

Post a Comment

All comments must be reviewed and approved before being published. Your comment will not appear immediately.