I'm trying to post an Item Journal to negatively adjust some inventory. Everything is tracked by a serial so there are item tracking lines for every journal line. When the posting routine gets to the second line it errors out, saying that "Value Entry xxxx already exists." The value entry that does already exist has nothing to do with the line that is being posted, and in fact shouldn't even come up in the filters.
I have looked at the debugger to see that no other filters are applied. It is only the Item Ledger Entry No. It is filtering on the correct Item Ledger Entry, but when it does a FIND 1) It errors out somehow even though it's in a conditional statement and 2) The Value Entry that already exists does not have that Item Ledger Entry on it.
The following code is from Codeunit 22. Function EnsureValueEntryLoaded(ValueEntry,ItemLedgEntry)
ValueEntry.SETCURRENTKEY("Item Ledger Entry No.");
ValueEntry.SETRANGE("Item Ledger Entry No.",ItemLedgEntry."Entry No.");
IF ValueEntry.FIND('-') THEN; <-- Error thrown here
It is called by Codeunit 22 in the Function TouchItemEntryCost(ItemLedgerEntry,IsAdjustment)
ItemLedgerEntry."Applied Entry to Adjust" := TRUE;
WITH ItemLedgerEntry DO
SetAdjmtProp("Item No.",ItemLedgerEntry."Entry Type",IsAdjustment,"Prod. Order No.",
"Prod. Order Line No.","Posting Date","Posting Date");
IF NOT IsAdjustment THEN BEGIN
EnsureValueEntryLoaded(ValueEntry,ItemLedgerEntry); // <---- Here
AvgCostAdjmtEntryPoint.UpdateValuationDate(ValueEntry);
END;
Answers
When NAV (all native without debugger) and also from some version under SQL buffers the insert into tables UNTIL some command requests a return from the DB-server. In that moment NAV sends all the inserts to SQL and you might hit the error. Search for an INSERT-statement on that table and for testing, change it to This will immediately trigger the error.
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
Interesting, I thought this was only in Native. Do you know which versions of SQL this happens in?
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
#-o yes of course. Thanks.
I think someone is inserting extra value entries by non-standard NAV code.
If you post more than 1 item ledger at a time, the last used Value entry "Entry No." is saved to avoid having to lock the table and get the last value entry again. If extra records are created by non-standard NAV code, this generates problems.
This is the reason I said to use "IF TheTable.INSERT THEN ; ".
And now that I think about it: I made a mistake. previous insert would avoid the error.
It should be :
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
As Kriki already pointed out, the reason why the error within the debugger occurs so lately is true to the new so called bulk-inserts-feature, which was introduced in NAV 5.0 (SP1) - see for example:
http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2008/04/14/bulk-inserts-in-microsoft-dynamics-nav-5-0-sp1.aspx
This feature applies only to SQL server. If you try the same within the native server, the debugger will stop at the right position.
It may be worth noting, that in the case of item journal posting (and other posting routines), the amount of time that a table is locked (this is claimed to be the main purpose of the new feature) is not reduced - at least not for the item ledger entry and value entry tables - because the locking immediately (and inevitably) becomes effective right in the beginning of codeunit 22 due to the usual LOCKTABLE - FINDLAST commands.
There is another remarkable point, yet: So why is there an error at all, if the posting routine simply adds value entries with the next free entry no.?
The reason is due to the function EnsureValueEntryLoaded, which has already been posted in the beginning: Now it is clear, that the ValueEntry with the next free entry no. is switched back to a lower entry no., because there already exists a value entry for the corresponding Item Ledger Entry (which has been deleted before). In some respect I find it striking that the same piece of code where the debugger more or less accidentally stops is responsible for the error in the end...
By the way, someone should check if the ValueEntry.FIND('-') should be changed to ValueEntry.FIND('+') - I'm worrying about the case when there exist two or more value entries for an item ledger entry...
On the one hand yes it's bad (very very bad) to delete ledger entries and import directly into the ledger instead of a journal. But on the other, I don't know if there's a better way to really learn all those dependencies. And then you learn / remember other things, like the buffer issue.
My Blog - nav.education
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!