Thursday, June 18, 2009

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

10 comments:

Janakiram M.P. said...

Mariano!

Great Post. It gives a complete detail on the Check Links. This was not available before anywhere and many Dynamics GP Professionals would love to read this for their knowledge upgrade. Really Great Stuff!!!!

victoriayudin said...

Mariano,

This is awesome stuff - thank you!

Victoria

Steve Chapman said...

Mariano,

This is fantastic. You rounded out my knowledge of the CheckLinks procedures. It has always been a slight mystery to me.

Mariano Gomez said...

All,

Thanks very much for the feedback on the article, and stay tuned as I may decide to post a follow up.

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

Amber said...

Thanks so much for this!

Honeylyn said...

Hi Mariano,
My question is when we run checklinks (due to multicurrency activation for existing companies), when we tried to upload safe pay files, it's now showing all our historical voids. I know that voided checks are in a separate table. But after running checklinks, did the system thought we were restarting Safe Pay? Is this going to affect our Bank Rec or anything? Please advise.


Thanks!!!
Honeylyn

Mariano Gomez said...

Honeylyn,

It would help me to understand what tables did you include in your checklinks.

Honeylyn said...

I only did checklinks on Multicurrency Setup. But it went through all tables afther that. Thanks.

Mielyn Nollase said...

Honeylyn: Yes, it looks at all possible setup and transactions tables that hold either a currency ID or curerncy index field

Mariano: I think starting on GP 10.0 (not sure on 9.0), Checklinks actually creates records in the POP module for orphaned receipt line items (as you can see I have not fully tested this yet). However, what we do is recommend clients to delete such recreated transaction headers and then re-enter them.

Thank you for the details you provided... It brings me back to when I started supporting GP 7.5 (and at that time, GP 7.0 and GP 6.0 are still supported).

Anonymous said...

surely if it was a half decent system, all the issues it picks up anbd fixes shoudl never exist in the first place !!!!!