Running out of entry numbers – use the ones below zero? (NAV 2009)

Business Central/NAV
1

Issue : If a client is using NAV 2009 on SQL for many years, there is a risk of running out of entry numbers, for example in the table G/L Entry. And imagine that it is imperative to continue using the same company instead of creating a new one.

Imagine this scenario : The first entry no. was set to 1 (many years ago) and then incremented over time as posting happened and continues to happen. In some time, it will be close to the maximum possible value of an integer, 2,147,483,647. When it hits, no more posting is possible as NAV will throw a runtime error. The integer’s minimum value is not zero, but in fact -2,147,483,647. So only half of the nearly 4.3 billion possible values are utilized. Brainstorming on possible solutions, the idea of using the negative range of -2,147,483,647 to -1 has come up.

Of course, I know that if it is even possible, it is a major development task. The task includes finding and modifying every piece of C/AL code that reads, filters out, increments, and assigns entry numbers, just to mention some of the work. I would also expect certain other changes such as field properties and relations. I must decide (and implement in the C/AL code) two principal approaches: start new entry numbers at -2,147,483,647 and increment or start at -1 and decrement. All that is OK, I’ll guess that I have all the time in the world – and I do enjoy a challenge. I am very curious if anyone has tried this approach and succeeded? I don’t know if any minimum values have been set to 1 inside the application itself – in the .exe and .dll files.

I know that if all this is done, any client using this modification will be running NAV on a completely unsupported platform. And I also know that asking for best practice to solve the problem results in only one correct answer: create a new company.

Additional information : Changing the datatype of the field Entry No. from integer to big integer has also been considered and rejected. There are a huge number of references to the field from fields in other tables, that also need to have changed their datatype – some in fields that are part of keys. And the required service window for down time to convert and rebuild keys are not very business friendly.

Any constructive thoughts on this issue are very much appreciated.

2

Hello,

I think it’s a good idea, in the past (Navision 2.60 to 9.0), we used a system to separate G/L entry numbers by databases, because N databases replicated data to a central database.

This is a similar approach, I think you don’t have to worry, the integer field entry does support negative numbers, but they are never used. It’s not difficult to make these changes in codeunit 12, it’s really simple.

In the part where the system searches for the last entry no. published, you should filter to consider only those less than 0; GLEntry.setfilter(“Entry No.”, ‘<0’)

If no record is found, assign the lowest value: -2,147,483,647… otherwise, let the system do its job.

You can apply the same system, if necessary, when searching for the latest VAT entry/Detailed Cust. Ledg. Entry/Detailed Vendor Ledg. Entry. I think those detail entries may have some problems when for a Customer/Vendor Ledger Entry there is part positive and part negative, probably won’t be displayed it correctly and can have some problemes to unapply it if necessary.

3

Thank U for the response. And oh yes - the task of using different entry ranges in order to copy the entries into one single company is also something I’ve done years ago.

Btw, so far (I’m not done), I’ve spotted thirteen tables (using OMA), that refs to Entry No. in G/L Entry. Some of these also has the field in one or more keys, some primary. And those fields refs to yet other fields.

4

And what is the problem?

The only problem I see is the order in which the data is displayed (as I said in my previous answer). The same “Entry No.” of “G/L Entry” is used in “Cust. Ledger Entry” (PK), “Vendor Ledger Entry” (PK), “Bank Account Ledger Entry” (PK) , “G/L Register” and some other tables, but I don’t see any problem in those tables if negative numbers are used.

And finally, the change is really easy to implement… I think is better to do the modification and do some testing to verify that all is working properly.