Change the Warehouse Entry table to Autoincrement

ALopez2710

I searched the forum but was not able to find anything specific to the Warehouse Entry table.

Has anybody changed the way the Entry No. is incremented from the traditional code to property AutoIncrement? It seems to make sense since this Entry No. does not have to be in order for this table.

Also, I remember reading somewhere that not only would this allow SQL to maintain the number but also that changing the type of 'Entry No.' from Integer to GUID (Globally Unique Idenfier) would allow new records to be inserted at different pages of the table instead of always trying to insert at the end and thus avoid conflicts with multiple inserts.

I would appreciate any insight or experiences from anybody who has worked on this. THanks.

kriki

It would make things worse anyway.

Inserting in Entry-tables is always done serially.
For this table, see codeunit 7301:"Whse. Jnl.-Register Line". Function Code:

IF WhseEntryNo = 0 THEN BEGIN
    GlobalWhseEntry.LOCKTABLE;
    IF GlobalWhseEntry.FIND('+') THEN
      WhseEntryNo := GlobalWhseEntry."Entry No.";
  END;

This code makes sure the last record is locked to be able to insert records at the last moment.
You might change this, but also Item Ledger Entry, Value Entry, Item Application Entry work like that. So you should also change those. But the "Entry No." of "Item Ledger Entry" is use in Value Entry, Item Application Entry to connect those to the Item Ledger Entry.

Worse: the "Entry No." is an integer of 4 bytes. a GUID is 16 bytes. And the primary key is always added to every secondary key so these would grow making performance worse.

Until Microsoft decides to completely rewrite NAV to avoid the LOCKTABLE+FINDLAST construct, it is best to keep it this way.

ALopez2710

Thanks kriki, I would not change it for tables that required a ref. to other tables via the Entry No., only for the ones where this does not matter (Warehouse Entry table).

My point is exactly that, there is no reason to get the Entry No. for these tables in sequence, if we let SQL assign the key, it most likely be at a different section (page) of the table, therefore, when more than one user is trying to insert records, they would not be locked since it will be in a different page and not be conflicting with the very last page of the table.

kriki

You might try it for warehouse entry. But it wouldn't do much good because most of the time the warehouse entry is posted at the same time as the item ledger entry and also that table has that locking mechanism.

ALopez2710

Good point, but since we are getting record blocks specifically in the Warehouse Entry table, it may help reduce at least this specific issues. Thanks.