Consistency Check Tool in Dynamics AX 2012

Tables in Dynamics AX 2012 are divided into different categories: Main tables such as Customers, Ledger Accounts, Vendors, Items, etc, Transaction headers such as Sales headers, Purchase order headers, etc, and transaction details such as Sales lines, PO lines, etc. These tables are all related to each other through keys. For example when a sales order is created for customer A123, the customer, sales header and sales line are all linked to each other.

Occasionally one or more of these transaction records can be abandoned, meaning the parent data was deleted but the transaction still exists. We call these orphans because they exist in the database but are not connected to any other record. While these orphans by themselves cause no problem, they take up space in the database and given enough time, a lot of these orphaned records can cause performance issues.

Dynamics AX 2012 has a tool to spot and remove these orphans. It’s called the Consistency Check tool. It basically runs through the entire database and locates transaction records that do not have any parent record associated with them. The Consistency check tool is located under System Administration > Periodic > Database > Consistency Check

AX Consistency check

When running this tool, you will first be presented with a series of options such as which modules you would like to check for orphans in, what to do with orphans found (check or fix) and a starting date to perform the search on (if you run this tool every 90 days you can select a day just 90 days back in order to speed up the search process).

Dynamics AX Consistency check

The tool will take an hour or more to run, be sure to run this after hours.

Dynamics AX

AX does a great job of keeping track of transaction data and removing automatically when any parent data is removed, but, as with any ERP, different scenarios may come up in which records are left orphaned. Using the Consistency Check tool on a regular basis is a great way to clear these records and keep your system running smoothly.

Can you recover a deleted record in Dynamics CRM 2011?

Unfortunately, the answer is no, unless you restore the database from a backup which is not really practical for a single deleted record.  However, you may be able to recover some data if you have auditing turned on and follow the steps in this Microsoft blog below.

You can also use a free tool to recover data (again if auditing is turned on).

If record deletion is a problem:  One solution is to remove “delete” privileges from most users and encourage users to deactivate records.  This way the record can still be reactivated.  If space is an issue, you can create Bulk deletion jobs that delete inactive/deactivated records every X days.

View relationship between Sub-Ledger and GL in Dynamics GP

How many times do you wish that there was report that would show you the link between a sub-ledger and the GL? Here is a little trick that will help you see some of this sub-ledger information through a GL Smartlist. It can especially come in handy when reconciling your Accrued Purchases account as there is not a routine to help you through this process.

Here are the steps:

Go to Microsoft Dynamics GP > Smartlist. Expand the Financial Folder and then Expand the Account Transactions folder.

Click on the default Smartlist indicated by an asterisk.

Sub-Ledger and General Ledger Smartlist

Click on the Columns button and search for the word Originating. This will bring you to fields such as Originating Master ID, Originating Master Name, Originating Document Number and so on.

These will be the originating Master Record and document information that generated each Journal Entry. The Originating Master ID will be either the Customer ID or Vendor ID depending on which module the Journal Entry originated in. When reconciling your Accrued Purchases account for instance, you can restrict the list to the Accrued Purchases GL Account and then export this data to Excel.

Then take your Credits minus your Debits to get the net changes and subtotal by Originating Master ID to get your Vendor received not invoiced totals.

This list can then be compared to the Rec’d/Not Invoiced Report subtotaled by Vendor. By doing this, you will easily be able to determine what vendor or document is causing issues and more easily resolve the discrepancy.

Sub-Ledger and GL in GP

Microsoft Dynamics CRM 2011 Activity Feeds

We have gotten quite a few inquiries regarding what Activity Feeds are and how to best use them to satisfy specific business needs.

Activity feeds are not meant to replace face to face conversation, emails or automated workflows, although you can trigger workflows off activity posts and vice versa. Instead, they are meant to increase collaboration between teams working with the same account, opportunity, case, or custom entity etc.

Much like Facebook News feeds, Microsoft CRM Activity feeds allow you to:
1. Stay up to date or follow users or records (leads, accounts, opportunities, cases etc.) that are important to you.
2. Keep others informed with your posts regarding specific records.
3. Communicate in real time. Your posts become available on followers Walls immediately.

Note: In order to see activity feeds for a specific record, the user must have appropriate permissions to that record.
This Microsoft Getting Started Video actually provides a good overview and business case of how to use Activity Feeds (if you can tolerate the Microsoft background music).

Although I believe Activity Feeds can be very useful, I do caution the amount of “noise” it can create within the organization. It can be effective if a user is only following the records or users that they have a vested business interest in. Activity Feed rules also have some limitations such as not being able to create new rules for an entity. Furthermore, you cannot tweak existing rules, you can only activate or deactivate out of the box rules.

The Activity Feeds managed solution is pre-installed and configured on all Dynamics CRM Online Organizations provisioned after the service update in Nov 2011. For CRM 2011 On-premise and for CRM Online Organizations provisioned prior to service update in Nov 2011, the Activity Feeds managed solution can be downloaded from the Microsoft Dynamics Marketplace.