Using one No. Series table in multiple companies

Michaellee

Hi all,

I have a requirement to get 2 companies in one database to share the No. Series.

I have asked our supplier and they suggested to change the DataPerCompany field on the No. series but could you please help me with how I can call from just one table?

Many thanks for your help.

Regards,

Mike

garak

The whole No Series or only some like contact or customer?

DenSter

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>

stryk

Hi!
I absolutely agree with Daniel! Many blocking problems are related to "No. Series" and this will get worse once you design it "DataPerCompany=No".
Maybe it is possible to create some kind of "Synchronization Routine" for specific Numbers?
Regards,
Jörg

garak

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

DenSter

We know Garak, you didn't give any advice, so that was not in reply to you. I added a quote to my reply to clarify that.

garak

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

Regards (René)

DenSter

That's alright Rene, no need to apologize. I can see how without the quote it looked as though I was replying to you, so I added as much to the confusion as you did

Michaellee

garak wrote:

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

Many thanks for the help guys and gals!

At the moment the criteria would be to use all of the No. Series.

Thanks again,

Mike

David_Singleton

Well Mike, unlike Daniel and Joerg that go as far as to say this advise is bad, i would take it further and give serious thought to the technical abilities of your partner.

Not so much the fact that they have given you extremely bad advise, but more the fact that it seems they have suggested that you implement this your self. I really do hope I am reading this wrongly.

had they said something like "Well we could change the datapercompany flag. Let me have one of our technical people confirm that and we will let you know if it is an option" is a good response from them; but if as you imply they suggested that you do this yourselves, then you have problems.

Michaellee

My partner only provided this option as a concept and as I would like to understand if there are other ways to do this, they could not provide me with an answer.

If the option is to have the use of all the No. Series Table in multiple companies, is it possible to do this instead of changing the datapercompany field to No? Just as a bit of background, we are using NAV 5 SP1 with a SQL database.

Many thanks,

Mike

David_Singleton

Michaellee wrote:

My partner only provided this option as a concept and as I would like to understand if there are other ways to do this, they could not provide me with an answer.

Then I missunderstood your original post.

So I am not sure why you need help on how to do this. Personally I think its a bad idea, but if your partner is goiing to do it, its not really necessary for you to know how to do it.

I guess i am completely missing what it is that you want to know.

trag

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.

DenSter

That's not a bad concept, I like it, although it probably takes quite a bit of coding to get the synchronization right, since when you set it to a global series in one company it has to check all the other companies as well. Did you create a copy of both number series tables and set the DataPerCompany to No, or did you create a table straight on SQL Server with a linked view? I'd say the first is easiest to implement.

trag

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

DenSter

Thanks. The part about how to create a global table is the easy part, I know how to do that. I am more curious about how you synchronize No. Series setup across companies.

So when you set a global vendor number series in Company 1, you want the same series to be set in company 2. Then if someone in company 2 changes it, how do you prevent that from happening? Which company is 'the boss'? What happens when someone in company 2 unchecks the 'global' box and wants ot to be a local series, but company 1 wants ot ti apply to all companies? How do you resolve those types of differences? There's quite a bit of logic that needs to be executed there.

trag

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.

DenSter

Gotcha, so it's a manual process, and it works because there's only one person that is in charge of the numbering series. It's handled by business process, not by a system process. The only thing that was actually developed is the part where it uses the external database for numbering series logic in case that flag is set.

Hardly a solid design, but it works for that customer, and that's what counts isn't it

trag

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.

Michaellee

Hey guys, thanks for the info! This would work really well and I'm glad that I did post this on the forum! I will see if our partner will be technical enough to do this... thanks again!



Mike