One of the most difficult errors in NAV to troubleshoot is: “The transaction cannot be completed because it will cause inconsistencies in the G/L Entry table”. In a standard, unmodified database it is usually related to tax/rounding issues. And in customized databases, it can be all sorts of issues.

I have seen all kinds of workarounds for figuring out the data/transaction that causes the inconsistency. Most of them have been way to complicated, doing all kinds of modifications to the posting codeunit, or looking up uncommitted records through sophisticated SQL queries. Until the post by Rashed Amini back in 2007 (can be found here on MIBUSO), there was no real good solution to it.

Unfortunately there is still a lot of developers that are not aware of this priceless trick, so let’s look into how he solved the issue of looking at the transactions that caused the inconsistency.

First of all, this uses the Single Instance feature of codeunits. Single Instance for codeunits is basically reusing the variables for multiple instances of the object. The official documentation puts it like this:

When you set this property to Yes on a codeunit, all codeunit variables that use this codeunit use the same instance. That is, all codeunit variables of this codeunit use the same set of internal variables when the code is running on the same client. The codeunit remains instantiated until you close the company.

The following example shows how you can use the SingleInstance property.

Two forms can connect to the same codeunit.

On Form1:

Codeunit1.SetNumber(100);

On Form2:

Number := Codeunit1.GetNumber(); MESSAGE(Format(Number));

The SingleInstance property in Codeunit1 is set to Yes. Form1 calls a function on Codeunit1 and sets the parameter to 100. Codeunit1 saves this parameter in a local variable. Form2 is now able to get the parameter value (=100) from Codeunit1. A message is displayed.

The purpose of this, is that an instance of the object can set a value of variable, that can be accessed from another instance of the same object (codeunit).

Another important point for single instance codeunits, is that they are not under the rules of the rollback of transactions. So even if an error happens, you can still read the values that were set in the previous rolled back transaction. This is where it becomes handy for as our little detective to finding the G/L inconsistencies.

Lets look at Ahmed’s actual code for the single instance codeunit:

OBJECT Codeunit 50000 Single Instance CU
{
OBJECT-PROPERTIES
{
Date=10/11/07;
Time=[ 2:50:02 PM];
Modified=Yes;
Version List=MOD01;
}
PROPERTIES
{
SingleInstance=Yes;
OnRun=BEGIN
IF NOT StoreToTemp THEN BEGIN
StoreToTemp := TRUE;
END ELSE
FORM.RUNMODAL(0,TempGLEntry);
END;

}
CODE
{
VAR
TempGLEntry@1000000000 : TEMPORARY Record 17;
StoreToTemp@1000000001 : Boolean;

PROCEDURE InsertGL@1000000000(GLEntry@1000000000 : Record 17);
BEGIN
IF StoreToTemp THEN BEGIN
TempGLEntry := GLEntry;
IF NOT TempGLEntry.INSERT THEN BEGIN
TempGLEntry.DELETEALL;
TempGLEntry.INSERT;
END;
END;
END;

BEGIN
END.
}
}

The code you need to insert into codeunit 12, is in the function FinishCodeunit():

FinishCodeunit()
WITH GenJnlLine DO BEGIN
IF GLEntryTmp.FIND('-') THEN BEGIN
REPEAT
GLEntry := GLEntryTmp;
IF GLSetup."Additional Reporting Currency" = '' THEN BEGIN
GLEntry."Additional-Currency Amount" := 0;
GLEntry."Add.-Currency Debit Amount" := 0;
GLEntry."Add.-Currency Credit Amount" := 0;
END;
GLEntry.INSERT;
//MOD01 Start
SingleCU.InsertGL(GLEntry);
//MOD01 End
IF NOT InsertFAAllocDim(GLEntry."Entry No.") THEN

To use the “detective” you first run the codeunit, that will set the variable StoreToTemp to TRUE, and instantiate the codeunit as single instance.

Next you run your posting that causes the inconsistency error. NAV will error the same way as it originally did, but now you have also written the GL Entries to our single instance codeunit, where they are stored in a temporary table.

By running the codeunit again, you will now display a form with the temporary GL entries, and it will be easy to see why the transactions do not balance.

Kudos go out to Ahmed Rashid for this great tip, and hopefully this blog post can get more people to know about this cool trick. I have it in my toolbelt, and have used it multiple times to troubleshoot with.