How to see why you are getting CONSISTENCY Error

ara3nara3n Member Posts: 9,255
edited 2011-10-05 in NAV Tips & Tricks
This is a trick I use whenever I do try to solve and see why you the db is getting CONSISTENT Error.

I create the following CU.
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.
  }
}



And in CU 12 I add the following Code in 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

Once you've made the changes. You run the SinleInstanceCU Once.
Then do what ever you do to get the consistency error.
Then Run the SingleInstanceCU again.
You'll see a list of GL lines Inconsistency. You will see why the transaction is not balanced.
Ahmed Rashed Amini
Independent Consultant/Developer


blog: https://dynamicsuser.net/nav/b/ara3n
«13

Comments

  • kinekine Member Posts: 12,562
    Good tip Ara3n =D>
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • kirankumarkirankumar Member Posts: 29
    This is really works ...

    Can you please let me know the 'Kitting' Functionality Introduced in Nav-5.0.

    I am really interested to know Such New things helps lot...

    Regards
    KiranKumar
    Hi
  • Alex_ChowAlex_Chow Member Posts: 5,063
    Very nice! Beats the alternative of turning off the CONSISTENT function and trying to figure out what happened.
  • WaldoWaldo Member Posts: 3,412
    Nice nice =D> .
    Tested and approved!

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • ara3nara3n Member Posts: 9,255
    Alex Chow wrote:
    Very nice! Beats the alternative of turning off the CONSISTENT function and trying to figure out what happened.

    Yep, hopefully you are doing that in a backup db.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ara3nara3n Member Posts: 9,255
    edited 2007-12-22
    Waldo wrote:
    Nice nice =D> .
    Tested and approved!

    I'm glad you are approving this one. :wink:
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Alex_ChowAlex_Chow Member Posts: 5,063
    ara3n wrote:
    Alex Chow wrote:
    Very nice! Beats the alternative of turning off the CONSISTENT function and trying to figure out what happened.

    Yep, hopefully you are doing that in a backup db.

    Life would suck if I didn't...

    Nonetheless, you still rock. :wink:
  • WaldoWaldo Member Posts: 3,412
    ara3n wrote:
    Waldo wrote:
    Nice nice =D> .
    Tested and approved!

    I'm glad you are approving this one. :wink:

    Well, I've got to keep being objective :mrgreen:

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • SavatageSavatage Member Posts: 7,142
    Question
    I can impliment this but the users license gives them an error that they can't use this codeunit. (I had to give it a diff unused #) that is now out of our range.

    would adding indirect permissons like
    TableData G/L Entry=rimd help? or could it be inserted into another codeunit that is accessible (That would be sloppy tho I think)?
  • ara3nara3n Member Posts: 9,255
    No you don't need the permission for GLEntry.
    The code only inserts into temporary table not actual table. As long as the user has indirect permission to post glentry, this would work.

    As far as not in the license, this 50000 range, I'm assuming most customer start from that range to purchase CU.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • SavatageSavatage Member Posts: 7,142
    Now sure if the purchase structure has changed over the years but by puchasing 100 Reports didn't get us any more codeunits. Since it was new to us - we were more fixed on Reports, forms, tables, dataports. Puchasing a decnet number for codeunit spots 50000+ was not a big concern since we didn't even know what it was. And many of the things needed can be done using reports. So our seller never really mentioned to us that we could buy codeunit #'s (50000+) probably for our own good too :wink:
  • ara3nara3n Member Posts: 9,255
    I see.

    I had the same issue with another customer as well.
    It was taking too long to order the CU. So I used old CU that was used in 2.6 in 100 range but did not longer exists in 4.x but the client has still license to them. :D


    For example Department and Project tables are no longer there but you can import a 2.6 fob object and then use them as other codes.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • krikikriki Member, Moderator Posts: 9,094
    Alex Chow wrote:
    Very nice! Beats the alternative of turning off the CONSISTENT function and trying to figure out what happened.
    With SQL, I have another trick:
    I start the debugger with a breakpoint just before the error. Then I check the T17-table in another session (of course in a test DB!). The advantage of seeing uncommitted records...
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • davmac1davmac1 Member Posts: 1,283
    I have a consistent problem and I believe I know exactly what is causing it. The sales tax calculation is a penny off versus the outstanding amount on one of the lines.
    Does anyone know a fix for this?
    The invoice rounding is set to .01

    I think the problem is caused by the sales tax routine calculating the sales tax for the entire sales invoice, then adjusting the sales lines taxable amounts to make them balance, but not making a corresponding adjustment to the outstanding amount.
  • ara3nara3n Member Posts: 9,255
    On sales Order Hit F9 and on invoicing tab change the tax by 0.01 cent.
    There is a setup allow tax differnce to be checked.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • davmac1davmac1 Member Posts: 1,283
    I tried adjusting the amount, but I still get the error.
    I have the tax difference checked.

    One of the sales lines has Outstanding amount = 27.06 and the Amount Including Tax = 27.07.
    Changing the tax in the Invoicing tab does not change this amount.
    Do I need to change the amount inclusing tax on the sales line itself by editing the line?
  • ara3nara3n Member Posts: 9,255
    Changing the Tax amount in Sales Order Statistic does change Amount including tax on the sales line.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • davmac1davmac1 Member Posts: 1,283
    I am only seeing a total taxable amount for each tax type, no breakdown by individual sales line is there a way ti get to that?
  • ara3nara3n Member Posts: 9,255
    on statistic form you see in the detail lines the breakdown of each joursdiction. you can change the tax amount of each line.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • davmac1davmac1 Member Posts: 1,283
    But the tax lines are for the order. I don't see a breakdown per line item.
  • ara3nara3n Member Posts: 9,255
    you see a breakdown by jurisdiction. what state are you charging tax and how many lines do you see on statistic form? And have you tried to change the tax amount for one of the lines by 1 cent?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • davmac1davmac1 Member Posts: 1,283
    3 lines - 1 per jurisdiction
    The tax amount on the sales line is non-editable.
    I could change it as a developer, but that means I cannot turn over future problems to the user.
    This particular order had a partial shipment, so I posted the invoice only, which appears to have fixed the problem. The remaining items are resources which did not appear on the shipment and the sales tax amount now reconciles with the outstanding amount.
  • ara3nara3n Member Posts: 9,255
    On the statistic Form F9 the user can change the Tax Amount. Click on Invoice tab in statistic form and then on the detail lines where it shows lines for every jurisdiction you can change the Tax amount field. Have you tried to change it by 1 cent?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • davmac1davmac1 Member Posts: 1,283
    Yes I did. I still had the error. I then tried posting the invoice without selecting the shipment and invoice problem and that posted and it also adjusted the outstanding amount to match the amount including tax on the sales line.
  • anwar0206anwar0206 Member Posts: 11
    =D> Really a great JOB.
    Regards,
    ~SA
  • ara3nara3n Member Posts: 9,255
    anwar0206 wrote:
    =D> Really a great JOB.

    thanks :D
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • David_SingletonDavid_Singleton Member Posts: 5,479
    ara3n wrote:
    This is a trick I use whenever I do try to solve and see why you the db is getting CONSISTENT Error.

    I create the following CU.
    OBJECT Codeunit 50000 Single Instance CU
    ...
    
    


    And in CU 12 I add the following Code in function FinishCodeunit

         ...
         //MOD01 Start
          SingleCU.InsertGL(GLEntry);
          //MOD01 End
          IF NOT InsertFAAllocDim(GLEntry."Entry No.") THEN
    

    Once you've made the changes. You run the SinleInstanceCU Once.
    Then do what ever you do to get the consistency error.
    Then Run the SingleInstanceCU again.
    You'll see a list of GL lines. You will see why the transaction is not balanced.

    I normally use one of the other methods, i.e. create a journal to see what will be posted, or remove the CONSISTENT command to see what was going on. I just recently had a new consistent error, and decided to try this tip, and I must say that its simple, it works and its brilliant.

    Well done Ahmed =D>
    David Singleton
  • ara3nara3n Member Posts: 9,255
    :) Thanks.

    Leaving the code on customer db won't hurt performance because the function will simply exit out if you haven't run it manually from object designer.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • David_SingletonDavid_Singleton Member Posts: 5,479
    ara3n wrote:
    :) Thanks.

    Leaving the code on customer db won't hurt performance because the function will simply exit out if you haven't run it manually from object designer.

    Yes I noticed this, but in the client didn't want modified code in the live db (that would require audit testing etc). Anyway great tip.
    David Singleton
  • jaustinjaustin Member Posts: 21
    I'm attempting to create the code unit that ara3n suggested but am not sure where/how some of this code can be put into the code unit (designer mode). Example: How or where do you enter the "TempGLEntry@1000000000 : TEMPORARY Record 17;" ??? I don't see a way to enter this in the designer.

    I've seen other postings with code displayed in the way that ara3n has put it in his posting. How do you get a code unit to display all of the properties and code the way that has been posted here? Can this code be imported some how, as is?

    Thanks,
Sign In or Register to comment.