CALCSUM showing incorrect value.

rahul_jnk
rahul_jnk Member Posts: 61
in NAV Three Tier
Hi all,

I have an Amount tendered field in a table which when computed using CALCSUM function shows a different amount than when I manually sum the columns. Anybody knows of any solution to this?

Regards,
Rahul.

Best Answer

  • David_Singleton
    David_Singleton Member Posts: 5,479
    Answer ✓
    rahul_jnk wrote: »
    Hi all,

    I have an Amount tendered field in a table which when computed using CALCSUM function shows a different amount than when I manually sum the columns. Anybody knows of any solution to this?

    Regards,
    Rahul.

    Go into object designer for this table (I think it's Trans. Payment Entry) , open Key sand show all columns then untick "MaintainSIFTIndex" for the key (or keys) that have "Amount Tendered" in the SumIndexFields column.

    Save the table, reopen it then retick the field and save again.

    This will drop the indexed view and rebuild it.
    David Singleton

Answers

  • NavNab
    NavNab Member Posts: 181
    Hello,

    Have you applied correct filters on your record before CALCSUMS ?
  • rahul_jnk
    rahul_jnk Member Posts: 61
    Hi NavNab,

    Yes filter is identical. Actually I wrote a code that loops through the records and sums the "Amount Tendered" field and one that uses CALCSUM("Amount Tendered") both are showing different values.
    In reality with that filter there are no records. Table.count shows 0 but CALCSUM is somehow getting value from somewhere.


  • NavNab
    NavNab Member Posts: 181
    Share your code, this will help understand the problem ;)
  • rahul_jnk
    rahul_jnk Member Posts: 61
    here it is..

    paymententry.SETRANGE("Statement Code",'xxxxx');
    paymententry.SETRANGE("Tender Type",'1');
    paymententry.SETRANGE("Z-Report ID",'');
    IF paymententry.FINDSET THEN REPEAT
    amt += paymententry."Amount Tendered";
    UNTIL paymententry.NEXT = 0;
    paymententry.CALCSUMS("Amount Tendered");
    MESSAGE(FORMAT(paymententry."Amount Tendered"));
    MESSAGE(FORMAT(amt));
  • NavNab
    NavNab Member Posts: 181
    try this 
    CLEAR(paymententry);
paymententry.SETRANGE("Statement Code",'xxxxx');
paymententry.SETRANGE("Tender Type",'1');
paymententry.SETRANGE("Z-Report ID",'');
IF paymententry.FINDSET THEN REPEAT
amt += paymententry."Amount Tendered";
UNTIL paymententry.NEXT = 0;

CLEAR(paymententry);
paymententry.SETRANGE("Statement Code",'xxxxx');
paymententry.SETRANGE("Tender Type",'1');
paymententry.SETRANGE("Z-Report ID",'');
paymententry.CALCSUMS("Amount Tendered");

MESSAGE(FORMAT(paymententry."Amount Tendered"));
MESSAGE(FORMAT(amt));
  • rahul_jnk
    rahul_jnk Member Posts: 61
    I tried the code the results are same. I suspect database corruption or something, since issue is only with calcsum.
  • pdj
    pdj Member Posts: 643
    Back in NAV4 I recall a bug causing this. It turned out the SIFT Bucket records were not deleted as they should. This was before NAV started using SQL Indexed Views.

    Which version and build no. are you using?
    Regards
    Peter
  • rahul_jnk
    rahul_jnk Member Posts: 61
    Hi pdj, I'm using NAV 2016 & SQL Server 2014 Enterprise Edition.
  • Yury
    Yury Member Posts: 59
    edited 2018-04-18
    pdj wrote: »
    Back in NAV4 I recall a bug causing this. It turned out the SIFT Bucket records were not deleted as they should. This was before NAV started using SQL Indexed Views.

    Which version and build no. are you using?

    Check SIFT table records manually for primary key of "Payment Entry" table.
    Regards,
    Yury
  • David_Singleton
    David_Singleton Member Posts: 5,479
    Answer ✓
    rahul_jnk wrote: »
    Hi all,

    I have an Amount tendered field in a table which when computed using CALCSUM function shows a different amount than when I manually sum the columns. Anybody knows of any solution to this?

    Regards,
    Rahul.

    Go into object designer for this table (I think it's Trans. Payment Entry) , open Key sand show all columns then untick "MaintainSIFTIndex" for the key (or keys) that have "Amount Tendered" in the SumIndexFields column.

    Save the table, reopen it then retick the field and save again.

    This will drop the indexed view and rebuild it.
    David Singleton
  • rahul_jnk
    rahul_jnk Member Posts: 61
    Thanks a lot David_Singleton. That worked.
  • David_Singleton
    David_Singleton Member Posts: 5,479
    rahul_jnk wrote: »
    Thanks a lot David_Singleton. That worked.

    Glad it fixed the problem. But ..

    Be aware that this shouldn't happen, even though it does. Most often it is because of someone playing directly on the SQL server. But sometimes Navision manages to do this some how.

    You should work to get to the cause of this, because it may affect other tables, and unfortunately the only way you typically find out is if you do what you just did to find this one. You can though write a SQL script that can go through and find any errors.
    David Singleton
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    Just a word of warning: - indexed views (the structure used by NAV to calculate SIFTs these days) cannot be updated/changed manually, in separation to the table. They are internal SQL Server structures maintained by the SQL Server, not by NAV. The only thing NAV is doing is asking the SQL Server to create one.

    If you have a discrepancy between the table and indexed view based on the table this usually means one of three things - the database corruption, the SQL Server fault, or the SQ Server hardware fault. Each one needs attention and remedy.

    Rebuilding indexed views is only addressing the visible artefact of some deeper problem with your SQL installation. I'd strongly suggest you to run DBCC CHECKDB against your database(s), apply the most recent service pack to the SQL Server, and if you are already on the latest SP keep the backup of the wrong database and raise the issue with Microsoft.

    What has happened shoud NOT happen in any circumstances, there is no user code which could contribute to such behaviour. What worse if it happened once it may happen again if you don't find and address the root cause. The next time it happens you may not notice and you may end up with incorrect amounts all over the database

    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • rahul_jnk
    rahul_jnk Member Posts: 61
    Thank you Slawek_Guzek & David for your valuable advice.
  • David_Singleton
    David_Singleton Member Posts: 5,479
    Yury wrote: »
    pdj wrote: »
    Back in NAV4 I recall a bug causing this. It turned out the SIFT Bucket records were not deleted as they should. This was before NAV started using SQL Indexed Views.

    Which version and build no. are you using?

    Check SIFT table records manually for primary key of "Payment Entry" table.

    This version of Navision with the sift error was released on April the First. :smile:
    David Singleton

Categories