Using one No. Series table in multiple companies

Michaellee wrote:

I have asked our supplier and they suggested to change the DataPerCompany field on the No. series

No that is bad advice, I would definately not do that. The numbering series table is a bottleneck as it is for individual companies, adding more companies would only make it worse. Plus, that way ALL numbering series would be shared, not just one of them. Besides, you'd also have to make related tables global, which can get complicated very quickly.

<edit>added quote</edit>

Thats the reason why i ask for: All No Series or only some.
So, here we need some more informations.

oh sorry Daniel i misunderstood you :oops: :oops:

Regards (René)

I implemented the following solution on customer side:

The no. series table was extended with a flag, that this global number is used in multiple companies. In the codeunit NoSeriesManagement i duplicated each function. If the no. series has the flag turned to yes, it will go to another table (for example Global No. Series Line), which is a SQL view to a central table in a central database. Everything else is the same, only the source table is different. With this solution, it is possible to use a global number series for special things like vendors, contacts and so on, but use a local number series for sales orders and more important for posted documents. With this, you get fewer locking problems. Probably this is a way to go, Mike.

it's very easy, DenSter. I write it down here.

Let me say, you have two SQL databases for different companies (let me say Company1 and Company2). I created a third database, which will contain the global no series tables (GlobalDatabase). In the global database, there is the table, which is the same structure, like the No. Series Line table from NAV. (GlobalDatabase.dbo.GlobalNoSeriesLine). In each Company, i create a view, which is a linked object in NAV.

use [Company1];

CREATE VIEW [Global No_ Series Line] AS
  SELECT * FROM [GlobalDatabase].dbo.[GlobalNoSeriesLine]

and in NAV, just create a table "Global No. Series Line" with DataPerCompany = No and LinkedObject = Yes and everything is running fine. Please note, this was written down out of my mind, there might be problems

ok, now I know, what you need. So here is, how it is handled on customer side.

First of all, there is one person responsible for the number series. If a number series is global, it's not allowed to modify the record by any user, only if the user has the permission to do that. Permission means a flag in the user setup. To get the global numbers running in both companies, the code for the global number series has to be the same in each company. The responsible person opens the No. Series form, define the same code and set the global flag to true. Now it is running out of the box in each company. If the no. series is created for the first time globally, the no. series line has to be created. Thus, a form for the global no. series line is also created, where the responsible person can do the setup.

With the same code in both companies, it is always using the same no. series line record in the global no. series line table. Furthermore you also have a lock on this record, if a user uses a global no. series (for example creating a new contact) and the lock will be release, after the transaction is committed.

I hope I missed nothing.

yeah, you are right, it is not a solid design. but the customer likes it and it is working very well, no problems at all with this solution.

i did something similar with a global credit limit check, also funny stuff.