How to become a great Microsoft Dynamics NAV developer: Tables and other objects

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.

The Object Table

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:

  • Tables
  • Forms (classic client)
  • Pages (from NAV 2009 RTC and NAV 2013)
  • Reports
  • Dataports (pre NAV 2013)
  • XMLPorts
  • Codeunits
  • Query (from NAV 2013)
  • MenuSuite

Object numbering system

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:

 Object no. range Description Comment

1 - 9,999

Standard application design area

Partner development license allows you to modify objects in this area, but you cannot insert new objects.

10,000 - 49,999

Country/region design area

Partner development license allows you to modify objects in this area if your license has a permission in that country/region. You cannot insert new objects.

50,000 - 99,999

Customer design area

You can create and modify all objects in this area with a partner development license. An end-user license can only create/modify/run objects in this range, if they have purchased access to the specific numbers.

100,000 - 999,999,999

ISV/add-on design area

ISV/add-ons are assigned objects to specific ranges in this range.
Partner development license allows you to modify objects if your license has a permission in a specific add-on in this area. You cannot insert new objects. Even if you have access to an add-on in this range, then the ISV might have protected you from modifying the objects.
2.000,000,000 – 2,999,999,999

System area

Objects in this area cannot be modified and you cannot insert new.

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.

Field numbering system

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.

Table number Field numbers Description  Comment

1 - 9,999

1 - 9999

Standard application design area

With a partner development license fields can be modified, but no new fields can be created.

1 - 9,999

10,000 - 49,000

Country/region design area

With a partner development license fields can be modified, if your license has within the country/region, but no new fields can be created.

1 - 9,999

50,0000 - 99,999

Customer design area

Fields can be added and modified.

1 - 9,999

100,000 - 999,999,999*

ISV/add-on design area

Fields can be added and modified only by the ISV to whom the field number range is assigned.

10,000 - 49,999

1 - 49,999

Country/region design area

With a partner development license fields can be modified, if your license has within the country/region, but no new fields can be created.

10,000 - 49,999

50,000 - 99,999

Customer design area

Fields can be added and modified.

10,000 - 49,999

100,000 - 999,999,999

ISV/add-on area

Fields can be added and modified only by the ISV to whom the field number range is assigned.

50,000 - 99,999

1 - 999,999,999

Customer design area

Fields can be added and modified.

100,000 - 99,999,999

1 - 9,999

ISV/add-on design area

Fields can only be added by the ISV to whom the tables number range is assigned. With a partner development license fields can be modified, if your license permission to the specific add-on range.

100,000 - 99,999,999

10,000 - 49,999

Country/region design area

With a partner development license fields can be modified, if your license has within the country/region, but no new fields can be created.

100,000 - 99,999,999

50,000 - 99,999

Customer design area

Fields can be added and modified.

100,000 - 99,999,999

100,000 - 999,999,999

ISV/add-on design area

Fields can be added and modified only by the ISV to whom the field number range is assigned. With a partner development license fields can be modified, if your license permission to the specific add-on range.

* 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

Table objects and SQL Server

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.

A table per company in SQL Server

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.

About this blog series

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.

Comment List
  • 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.

Related
Recommended