Once Upon a Time; this is how fairytales often start and even though the story of Microsoft Dynamics NAV is anything but a fairytale, it sure has some magic.
With more than 1,350,000 seats and 75,000 installations it is one of the most popular ERP packages in the mid market. In this book, we will go through the magic of the Dynamics NAV application. We'll see how Dynamics NAV will give better information on how our business is doing and better insight into where the processes can be optimized or need to be changed.
In this chapter, we'll discuss the basic principles of the Microsoft Dynamics NAV application, how it's structured and why. After reading this chapter, you will have a better understanding of what to expect when implementing and designing Microsoft Dynamics NAV.
At the time of publishing this book, Microsoft Dynamics NAV 2009 (6.0) SP1 is the most recent version of the product. When the Windows version was first introduced in 1995, the product was called Navision Financials 1.0. The Danish software company that originally developed the product, Navision Software A/S, was not yet acquired by Microsoft and it was a revolution. It was a full Windows product and had all the basic functionality that small companies needed. It is important to understand that the original version was targeted at smaller companies.
Since then, we have had many (20+) versions. All new versions contained new functionality and with that, the product has gotten more mature and more suitable for bigger companies. This was especially empowered with the support of the Microsoft SQL Server platform allowing more concurrent users to work in the same application areas.
Until version 5.0, the technology of the product did not change. The original intention of Microsoft was to release a new technology platform together with the new functional changes. This turned out to be a very difficult task so they decided to split the improvements into two releases. Version 5.0 contains new functionality and improvements, whilst version 2009 or 6.0 which is the technical release number, is a technology release.
The technical challenge was to migrate from the old C++ platform to .NET and to move from a two tier to a three tier technology. This was also the first release with a drastic change in the user interface. Microsoft Dynamics NAV 2009 contains an entirely new user interface, the "Role Tailored Client", built new from the ground up—the existing ("Classic") user interface is the same with no changes. During this migration process, all application functionality was frozen although small improvements and bug fixes were made in 2009 SP1.
This book supports functionality from both the 5.0 and 2009 release even though we decided to use the new 2009 interface for all user interface screenshots and pages for the development examples. As the development environment is only available in the classic client, we have taken these screenshots from there.
The title of the book is "Microsoft Dynamics NAV 2009 Application Design". What does Application Design mean? And what does it mean in Microsoft Dynamics NAV 2009?
Microsoft Dynamics NAV 2009 is a very complete ERP package, but unlike other ERP packages it has a design capable of providing an open structure and a development platform. The idea is to provide 80% of the solution out of the box and allow the other 20% to be designed by qualified business application developers.
The partner channel is a unique part of Microsoft Dynamics NAV. From the first moment that Navision was introduced, company management decided that it would only make sense to have an indirect selling model and to let the resellers (called partners) have the availability to change the product and add new functionality.
This book is about both the 80% and the 20%. We'll see that the percentages differ as per the industry where it is applied. Some industries have close to a 100% fit while others have a need for 80% development.
So there is a thin line in this book between using the standard application and designing changes and expanding the product. Although this is not a development book, we'll dive into code and objects in almost every chapter.
To understand the code it should be enough to read this chapter but if you want to know more we highly recommend reading "Programming Microsoft Dynamics NAV 2009" written by David Studebaker and also published by Packt.
This book is not a manual for Microsoft Dynamics NAV 2009. It should give a clear idea of how the structure of the application is laid out and about its possibilities. We do not want to replace or rewrite the Microsoft Documentation but rather want to provide ideas you might not have thought about.
In Microsoft Dynamics NAV, the line between implementing and developing is very thin. Where you would do a lot of setup in other ERP packages, you'll see that it often makes more sense in Dynamics NAV to make a change with the Development Tools.
The standard package is very complete in its functionality but does not support all industries. It is more a framework for Partners to work with. In this book, we will explain this framework and what philosophy it is built on. Understanding this philosophy is critical to knowing how to expand the functionality.
But, expanding the functionality means customizing the application. Do end-users in 2009 still want customized applications? Mostly they will say they don't want their software customized, but in the next breath, they will say that the software should change to match their way of doing business, and that they should not have to change their business to fit the software.
This is why Microsoft pushes their Partners to create horizontal and vertical solutions on top of the standard product and release these solutions as products with their own versions like it was part of the standard applications. This way of using the partner channel is a unique concept that has proven to be very successful and has made Microsoft Dynamics NAV useable in almost any industry.
Most companies, however, have such a unique way of working that they will always require more or less customized solutions. The total cost of ownership depends on the level of customizations and how these customizations are designed.
The key is knowing when to do setup and when to do a customization. Only a solid understanding of the application will help you determine which is correct.
After reading this book, you will know how to design your application best to have a good balance between cost of ownership and functionality.
As discussed earlier, the application is designed to be expanded and changed by external partners. When this Partner program was created, a decision was made that partners could only do a good job if the application was completely open for them to add and change. This philosophy is very important to understand when you first start implementing or changing Microsoft Dynamics NAV.
Partners can change all business logic in the application. They can add new fields to tables and create their own tables. The only thing they cannot do is delete fields from the tables in the base application.
As you can see, Microsoft Dynamics NAV is an extremely flexible and open product with a lot of freedom. But with freedom comes responsibilities. In Dynamics NAV, you are responsible for the housekeeping in your system.
Because of this open system, partners have created thousands of smaller and larger changes to the system. Some of these changes were bundled into new functional pieces and called "add-ons". These add-ons are often solutions that change Dynamics NAV into a product for a specific industry rather than a generic ERP system. Other add-ons are specific features that can be used in all industries like EDI or Workflow. Microsoft calls the industry specific add-ons verticals and the generic add-ons horizontals.
Even though Dynamics NAV has an open source for their partners, it does not come fully equipped with a development environment like most developers are used to. It has a customization tool that lets you customize the application like you would customize another ERP system with settings. This customization tool is a basic tool that is nice to work with but misses some development features such as version control and intellisense. This makes it more difficult to keep track of your changes.
This book will cover most functional elements of Dynamics NAV in a number of vertical industries. We will do this in a supply chain matrix. The specific industries we will look at are fashion, automotive, medicines, food, and furniture. For production and trade we will look at the general process and we will see how consultancy and distribution companies help in this process.
The following image shows how this book is structured:
For all these industries we will look at what parts of the standard product can be utilized and where we need vertical solutions. We'll discuss how these vertical solutions will interface with the standard package or maybe even change the behavior of the standard product.
Two parts of the product however are so general in their use and usability for all industries that we'll discuss them in their own chapter. These are Financial Management and Relationship Management.
To emphasize the strength of the vertical concept, we'll design and create a vertical solution for a distribution company.
Now we will look at some of the basic concepts of the application.
With the NAV 2009 release, Microsoft marketing decided to introduce the concept of Role Tailored ERP. Until now, most ERP systems were module driven, which means that the application has an area for finance, CRM, sales, purchasing, and so on. The access to the individual modules was separated. A purchaser needs to switch to sales in order to see the sales orders.
This is a "purchaser's" Role Center. As you can see, all the information needed by this person in the organization is in one place and usable in a workflow-like way. Also, the Sales Orders are accessible from the main menu. It is completely different to the menu found in version 5.0 or before
However, the Role Tailored concept is not new. Dynamics NAV partners have been implementing it for many years. In the classic menu, it was extremely easy to create new menus and most companies implemented their own menus per role. When the 'Microsoft Outlook' style Menu Suites where introduced in version 4.0, end users could create shortcut Menu Suites and these also quickly became role centers. You can clearly see that the role tailored concept is like coming home for Dynamics NAV.
Like all database applications, it starts with tables. They contain all the information displayed in a structured way. It is important to understand that the tables of Microsoft Dynamics NAV are not completely normalized. The tables are structured in the way the user interface works. This makes it easy for non- technical people to understand the data model. We'll discuss the unique structure of the application in the next chapter.
Tables, however, not only contain data, they contain business logic as well. As they are structured like the functionality in the database, tables contain simple functions like address validation, and more complex functions for VAT and discount calculation.
Whenever functionality gets more complex or can be shared across the application, it is better to move them to the Codeunit object. These are containers of business logic for a special purpose.
For the user interface there are three object types: Forms, reports, and pages. The first and latter are intended for user input. Reports are originally intended to be printed on paper but with the current status of technology, they are more and more used as information dashboards combining management information with drill-through possibilities.
Forms and pages are tightly linked to each other. Each form object has a page object with the same number and name. The form object is used in the 'Classic Client' only whilst the pages are used in the 'Role Tailored Client'.
The report object is used in both interfaces but has two layouts—a black and white layout for the Classic client and a RDLC layout for the Role Tailored client that supports colors and graphs.
As the tables are structured in the way the application works, the forms and pages are bound to one table. For people new to this concept, it sometimes takes a while to get used to this.
The last two object types are external interfacing objects. Data ports and XML ports make it possible to import and export data in and out of the system.
For this book, the table and page objects are the most important to understand. Most of this book, however, can also be applied to older versions but then forms should be applied wherever this book addresses pages.
For example the Job Card (88) is built on one table, the Job (167). This table contains all fields required for this screen.
In a traditional development environment this screen would have a transaction
UpdateJobData. These transactions would read the information from the database, map them to the screen, and save the information in the database if the user if finished. However, in Microsoft Dynamics NAV, all fields that are displayed in the interface are stored in one table. This makes it possible for the screen to have built-in triggers to get the data and update the database.
The table object then contains the business logic required for this document. Let's have a look at some of the fields in this table.
Currency Code - OnValidate() IF "Currency Code" <> xRec."Currency Code" THEN IF NOT JobLedgEntryExist THEN CurrencyUpdatePlanningLines ELSE ERROR(Text000,FIELDCAPTION("Currency Code"),TABLECAPTION);
It contains business logic that gets executed every time something happens with this field. In this case, the currency factor is recalculated and updated in the Sales Lines.
So, the tables in Microsoft Dynamics NAV are not just data containers, they are the foundation for both the business logic and the application workflow.
The Dynamics NAV product is used almost everywhere in the business supply chain. This is mainly because it is a highly customizable ERP system. Dynamics NAV is used in the classical supply chain companies like manufacturing plants, wholesale companies, and in retail with or without many changes. But with an add-on, the product is also used in transportation companies or in the recycling industry.
In order to understand this better, it is important to know how companies work. A company is a person or a group of persons using materials and resources to deliver a product or a service to other companies or end consumers. A group of companies working together is called a supply chain. Dynamics NAV can be used in all these companies although it is traditionally used in companies with 5 to 250 concurrent users.
Financial management: Traditionally, financial management was used in companies to comply with federal regulations of bookkeeping. For entrepreneurs starting their business, this is usually the part they least like. However, good bookkeeping can give a clear view on the company's well being and support strategic decisions with good financial information.
Inventory: Every company that grows will reach a certain point where it is no longer possible to handle inventory without a system. Keeping too much inventory is expensive. A good inventory system can help you keep your stock management as efficient as possible.
Relationship management: When it comes to people, a company is not only dealing with customers and vendors. RM will help you keep track of every company and person your company is dealing with.
Sales: The sales process is usually the place where businesses make money. The system will help you keep track of orders that your customers place.
Purchasing: The purchasing department is usually split in two pieces. One piece is the purchasing of goods the company needs for itself. This facility management can grow into a business of its own at large companies. The other purchasing part is buying the materials and resources you need for your sales process. For some trading companies, this can even be a drop shipment process where you never have the purchased goods in house.
Warehouse management: Warehouses are getting bigger and bigger, making the need for a system that supports the picking and put-away process even greater. This is usually tightly connected to the sales and purchasing process.
Manufacturing: When you make products yourself, you need a system that helps you create a new item from one or more purchased materials and resources.
Jobs: In some companies the process of delivering a service is so complex that it requires its own administration process. Time and billing is usually a very important process for these companies.
Microsoft Dynamics NAV has some basic structures that are reused throughout the application and are necessary to understand before you read the rest of this book.
Some tables have automatic incremental numbering that cannot be influenced. These are often accounting tables that have auditable purposes. Examples of these tables are G/L entries, G/L registers, and VAT entries.
The other way is using a flexible alphanumeric code. In some setup tables, users are free to create their own numbers, like in the location table, but most of the time, number series functionality is used. These can be influenced by the end user depending on their access rights. Let's have a closer look at those:
Users can define their own numbering, usually starting with an alphanumeric character. Numbering can be done automatically, manually, or in a combination. Numbers can have a starting date and incremental number. This way you can number your Sales Invoices SI11-0001. SI means Sales Invoice. 11 means 2011 and 0001 is the incremental number.
The text can be defined for all languages in the system and valid for a specific period.
We can enable or disable using the text for most documents available in the system, so we can have a long text for the Sales Quote and a shorter text for the Sales Invoice.
The main reason Microsoft Dynamics NAV consultants like you to use numbers as SI11-0001 is the Navigate functionality. This functionality makes it possible to find all information in the database linked to this document. If you were to call your Sales Invoice 110001 and your Purchase Invoice the same, the system would not be able to find the correct information.
When Navigating on Posted Sales Invoice 103006 in the CRONUS Demo database, we get all the information that is linked to this number.
An ERP application can be used in many different ways and to make it work in the way we want, we need to set it up correctly. We already discussed that Dynamics NAV has far less setup work required than other ERP packages and is more likely to be changed but nonetheless, there is setup work to do.
Every part of the application has its own setup table. There are also some application-wide or cross application setup tables. During the implementation, we need to make sure to touch all of these tables. Changing these setups after the implementation should be done with great care.
This list shows all Microsoft Dynamics NAV setup tables grouped by type.
Specific setup tables
Application wide setup tables
General ledger setup
Sales & receivables setup
Purchases & payables setup
Human resources setup
Production schedule setup
Nonstock item setup
Service mgt. setup
Source code setup
Change log setup
SMTP mail setup
Job queue setup
Online map setup
Interaction template setup
Employee portal setup
Order promising setup
BizTalk management setup
When we open a setup from the application, we see several options, including the numbering we discussed earlier.
Most application areas have one or more posting group tables:
Customer posting group
Vendor posting group
Inventory posting group
Job posting group
Gen. Business posting group
Gen. Product posting group
Bank account posting group
VAT business posting group
VAT product posting group
FA posting group
We'll discuss posting groups in more detail in Chapter 3, Financial Management.
When it comes to pricing and discounts, Microsoft Dynamics NAV has a very simple, yet effective way of calculating prices.
7004—Sales Line Discount
7014—Purchase Line Discount
The system finds the appropriate price by filtering down in these tables. The narrower the filter, the more likely the price is applied.
For example: The normal price of item 1972-W on the item card is 974,80 but from 1-1-2011 it is 843,345.
The filtering is done in Codeunits Sales Price Calc. Mgt. (7000) and Purch. Price Calc. Mgt. (7010). We'll discuss this structure in Chapter 2, An Example Application where we will also create such a structure for our own application.
The application has two global dimensions that are directly posted into each transaction. Six other dimensions can be defined as shortcut dimensions to be directly used in journals and documents. An unlimited number of additional dimensions can be added but need to be accessed with additional effort.
This screenshot shows how Global and Shortcut Dimensions can be used in a Sales Document.
Although the cubes can be updated real time during posting, it is highly recommended to update them periodically in a batch. Also, the number of dimensions has an impact on the performance of the system.
Microsoft Dynamics NAV has some specific data model principles that are very important to understand before you can create your own structure. The building blocks are layered and reused and rely on each other in order to secure data integrity.
The combination of all above combine the information allows us to quickly analyse the created data.
Every transaction starts with a journal. Each journal can contain a number of sub transactions that are treated by the system as one. This way the system is able to check, for example, if the integrity of the system is maintained after the transaction is completed.
Every journal can contain one or more templates with one or more batches, allowing multiple users to have multiple templates and batches. A journal line has a source number field that refers to, for example, the G/L Account number or the Item number we are changing. When we post the journal, the changes are stored in the entry table and a register is maintained for all the lines for the journal allowing auditors to check if the transactions are consistent.
These are created through journals, so let's open a journal.
In any ERP system, totaling and balancing is crucial, whether you are totaling the general ledger, customer payments or inventory, it is important to know the balance of each Account, Customer, or Item.
Traditionally, this requires calculating these balances and deciding a place to store the totals and subtotals. Not in Dynamics NAV. The system has built-in technology that will handle balancing and totaling for you, without effort and cost of performance.
This built-in technology is called Sum Index Flow Technology, SIFT in short. For Dynamics NAV it is the key feature to its success.
The way it works is that, as a developer, you define your totaling on an index level. By associating the totaling fields with a key, the system knows that it has to maintain the totals for you.
In the original proprietary database, this technique was built-in and invisible for the user but in the SQL Server database, we can see how it works.
If we go into the CRONUS database and open the G/L Entry table with its keys ,we see this information.
Let's take key number two as an example. The key contains the fields G/L Account number and Posting Date. If we take a closer look at the SumIndexFields column, we see the following fields listed.
From the SQL Server Management studio you can see the generated data from the
SumIndexField definition. Each key with a
SumIndexField generates a view in the database. In older versions (prior to 5 SP 1) the
SumIndexFields are saved in tables.
So now we know that we do not have to worry about maintaining the totals, we can spend our time on what's really important.
As discussed earlier, screens in Microsoft Dynamics NAV are built directly on one table. These table definitions contain all fields including the totals. However, these totals are not real database fields.
This can be illustrated by comparing the table definition in Microsoft Dynamics NAV to the table definition in the SQL Server.
The fields Date Filter (28) to Budgeted Amount (33) are not actual fields in the database. They are helper fields to show data on screens.
Sum("G/L Entry".Amount WHERE (G/L Account No.=FIELD(No.), G/L Account No.=FIELD(FILTER(Totaling)), Business Unit Code=FIELD(Business Unit Filter), Global Dimension 1 Code=FIELD(Global Dimension 1 Filter), Global Dimension 2 Code=FIELD(Global Dimension 2 Filter), Posting Date=FIELD(UPPERLIMIT(Date Filter))))
This creates the Sum of the field Amount in the G/L Entry table (17) filtering on G/L Account, G/L Account No., Business Unit Code, Global Dimension 1 & 2 Code, and Posting Date.
Some of these filters are actual fields in the G/L Account table, but others are Flow filters. Non-existing fields that can be used as a runtime filter to limit the result of the Query.
We will use and discuss more of these Flow filters and Flow fields in this book.
So now that we know how a journal works, it might be interesting to build a posting diagram of Dynamics NAV. Dynamics NAV has a number of journals, registers, and entries built on top of each other.
These are the most important journals, registers and entries:
Gen. Journal Line (81)
Item Journal Line (83)
Res. Journal Line (207)
Job Journal Line (210)
G/L Register (45)
Item Register (46)
Resource Register (240)
Job Register (241)
G/L Entry (17)
Cust. Ledger Entry (21)
Vendor Ledger Entry (25)
Item Ledger Entry (32)
Job Ledger Entry (169)
Res. Ledger Entry (203)
VAT Entry (254)
Bank Account Ledger Entry (271)
Please notice that when you look in the database you'll find more of these tables, but these are the main building blocks.
Each journal is responsible for creating its own entries but may run another journal if that is required. For example, an Item Journal may generate G/L entries if required using a General Journal and a Job Journal may create Item Ledger Entries using the Item Journal.
We already discussed the G/L Entry table which is used to store the basic financial information. This is the basic administration table.
The other entry tables are sub ledger tables. They store redundant information but have extra information for their specific use. A total of a sub ledger should always balance with the G/L. We'll see how that works in Chapter 3, Financial Management.
The Customer and Vendor ledger entry tables are used to store specific information about the accounts receivables. They are linked to Customer and Vendor master data tables.
The VAT Entry table stores specific information to make registration easier. Most companies do monthly or quarterly VAT registrations with one or more governmental agencies.
VAT is different in many countries and could be different from what this book describes in localized country systems.
The Bank Account entries should show exactly what transactions were carried out on our bank accounts.
The logistical part of the ERP package is handled by the Item Journal. Every item that is purchased, produced, or sold is handled though this journal. Services are handled through the Resource journal. A 'Resource' can either be a person or a piece of equipment, for example a lift.
The Job journal is an umbrella overlaying the entire application. It allows you to group transactions making it easier to analyze cost and profit for larger projects.
The General Journal is the heart of the application where the basic financial information is created in the ledger entries. All the basic information is in the G/L entry table which is grouped in the G/L Register which is always balanced. The Customer, Vendor, VAT, and Bank Account Ledger entries are sub tables that always refer to a G/L register. We can never create one of these entries without touching this part of the application.
When an entry is created, its basic structure should not be changed for audit ability. This is why most entries in Microsoft Dynamics NAV have sub- or detailed entries.
The Customer and Vendor Ledger Entry have details for application, unrealized loss and gain, various discounts, and corrections. This way we are able to keep track of what happens with an entry without changing the original information.
The Item Ledger Entries a have wide variety of sub entries depending on what you are doing with the items.
One of the most important tables in Microsoft Dynamics NAV is the Value Entry table. Each Item Ledger Entry has one or more of these. This table is the 'soft bridge' between the inventory and the financial part of the application.
Warehouse entries enable moving items within our organization without touching the basic inventory or financial application.
Traditionally, companies work with documents. This was also the case before ERP applications were introduced. A sales representative would travel through the country with a paper order block and then come back to the back office. The back office then ships the orders with shipping documents and invoices.
Microsoft Dynamics NAV supports working with documents. Traditionally, we divide the documents in sales and purchasing documents but the later versions of Microsoft Dynamics NAV also have warehouse documents. Other supported documents are reminders and service documents.
The lines contain information about what is sold or purchased. This can be a variety of G/L accounts, items, and resources.
A document can have different stages depending on the type of the transaction. A quote is a typical starting point in the sales or purchasing process. When a quote is approved it can be promoted to an order which is then shipped and invoiced. The process can be also reversed via a return order resulting in a credit memo.
Transactions in the database can be started via documents. When a document is processed the necessary journals are automatically populated. For example, when an order is shipped the goods leave the warehouse, thus an Item Journal is created and posted to handle this. When the invoice is posted, a General Journal is generated to create G/L Entries and a Customer or Vendor Ledger Entries.
The three most important other structures are CRM, Jobs, and Manufacturing. These structures are all 'umbrella' structures for other processes.
We have already seen the Customer, Vendor, and Bank master data records. But what if a Vendor is also a customer or vice versa. We don't want to maintain the same data twice. We might also want to keep extra information of our customers and vendors like contact persons and their interests. We'll see more of that in the RM chapter later.
The job structure in Dynamics NAV allows you to handle this. Every document and journal transaction can be attached to a job making it easy to analyze profit and loss, and even schedule your jobs.
The jobs module also allows you to do a calculation before you start the project and balance this calculation throughout the process.
In this chapter, we have covered the basic structure of Microsoft Dynamics NAV. We talked about the design philosophy, application objects, and the unique table structure. We discussed the role tailored concept and its reflection to older versions of the product. We talked about some basic functions of the product-like number series and application setup. Very important is the basic posting structure and the way SIFT works. We discussed how the document structure is overlaying the journal structure and how the umbrella structure is on top of that. In the next chapter, we will look at a sample industry application and its effect on the standard functionality.