AX Technical Tip: Prevent Headaches with Data Consistency Checks

AX Technical Tip: Prevent Headaches with Data Consistency Checks

 June Blog Series: A Case For…

Insights From: Doug Payton (Fullscope Technical Consultant)

Microsoft Dynamics AX includes classes that check data consistency in most of the modules, and it provides a job to run all or a subset of each one. This is also sometimes called a "sanity check," which ensures the data for tables are in a valid state (for example: tables with lines each point back to a valid header, etc.) or notes that a situation shouldn't last for long (for example: sales orders on hold). It ensures that data corruption or inconsistencies (by the system or by people) can be caught early before effects are felt downstream.

From the menu, run System administration > Periodic > Database > Consistency check.

Each entry or sub-entry ties directly to an Application Object Tree (AOT) class, which by convention has a name ending in "ConsistencyCheck". The accounts receivable module class, for instance, is "SalesConsistencyCheck". Initially, the code searches all classes for any that derive from "SysConsistencyCheck". When there are sub-entries, for instance with Customer, each one is further derived from the class for that module.

For example: The class "CustConsistencyCheck" has two classes that derive from it, which follow the naming convention for subclasses.

  • "CustConsistencyCheck_LinkTable " for checking Relation tables
  • "CustConsistencyCheck_BOE" for checking Bills of exchange

Dynamics AX calls the method treeParentClassId() to determine which classes are subordinate to which. (The default is a parent of the main class SysConsistencyCheck.) It also has a Module dropdown that restricts the tree to just that module (where the "module" is one of the classes that says its parent is "SysConsistencyCheck"; entries in the first level of the tree below "Programs").

When you run the consistency check from the System administration's Periodic menu, the "SysConsistencyCheckJob" class, which uses the RunBase framework, is running. It sets up the form and then calls the "SysConsistencyCheck" classes selected.

The output is an InfoLog with all consistency errors and warning:

The "SysConsistencyCheck" class has some hook methods that can be overridden to allow the developer to add code at certain points in the process. It also has methods that do the consistency check for a particular table (kernelCheckTable()), an individual record (kernelCheckRecord()) or a subset of records based on a query (kernelCheckRecords()).

To check a record in a table, the kernelCheckRecord() method first calls the table's validateWrite() method on the record. Then, it goes through each non-system field in the record and calls the table's validateField() method on it.

As a side note, this exposes some problems with standard AX code. Here are some errors generated by the consistency check:

If validateField() and validateWrite() are called on an unchanged record, nothing is modified. For some tables, that method makes an incorrect assumption. Keep this in mind when you read the InfoLog and when you write custom validation methods.

The "SysConsistencyCheck" class extends "RunBaseBatch", meaning that any individual sub-class could be called by a menu item and run individually, interactively or in batch. In this case, a main() method needs to be written, which will be explained later. Like all "RunBaseBatch" sub-classes, override the run() method to get the work done.

To get an idea of what a sub-class might typically do and how to create classes for customized tables, take a look at the "SalesConsistencyCheck" class.

SalesConsistencyCheck extends SysConsistencyCheck. The next few methods are required for each SysConsistencyCheck subclass.

The description() method returns the text to use on the Consistency check form next to the checkbox for this class.

The helpText() method returns the text as help text for this class (below the tree of checkboxes on the form).

The executionOrder() method returns an integer indicating the relative position for this module on the form. If this is not overridden in a new class, it will default to 999. Look at this method in the other consistency check classes to see what should be returned in order to appear in a particular location.

If this class needs to be run individually from a menu item, create a main() method similar to the "RunBaseBatch" sub-class. However, the class created with new() should be a "SysConsistencyCheckJob" class, and the passed parameter is the class ID that should be run. It will run that class and any of its sub-classes.

The run() method does the work. In the SalesConsistencyCheck class, it calls two methods to deal with the SalesTable and SalesLine tables. Then it checks the SalesParameters table. The methods that are called determine if a particular record in those tables needs to be checked.

For example: If the sales order is associated with a project that is in a status that no longer allows for the creation of sales orders, the order record is skipped.

After validating a record and all its fields, the table method consistencyCheckFix() (if it exists) is called for the record. The default behavior of kernelCheckRecord() calls this method on every record checked regardless of the value of the Check/Fix dropdown on the main form. Some consistency check classes do look at this value before fixing a record, but the default behavior doesn't check it before calling consistencyCheckFix(). There's even a SysConsistencyCheck() method to get the value.

The consistencyCheckFix() method does general fixing for the record, regardless of data consistency. However, of all the tables in Microsoft Dynamics AX 2012, only a few have this method. Some of the tables only send out a more detailed InfoLog. So, it's not something that AX does much of itself, and it's not usually something that should be corrected in code (that is, without human input) unless it's a very simple fix. Note that this method will need its own ttsbegin/ttscommit because the consistency check does not have one.

Also, it's a second method call for every record checked for that table. So, unless there's a very general fix that can be done, and done often, avoid creating one.

Since the consistency checking framework makes use of the validateWrite() and validateField() methods on tables, it gives those methods something of a higher profile and gives more reason to properly update these methods when modifying tables. When creating new tables, consider making data consistency one of the to-do items in the project. Written properly and run consistently, it may prevent bigger headaches down the line.

If your manufacturing company is looking to eliminate headaches with its current ERP system, contact the experts at Fullscope to learn how Microsoft Dynamics AX ERP can help your business.

Data, ERP, Data Consistency Checks, Microsoft Dynamics ERP