CALCSUM showing incorrect value.

rahul_jnk

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.

David_Singleton

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.

NavNab

Hello,

Have you applied correct filters on your record before CALCSUMS ?

rahul_jnk

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

Share your code, this will help understand the problem

rahul_jnk

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

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

I tried the code the results are same. I suspect database corruption or something, since issue is only with calcsum.

pdj

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?

rahul_jnk

Hi pdj, I'm using NAV 2016 & SQL Server 2014 Enterprise Edition.

Yury

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.

David_Singleton

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.

rahul_jnk

Thanks a lot David_Singleton. That worked.

David_Singleton

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.

Slawek_Guzek

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

rahul_jnk

Thank you Slawek_Guzek & David for your valuable advice.

David_Singleton

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.