How to become a great Microsoft Dynamics NAV developer: Which tables to use?

Coming from a different development environment, then you will often bring your habits of doing development with you. And when met with a requirement then you think that the way that looks the shortest is the best and easiest. With NAV (or for that matter any other advanced ERP system) it is not always the case.

I know I was promising that the next blog post in this series was going to be about the debugger. But the last week I have seen several forum posts, which have made me change my mind. I see posts where it’s obvious the questions comes from experienced developers, but it also show that they lack the overall understanding of the way Dynamics NAV works and how the system is overall designed! They really need to go back and get more familiar with the system, exactly what I wrote about in my first blog post in this series: How to become a great Dynamics NAV developer: Getting Started.

Microsoft Dynamics NAV is an advanced ERP system which is used to manage both small, medium and large companies world wide. It is handling all sorts of administrative functions of which many are regulated by both international and local legal requirements. Many functions work the way they do because otherwise they would not be approved by the governments and auditors who validate the companies financial reporting etc. These requirements are not only within the areas of VAT/Sales Tax calculations, but also within traceability, unique number series and a lot more.

Today the system has about 250,000 lines of source code, then no developer is expected to know them all. But NAV developers should know the general structure and especially who almost everything is linked throughout the system. They should know the overall data flow, by which I mean how data is generated and flows through the system.

Like how a Sales Order becomes a Posted Sales Invoice when posting it and that it at the same time first generates Gen. Journal Lines which becomes General Ledger Entries and a Customer Ledger Line (and Detailed Customer Ledger Line), as well as Item Journal Lines which becomes Item Ledger Entries. All in one function, which calls multiple codeunits all doing a small part of the work. And if you learned this, then you will be happy know learn that the Purchase Orders structural works exactly the same, just with different codeunits and different tables.

Dynamics NAV - Sales Posting

So whenever you need to create data in Navision you should look at how data in similar or the same table is done in the standard system. Exactly as I wrote in my “Getting Started” post.

An example – import data into the customer ledger entry

But let me try to explain what I mean by coming with an example. The below could be a requirement you have received from your employer/client:

“We need a system which imports data from our external system, which tracks whenever our customers are logged into our XYZ system. This data you need to import is a simple text file containing: Customer no., Date, Time, Description and Amount. The external system have issued a “Receipt of service” via email with a transaction no./invoice no. We need you to add it to the customer ledger (to create a Customer Ledger Entry) so that it will be included on the customers monthly statement, and that we can register that the customer pays. You also need to create one G/L Entry per day (file is issued per day) which needs to be posted to our account no. 1010. There is not VAT on this service.”

 

Then as a new developer you might think that this is a very easy request. You can quickly create a solution which inserts the file data into the Customer Ledger Entry. Not really a lot of data in this table, and when I don’t have to worry about VAT etc. then this is easily done. And one G/L Entry line is also not a problem.

Well I'm sorry to say it, but you’re wrong!

  1. Firstly then you should NEVER insert anything directly into the entry tables. Because no entries stand alone in NAV. As I said above, then everything is linked tightly together. Not only are entries in NAV always linked to a Register. The register is the place the auditor can use to get a complete overview over all postings made to the system. All ledger entries including General, Customer, Vendor and Item Ledger Entries are part of a traceable register.
  2. Second you cannot create a “stand-alone” G/L Entry. All G/L Entries needs both a balancing debit and credit post, otherwise the system will become “out-of-balance” or in-consistent. This will prevent you for doing any new postings, until the system again is consistent.
  3. Third you need to know that a Customer Ledger Entry (and a Vendor Ledger Entry) always has a directly linked G/L Entry and that it is using the same Entry No. as it’s G/L Entry. So if you were to insert the usage directly into the Cust. Ledger Entry table, then you would also have to create the linked G/L Entry. This of course could solve the issue of keeping the system consistent.

There is a lot more to think about, than just inserting data into a few “simple” tables. But as long as you know the structure of Dynamics NAV, then it’s still a rather simple request.

So how would the experienced NAV developer handle this request?

Personally I would start by questioning the file content. It’s ok that no actual invoice is required and doesn’t need to be printed, but there should always be a link which makes it possible to trace all posts in the system. Not only because at some point the customer will start asking questions about his monthly statement, but also because it’s a legal requirement that everything is traceable within the system and that all sales has a unique invoice no. So as a minimum the transaction no. should be included and added to the statement. Very similar to how you own credit card statement from you bank works. This transaction no. should be used as the Document No. within NAV.

As I wrote above, then the only right way to create Entries in NAV is to follow the way Entries are created in standard NAV. And the standard flow is that G/L Entries are always created via General Journal Lines. So your file import should start by creating Gen. Journal Lines – not General Ledger Entries or Customer Ledger Entries. One line for each line in the file. It doesn’t need to “physically” store the General Journal Line in your database, but it can be a good idea to do it. Not only because it allows you to test your work this way. But also because it is often preferable for the client if they are able to verify the file content manually (by printing the Gen. Journal Test report). If they still prefer to have it post automatically, then you can easily add this, when you know that everything imports and posts correctly when done manually.

When creating the import using the General Journal instead of directly to the G/L Entry and Cust. Ledger Entry tables, then it also enables you to use the standard table and field triggers of the General Journal Line table. So when writing your import code, then the best way is to start by testing how to manually enter the same data using the Gen. Journal Line. Find out which order the fields should be validate. The order it is done in, is often quite important. I.e. it’s important that you validates line type and account no.’s before validating the amounts. But using the debugger whilst testing how it works when you do it manually will answer these questions for you.

Then I would do it by using the G/L Sales Account as the primary account on each line and the customer no. as the balancing account. This way both my debit and credit part of each post is taken care of.

Avoid hard coding

Finally I would make sure I don’t have to “hard code” anything into the system. If you’re not familiar with the term “hard coding” then this is one of the biggest mistakes a developer can do, and it means that you are putting setup data directly into the code.

In this example, even though the client directly specifies that account no. 1010 is to be used, then I would never put this number into my source code directly. Instead I would create a new field to store this setup data into. Either in a new “XYZ Import Setup” table or in an existing related table.

The new table is the best way when you think about future system upgrades. But when a new table (and related form/page) costs money in NAV, and because it takes a little longer to create a new table and form/page compared to adding it to the an existing, then most developers would choose to use an existing. Even though it is not the better way and the extra money spent now is saved many times later.

Post Scriptum

As with most other situations in life, there is not just ONE WAY to do things. Not only might you have different ways you prefer to do things, but the request could also be slightly different. And as such my way of doing it is just one of the ways. But even though you might do it a bit different, then you should never write/import directly to Dynamics NAV entry tables. 

Comment List
  • Good post, Erik!

    Something that we could only learn by doing experience. I found no one information like this in the training book or online help.

    Thank you.

  • Great post Erik!

    If we all were a little better at respecting the standard way of creating records, posting and development in general, the solutions out there would be better.

    Thanks for sharing your thoughts once again.

    /Søren

Related
Recommended