How does Check Links work?

This is a rather complex question. To understand how Check Links works one needs to step back a bit into the history of Microsoft Dynamics GP.

In its origins, Dynamics GP was conceived with the idea of running on multiple operating systems (Windows, Mac OS, Novell Netware), and "database" platforms, namely Ctree and Btrieve. The term database is a misnomer because in reality Ctree and Btrieve at the time were nothing more than Index Sequential Access Method (ISAM)-based file systems, with Btrieve being at best a record manager environment that could run on Netware and Windows. However, ISAM-based systems lacked referential integrity capabilities.

This brings me to Microsoft Dexterity -- the Microsoft Dynamics GP development environment. Within a Dexterity application, such as Dynamics GP, developers can define table structures and relationships that the Dexterity Runtime Engine then replicates into physical files depending on the file system platform. This table replication could be controlled via the DEX.INI file, with the FileHandler key.

FileHandler = Ctree

Since ISAM file systems had no ability to support referential integrity, the Dynamics development team had to create their own routines to analyze and maintain data integrity. The routines that were put into place were Shrink, Rebuild, Check Links, and Reconcile. In fact, back in the 80's and 90's if you called Great Plains Technical Support, the first thing you were always asked to do was to run a Shrink-Rebuild-Check Links-Reconcile.

The Shrink operation would remove any blank spaces between records, which was very common in ISAM-based platforms. Since ISAM files lacked the ability to compress files, records removed from GP's physical files would create logical gaps in the file itself, hence space was never released.

NOTE: Btrieve was modularized starting with version 6.15 and became one of two database backends that plugged into a standard software interface called the Micro-Kernel Database Engine. The other product is Scalable SQL, a relational database product that uses Structured Query Language, otherwise known as SQL. After several new versions were released the company was renamed to Pervasive Software and they now sell the product as Pervasive P-SQL. All three platforms were supported by Dynamics at some point in time.

The Rebuild operation would "attempt" to recreate table structures and place default records in them if needed. It was not uncommon, for example, for palette files (the predecessor of today's menus) to become corrupt and for system administrators to have to run a Rebuild operation to restore the default records in them.

Check Links however, is a different animal, so to speak. Check Links are entire procedures designed to analyze and remove any suspect records based on how tables are related in the application's dictionary. Hence, at the time it was highly recommended to take full backups of the physical files before running this maintenance utility. Check Links performs validations to make sure every record that is part of an atomic document(for example, a customer record, a purchase order, a sales order, an RM transaction, etc.) is valid within the context of that document. The check links algorithms sweep through the records in every table that is not the main table of a table group, then compare to the records in the main table. If there is any missing corresponding record in the main table, for the records currently being processed, these records in the table being processed are removed. For example, invoice line items cannot subsist without an invoice header record; invoice distributions, cannot subsist without a header record. But Check Links only verifies integrity. Accuracy is carried out by the Reconciliation processes. Hence, after running a Check Links, it is always recommended to run a Reconcile.

With the release of SQL Server, the development team decided that it was more cost efficient (time, effort, and money) to live with the table relationships and the routines to maintain data integrity within the Dynamics dictionary all the while creating the extensions needed to validate certain operations required in the Dynamics code only for SQL. For example, it was not unusual to find source code that was designed to work in one way if running on Ctree/Btrieve versus calling a stored procedure if working on SQL Server -- this is also known as selective code. After all, Ctree and Btrieve were still supported up until v7.5 and the code needed to work across all platforms supported. It was a lot easier to enhace parts of the application to support SQL Server rather than rewriting the code from scratch for the SQL platform. Introducing SQL Server also needed to be transparent to the 1000's of ISVs who had integrating applications and add-ons to GP that wanted to make their code available for SQL Server without major rewrites.

Back to Check Links... SQL Server also allowed the development team to create more efficient routines to analyze data integrity. Portions of Check Links, especially those related to table groups, have been migrated to SQL stored procedures. In fact, the following list of stored procedures execute Check Links operations and are called from the Dexterity Sanscript code based on the selected table group.

glCLAccountMSTR
pmCLApplyToOPENOPEN
pmCLApplyToWORKOPEN
pmCLDistributionWORKOPEN
pmCLHistoryLogicalTableGroup
pmCLKeyMSTR
pmCLManualPaymentWORK
pmCLMCRevaluation
pmCLMCTransactions
pmCLMoveFullyApplied
pmCLPaymentWORK
pmCLSchHdr
pmCLSchLine
pmCLTaxInvoices
pmCLTaxWORK
pmCLTransactionLogicalTableGrp
pmCLTransactionOPEN
pmCLTransactionWORK
rmCLAppliedOPEN
rmCLCashWORK
rmCLCommissionWORK
rmCLDistributionWORK
rmCLHistoryLogicalTableGroup
rmCLKeysMSTR
rmCLMCRevaluation
rmCLMCTransaction
rmCLRMOPEN
rmCLSalesWORK
rmCLSchHdr
rmCLSchLine
rmCLTaxWORK
rmCLTransactionLogicalTableGrp

So what happens to non-table groups? Certain tables are managed from the Dynamics interface with Sanscript code, certain other tables are not even checked as they may be self contained.

[Edit] David Musgrave also adds that you must "Be aware that Check Links can delete records it feels are damaged or orphaned. I have heard of valid data being removed. This has been known to occur with some ISV products which store data in GP tables, but Check Links does not recognise them.". The moral of the story: backup, backup, backup all your data!

Related Articles:

Understanding how Microsoft Dynamics GP works with Microsoft SQL Server - David Musgrave @ Developing for Dynamics GP.

Understanding how Microsoft Dynamics GP works with Microsoft SQL Server continued - David Musgrave @ Developing for Dynamics GP.

More Dex.ini Settings! - This Site

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com