mibuso.com

Microsoft Business Solutions online community
It is currently Wed May 22, 2013 6:24 pm

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 20 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Using one No. Series table in multiple companies
PostPosted: Fri Jan 23, 2009 6:45 pm 
Offline

Joined: Tue Nov 13, 2007 6:51 pm
Posts: 45
Location: UK
Country: United Kingdom (uk)
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


Top
 Profile  
 
 Post subject: Re: Using one No. Series table in multiple companies
PostPosted: Fri Jan 23, 2009 7:24 pm 
Offline

Joined: Mon Mar 08, 2004 2:42 pm
Posts: 3255
Location: Hannover
Country: Germany (de)
The whole No Series or only some like contact or customer?

_________________
Do you make it right, it works too!


Top
 Profile  
 
 Post subject: Re: Using one No. Series table in multiple companies
PostPosted: Fri Jan 23, 2009 10:53 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7931
Location: Howell, MI
Country: United States (us)
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>

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Last edited by DenSter on Sat Jan 24, 2009 5:23 pm, edited 1 time in total.

Top
 Profile  
 
 Post subject: Re: Using one No. Series table in multiple companies
PostPosted: Sat Jan 24, 2009 12:23 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jul 31, 2003 12:00 pm
Posts: 639
Location: Nürnberg
Country: Germany (de)
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

_________________
Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool


Top
 Profile E-mail WWW  
 
 Post subject: Re: Using one No. Series table in multiple companies
PostPosted: Sat Jan 24, 2009 12:32 pm 
Offline

Joined: Mon Mar 08, 2004 2:42 pm
Posts: 3255
Location: Hannover
Country: Germany (de)
Thats the reason why i ask for: All No Series or only some.
So, here we need some more informations.

_________________
Do you make it right, it works too!


Top
 Profile  
 
 Post subject: Re: Using one No. Series table in multiple companies
PostPosted: Sat Jan 24, 2009 5:24 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7931
Location: Howell, MI
Country: United States (us)
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.

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject: Re: Using one No. Series table in multiple companies
PostPosted: Sat Jan 24, 2009 5:33 pm 
Offline

Joined: Mon Mar 08, 2004 2:42 pm
Posts: 3255
Location: Hannover
Country: Germany (de)
oh sorry Daniel i misunderstood you :oops: :oops:

Regards (René)

_________________
Do you make it right, it works too!


Top
 Profile  
 
 Post subject: Re: Using one No. Series table in multiple companies
PostPosted: Sat Jan 24, 2009 9:13 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7931
Location: Howell, MI
Country: United States (us)
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 :mrgreen:

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject: Re: Using one No. Series table in multiple companies
PostPosted: Tue Feb 03, 2009 8:21 pm 
Offline

Joined: Tue Nov 13, 2007 6:51 pm
Posts: 45
Location: UK
Country: United Kingdom (uk)
[quote="garak"]Thats the reason why i ask for: All No Series or only some.
So, here we need some more informations.[/quote]


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


Top
 Profile  
 
 Post subject: Re: Using one No. Series table in multiple companies
PostPosted: Tue Feb 03, 2009 11:42 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Tue Aug 14, 2001 7:01 am
Posts: 5315
Location: Prague
Country: Czech Republic (cz)
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.

_________________
David Singleton
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
 Post subject: Re: Using one No. Series table in multiple companies
PostPosted: Wed Feb 04, 2009 11:22 am 
Offline

Joined: Tue Nov 13, 2007 6:51 pm
Posts: 45
Location: UK
Country: United Kingdom (uk)
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


Top
 Profile  
 
 Post subject: Re: Using one No. Series table in multiple companies
PostPosted: Wed Feb 04, 2009 12:03 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Tue Aug 14, 2001 7:01 am
Posts: 5315
Location: Prague
Country: Czech Republic (cz)
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.

_________________
David Singleton
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
 Post subject: Re: Using one No. Series table in multiple companies
PostPosted: Wed Feb 04, 2009 2:59 pm 
Offline

Joined: Fri Sep 23, 2005 8:45 am
Posts: 21
Location: Feldkirch
Country: Austria (at)
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.


Top
 Profile  
 
 Post subject: Re: Using one No. Series table in multiple companies
PostPosted: Wed Feb 04, 2009 3:32 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7931
Location: Howell, MI
Country: United States (us)
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.

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject: Re: Using one No. Series table in multiple companies
PostPosted: Wed Feb 04, 2009 3:39 pm 
Offline

Joined: Fri Sep 23, 2005 8:45 am
Posts: 21
Location: Feldkirch
Country: Austria (at)
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.

Code: Select all
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 :)


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 20 posts ]  Go to page 1, 2  Next

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: