One way Dynamics NAV differs from most traditional development environments is the way everything is divided into different objects. In NAV these objects contains the complete application definitions or programming source code, if you prefer to call it so.
This post in my series with tips and tricks for new NAV developers is about the the NAV objects, but mostly about tables.
Inside the NAV database all the objects and their definitions are stored inside the Object table (no. 2,000,000,001). You cannot see this table from within the NAV table designer, but if you open the table via SQL Server then you can see it here. You cannot see the actual source code here, as it is stored in the BLOB (binary large object) field “BLOB Reference”. But you cannot change the objects directly from this table, only indirectly using the Object Designer.
The different objects types which are available in NAV are:
As a new user its important to know your way around the different objects. The number assigned to the object tells you a lot about the object and if you are able to modify the object or not, depending on your license type.
All the objects inside NAV are numbered according to the below table:
1 - 9,999
Standard application design area
10,000 - 49,999
Country/region design area
50,000 - 99,999
Customer design area
100,000 - 999,999,999
ISV/add-on design area
You should avoid using the object numbers in the range 99,000 - 99,999, even though they are in the customer design area. These numbers are used by the training material for Microsoft Dynamics NAV.
A similar numbering system exists for field numbers. You can only add/modify fields in tables you have access to according to the table above.
1 - 9999
10,000 - 49,000
50,0000 - 99,999
100,000 - 999,999,999*
1 - 49,999
1 - 999,999,999
100,000 - 99,999,999
* a part of this range also belongs to the standard application area, typically 99,000,000 – 99,999,9999.
More information here: Dynamics NAV Number Conventions
A new table is always created and modified using the table designer inside NAV, either directly in the classic client, or the “Dynamics NAV Development Environment” (NAV 2013). Navision will then create the table accordingly in SQL Server, or synchronize the changes to SQL.
In general you should NEVER change the design of a Navision table directly from SQL Server. If you do then these changes are not synchronized back into Navision’s database definition and your system might start malfunctioning.
Also when you have to move new or changed tables, in example from your local development database to the customers database. Then you must export the objects from your local version and import them again using the object designer in the classic client/development environment.
If you open your database using SQL Server Management Studio you will notice that NAV creates a table per company. So for example the Item table is created as “CRONUS Danmark A_S$Item” – a combination of the company name (CRONUS Danmark A/S) and the table name.
If you have tried to create a new company, then you might also have seen the first thing what happens is that NAV says “Creating tables….”. This is where all the new “company-specific” tables are created in the SQL Server database. When NAV synchronizes changes to SQL it does so to all the “physical” tables related to the table object.
The only exception are tables where the property DataPerCompany is set to No in the Table Designer. This is used if you have tables where the data is to be shared between the different companies.
But use this property with caution. First it will often give problems in a test environment, basically because you often will use the NAV backup function to copy companies. And after your database has its first company created, then you can no longer restore “Data Common to all Companies”.
Now a lot of new developers would think that the DataPerCompany property is a great and easy way to solve a “master data” request. An example is if a company want to share their vendors or customers between their different NAV companies. Here I have seen many developers who think that this can be done just by setting the DataPerCompany to No. But the reality is that it takes a lot more than this property. If you start setting the property on the vendor table, then you also need to set it on all the related setup tables as well. And within long you have changed at least 30-40 tables and it then it easily get out of hand. Not to mention the above issues with backup/restores. It should really only be used for “simple tables”, which truly are common to all companies.
A better way to solve the “master data” request is either to have a separate “master data company” and then synchronize between the companies. Maybe not easier to do, but much cleaner and in the long run, then it will give you a lot less problems, as you easier can separate the new functionality from the standard functionality. A key when we later are going to look at how to develop with upgrades in mind.
This blog post is the second in a series of posts with the purpose of helping especially Dynamics NAV developer newbies with some of the most frequently asked questions, basically small tips and tricks. If you have a suggestion to topics I should write about, then please write it in the comments below. If it’s a general question then I will put it on the list to be included in a future post. But I please don’t ask any urgent questions. Urgent and non-general questions should be asked in the forums: http://dynamicsuser.net/forums/navision.aspx
In the next post in this series I will write about the debugger and how to use to to learn more about Dynamics NAV.
Cant we modify 20000000XX series objects with Partner license?
Technically yes you can modify them with a partner license, but not recommendable. These tables are system generated (like created automatically when you create a new database). You will also notice that no triggers are visible.
I think the only one I ever did modify was the User table, where I added a new field. But that was many years ago and not something I would recommend anyone to do.