I’m pretty sure David (Singleton) will want to crucify me for bringing this topic back again. I know why we shouldn’t change DataPerCompany to no for some major tables, like G/L Account, Customers, Currency, etc. I totally understand how DANGEROUS this can be. But my question is: is it really a crime to do it for tables with less impact. The table “Payment Terms” for example?
No it’s not a crime, it can even be very helpful. The only issue that you need to discuss is in which companies you will allow records to be changed. If one person in company A changes a payment term, that will also change in all the other companies, and that will surely cause all sorts of problems. We actually did some of this at a number of clients. We added a boolean ‘HQ’ or something to that effect to the Company info table, and only allow one company to have that boolean checked. Then, all the tables that are set to DataPerCompany=No can only be editable for the HQ company. We did that by getting the company information when the form opens and set it to not editable if the box isn’t checked. Now for companies that have multiple offices in multiple companies in Navision that have for instance a common Item list, but they want to have their own control over their Item setup, we created a copy of the Item table, called it ‘Global Item’ and developed functionality to copy global items into the regular item table. The Global Item has DataPerCompany=No, so everybody can see this. But, each company has their own subset of the global items in their regular item table, and they can only see their own transactions. This is basically a nutshell description, because it gets really complicated with subsidiary tables, but it can work really well if you have a good design.
Hi David, basically there are some simple pointers if you are going to make a table global. 1/ Make sure that you can not rename the table. 2/ There should be no code that modifies the the table directly. “payment terms” is a good example, it is manually set up, and there is not likely to be a problem where the record is changed by code in a particular company. 2a /There is no problem with code that creates new records, its just delete, modify and rename that are problems. 3/ There should be no field on the table that has a table relation to another table that is “per company”. 4/ If you did have to do (3), then make sure you have code which which validates through all companies. 5/ If there is someway of doing it with a new table, do it that way instead. 6… there are of course more … please every one add to the list. I disagree with Daniel (sorry [B)]), on the HQ concept, it should be user based security, since by definition, you are removing company level access control. It really does not matter what company the user is logged into, it is the user that should know what they are doing. I DO agree with Daniel’s aproach on the Global Item solution, and have done it often, in fact Daniel is probably describing some of my code[8D]. The thing Daniel points out, that is most critical, is “good design”. That is what really matters. Personally I have never been able to see the advantage of changing the database structure, over adding a new table and some code. Remember, your code has at least one bug, try to have that bug show up as soon as possible. If you write code to link tables, the bug will show up in the begining when you link. If you change the table structure, the bug will show up some where later, when you are not around.
We made this change to the Customer and Vendor tables for a client and it works OK, certainly easier than maintaing the same customers in several companies. It looks a bit odd that they have different ledgers and in different companies but that’s what is happening in reality. They say that they won’t delete any customers or vendors so that shouldn’t be a problem. However they did go out and buy another company recently, and wondered why we laughed when they said they wanted THIS company’s customers and vendors to be separate! *$%&@!# clients, eh. So I don’t think it’s a crime to change the datapercompany property but once you’ve done it remember that you can’t go back!
Dan, that’s exactly the problem with making DataPercompany=No! There comes the point where customers want to reverse their decision, then you are better of if you have implemented a synchronization solution between companies instead of DataPerC=No. How did you treat the other table that Cust/Vend tables have relation to???
Why do not use a function “Validate in all company”, you made the modification for a Company and after if you want lauch the function toapplys this modification to all the company or better use a form displaying the company in the database tick the company you want ti update and after launch the function. It’s better the modify the property “DataPerCompany”
Hello, Just to warn Dan - I hope you added/changed the code to the Customer/Vendor tables to check all ledgers in all companies before allowing things like deleteing, otherwise if you have a Customer/vendor in company X with ledger entries (Some of which may be open) and in company Y they have no ledger entries (Or old entries that will not prevent deleteing) then a helpful user could delete the record in Company Y even though they should not be allowed in Company X. The code will only check the ledger you are in because that table is not common to all companies.
quote:
Originally posted by David Singleton
Hi David, I disagree with Daniel (sorry [B)]), on the HQ concept, it should be user based security, since by definition, you are removing company level access control. It really does not matter what company the user is logged into, it is the user that should know what they are doing. I DO agree with Daniel’s aproach on the Global Item solution, and have done it often, in fact Daniel is probably describing some of my code[8D]. The thing Daniel points out, that is most critical, is “good design”. That is what really matters.
David, I am sure that I am not the only one or even the first one to do something like this, and it sounds like we did similar things (GMTA [:D]). But this global item/vendor/etc. that I developed did not come from anybody else than me and my team. I can assure you that I am not describing your code, I don’t even know who you are. [8)] And the HQ concept is basically user driven. Only certain users have access to the HQ company, it’s just that this was for us the easiest way to enforce that global data can only be changed in a certain way. The people who are in charge of these global records are actually employed by the main office, and users from other offices have to submit a request for a new global item/customer/etc. to HQ, from which they can then copy it into their ‘local’ tables. So, it was a pretty easy step to do that HQ concept. If the situation were different, and people in more than one office should have been able to enter these global records, then obviously we’d have gone for a different design. And designing this correctly is the main issue isnt it. [:)] Thanks, Daniel
Hi Edward, No need to do this, users can’t do anything apart from minor editing within Navision. Customers and Vendors are replicated from an external database into both Navision and another system which interfaces with Navision. Among the rules are no deleting and no renaming. It sounds dodgy but it works. It’s not the way that I would have done it but it’s what the customer wanted - and the customer’s always right, right? Yeah, right. I’m waiting for the day that they decide that they DO want to delete customers, then we’ll cost the extra checks into our estimates. Dan.
Dan, just make sure to add code in the tables to prevent deleting and rename, other wise it will eventually happen.
Oh don’t worry, there’s a big fat error message that comes up if you try to do either of these things. Crude but effective.
good, your original post gave the impression that you used the “cross fingers principle”
quote:
Originally posted by Dan Kelly … They say that they won’t delete any customers or vendors so that shouldn’t be a problem. …
I’m all in favour of big fat error messages in these circumstances![:D][:D][:D]
Hello to Mister Beaver - Hello to all Mister Beaver wrote: “There comes the point where customers want to reverse their decision, then you are better of if you have implemented a synchronization solution between companies instead of DataPerC=No.” Do you / does anybody know a solution? - an addon or ready objects or othner programm for this? thanks in advance