Once upon a time; this is how fairytales often start and even though the story of Microsoft Dynamics NAV is everything but a fairytale, it sure has some magic.
With more than 100,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 provide a better insight where 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 for Microsoft Dynamics NAV.
At the time of publishing this book, Microsoft Dynamics NAV 2013R2, 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 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 it the product has become more mature and suitable for bigger companies as well. 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 new functional changes. This turned out to be a very difficult task so they decided to split the technology into two releases. Version 5.0 contained new functionality and improvements while Version 2009, or 6.0, which is the technical release number, was 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, which is 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.
With Microsoft Dynamics NAV 2013, we have entered a new era where the transformation is complete. The product is converted to .NET and even supports limited use of DotNet Interoperability directly from the C/AL programming language. The classic user interface is discontinued as is the native database.
This book supports functionality from the 2013 release although most concepts relate back to the older versions.
The title of the book is Microsoft Dynamics NAV 2013 Application Design. What does application design mean? And what does it mean in Microsoft Dynamics NAV 2013?
Microsoft Dynamics NAV 2013 is a 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 percent of the solution out of the box and allow the other 20 percent to be designed by qualified business application developers.
The partner channel is a unique part of Microsoft Dynamics NAV. From the moment 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 functionalities.
This book is about both the 80 percent and the 20 percent. We'll see that the percentages differ per industry where it is applied. Some industries have close to a 100 percent fit while others have a need for an 80 percent development.
So there is a very 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.
Reading this chapter will be more than enough to understand the code but if you want to know more, we highly recommend reading Programming Microsoft Dynamics NAV 2013, David A. Studebaker, Christopher D. Studebaker, Packt Publishing.
This book is not a manual for Microsoft Dynamics NAV 2013. It will give you 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 that you might not have thought about.
In Microsoft Dynamics NAV, the line between implementing and developing is very thin. Where you would perform 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.
However, expanding the functionality means customizing the application. Do end users in 2013 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 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 as if they were a part of the standard applications. This way of using the partner channel is a unique concept that has proven to be very successful and make 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 in knowing when to do a 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 proper 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 such as 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 or IntelliSense. This makes it more difficult to keep track of your changes. We will discuss how to use Team Foundation Server for Object Versioning in Chapter 10, Application Design.
Application programming interfaces (APIs) are reusable blocks of code that generally do not change. They are as important to know to work with Dynamics NAV as .NET libraries are to work in C#. Within Microsoft Dynamics NAV, we have several building blocks that are reused but not changed. Examples are address formatting and the navigate page.
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 diagram 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, meaning 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.
Most people in a company have specialized tasks that the ERP system should support. In a classic ERP interface, the users would have to decide themselves the location of the parts that they need. This has changed with the introduction of the Role Tailored concept.
This screenshot shows a purchasers' Role Center. As you can see, all information for 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 window. 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, as seen in the preceding screenshot, it was extremely easy to create new menus and most companies implemented their own menus per role. When the Microsoft Outlook style menu was introduced in Version 4.0, end users could create shortcut Menu Suites, which 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 nontechnical people to understand the data model. We'll discuss the unique structure of the application in Chapter 2, A Sample Application.
Tables, however, not only contain data, but 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. Tables can also be used as a class without storing data. This allows more structured programming.
For the user interface, there are two object types: reports and pages. 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.
As the tables are structured in the way the application works, the pages are bound to one table. For people new to this concept, it sometimes takes a while to get used to this.
The last object type is an external interface object. XML ports make it possible to import and export data in and out of the system.
Query objects are introduced in Microsoft Dynamics NAV 2013 and allow developers to define SQL Server SELECT statements on the metadata level that can be used in C/AL code. It is possible to join multiple tables into one query. Query objects can also be exposed as OData web services.
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.
In a traditional development environment, this screen would have a transaction GetJobData and 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 is 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:
When we click on the C/AL Code icon and we focus on currency code, we get the following result:
Currency Code - OnValidate()
IF "Currency Code" <> xRec."Currency Code" THEN
IF NOT JobLedgEntryExist THEN
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, such as 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 five to 250 concurrent users.
- Financial management: Traditionally, this 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 wellbeing 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 very expensive. A good inventory system can help you keep your stock as efficient as possible.
- Relationship management (RM): 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 purchase 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.
- Service management: This supports the service process handling warranty and necessary periodical maintenance of your items.
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 them:
Users can define their own numbering, usually starting with an alphanumeric character. Numbering can be done automatically, manually, or a combination of the two. 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.
For example, number series can be linked to each other making it possible to have a different number series for national and international customers.
We can enable or disable using the text for most documents available in the system, so we can have some long text for the Sales Quote and some shorter text for the Sales Invoice, as shown in the following screenshot:
The main reason Microsoft Dynamics NAV consultants like you to use numbers like SI11-0001 is because of the Navigate functionality. This functionality makes it possible to find all information in the database linked to this document. If you name your Sales Invoice 110001 and your Purchase Invoice the same, the system would not be able to find the information at a detailed level.
Navigation shows both documents and entries. Using the Show option, we can drill down into the records and go even deeper into the information. Navigation is present at most pages that show posted transactions and historical data.
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 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.
The setup tables use the singleton table design pattern. The following table 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 some 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.
All sales and purchase prices are stored in four simple tables:
- 7002 – Sales Price
- 7004 – Sales Line Discount
- 7012 – Purchase Price
- 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 97,480, 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, A Sample 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.
The preceding 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.
Dimensions were redesigned in Microsoft Dynamics NAV 2013. The redesign has a huge impact on application performance and can reduce database size up to 30 percent.
Microsoft Dynamics NAV has some specific architectural design patterns 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.
An example of a supplemental table is the item vendor table.
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, whether 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 all the lines. For the journal, a register is maintained 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, and 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. In Dynamics NAV, the system has built-in technology that will handle balancing and totaling for you.
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 this works.
If we go in the CRONUS database and open the G/L Entry table with its keys, we see this information, as shown in the following screenshot:
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 SumIndexField generates a view in the database. In older versions (prior to 5 SP 1), the SumIndexFields are saved in tables.
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.
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.
We will use and discuss more of these flow filters and flow fields later in this book.
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.
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. Here are some more tables:
- 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 we do 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 like a lift.
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 then 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.
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.
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, then 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 previously discussed pattern with journals and documents is by far the most important transaction structure. But Microsoft Dynamics NAV also has other structures as well.
The three most important other patterns are CRM, jobs, and manufacturing. These areas 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 such as contact persons and the interests of our customers and vendors. We'll see more of that in Chapter 4, Relationship Management.
There is also a need to analyze the data we have created with the document and journal structure.
Sometimes, a project can be more comprehensive than just a purchase and/or a sales document. A project can take from several weeks to over a year and requires multiple documents.
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 manufacturing module of Microsoft Dynamics NAV allows you to handle this process. Basically what it does is create an item out of one or more other items and resources.
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. We also talked about the important basic posting structure and the way SIFT works. We discussed how the document structure overlays 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.