Strange SumIndexField error, solved but curious why.

Mauddib

NAV 5.0

Ok so there is a really bad key on one of my customers Gen. Journal Line table.

The key is:

Journal Template Name,Journal Batch Name,Account Type,Currenc

y Code,Account No.,Amount (LCY),Payment Order No.,Payment Type

Yuck I know. What is worse is that the SumIndexField is "Amount (LCY)" which is also in the key!!!

Anyway I was changing some code and came accross this code block:

GenJnlLine2.RESET;

GenJnlLine2.SETCURRENTKEY("Journal Template Name","Journal Batch Name","Account Type","Currency Code",
                                        "Account No.","Amount (LCY)");

GenJnlLine2.SETRANGE("Journal Template Name",'ZAHLUNGSEI');
GenJnlLine2.SETRANGE("Journal Batch Name", 'PAYBACK');
GenJnlLine2.SETRANGE("Account Type",GenJnlLine2."Account Type"::Customer);
GenJnlLine2.SETRANGE("Account No.",'DEB-C-0003490');
GenJnlLine2.CALCSUMS("Amount (LCY)");

So I tested this in debugger and the first result it got was a Gen. Journal Line (there was just one in the filter I checked with COUNT) with a value 1189 but the CALCSUMS gave me 27347 which is EXACLTY 23 times bigger.

Any ideas why? There is more than 23 lines in that journal and MUCH more than 23 lines in the table.

I solved the issue with this addition:

GenJnlLine2.SETFILTER("Amount (LCY)", '<>0');

But I have no idea why this fixes it. For my own interest Id like to know why.

ara3n

I don't know why, but if you are on sql I would disable MaintainSQLIndex, and disable MainTainSift.


Also if the key is only used in the mentioned code, the Amount LCY doesn't need to be part of the key.