Home Business & Other Microsoft Dynamics NAV 2009 Application Design

Microsoft Dynamics NAV 2009 Application Design

By Marije Brummel
books-svg-icon Book
eBook $39.99 $27.98
Print $48.99
Subscription $15.99 $10 p/m for three months
$10 p/m for first 3 months. $15.99 p/m after that. Cancel Anytime!
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
BUY NOW $10 p/m for first 3 months. $15.99 p/m after that. Cancel Anytime!
eBook $39.99 $27.98
Print $48.99
Subscription $15.99 $10 p/m for three months
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
  1. Free Chapter
    Chapter 1: Introduction to Microsoft Dynamics NAV
About this book
Dynamics NAV 2009 is an Enterprise Resource Planning (ERP) software product from Microsoft that can be used for variety of business needs. It is part of the Microsoft Dynamics family, and intended to assist with finance, manufacturing, Customer Relationship Management, supply chains, analytics, and electronic commerce for small and medium-sized enterprises. This book is a focused tutorial on Microsoft Dynamics NAV application development, so you can develop complete applications and not just application outlines. It will show NAV developers how to create different kinds of applications. Different kinds of application are vital in different industries like fashion, automobile, retail, books (education), and other industries. It starts off by introducing the supply chain that you will be using throughout the book. You will implement the Microsoft Dynamics NAV ERP suite and learn how it is set up and customized for various industries. You will be able to customize Dynamics NAV to suit the different aspects of a business such as Financial Management, Relationship Management, Production, Jobs, Trade, Storage, Logistics, and so on. The book will take you through these Microsoft-designed application features and show you how to customize and extend them safely. Thus, you will be able to create a structure of your own in Microsoft Dynamics NAV.
Publication date:
June 2010
Publisher
Packt
Pages
496
ISBN
9781849680967

 

Chapter 1: Introduction to Microsoft Dynamics NAV

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.

 

Versions and history

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.

 

What is this book about

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.

 

Setup versus customization

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.

 

The beauty of simplicity

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.

Horizontal versus vertical solutions

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.

Open source

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.

 

Structure of this book

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.

 

The Role Tailored concept

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.

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 which parts they need. This has changed.

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.

 

The building blocks

To understand the development examples in this book, we will discuss some of the basic building blocks of Microsoft Dynamics NAV 2009.

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 Menu Suite defines the way the navigation is structured when people leave their Role Centers, or when using the Classic Client which does not support Role Centers.

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.

Tables as user interface and business logic

The table object in Microsoft Dynamics NAV is very important. As it is not normalized, it contains a lot of information about how the database works.

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 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 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.

In this table you will see see a lot of fields that are required for a Job like WIP Method, Currency Code, and so on. But when we click on the C/AL Code icon and focus on Currency Code we get this.

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.

Dynamics NAV in throughout supply chain

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.

In order to serve this process, Dynamics NAV has a list of basic modules:

  • 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.
  • Service management: This supports the service process handling warranty and necessary periodical maintenance of your items.

Some basics

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.

Number series

Databases need unique records. The application has two ways of making this happen.

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.

Number series can be linked to each other making it possible to have a different number series for national and international customers.

Extended text

Most master data tables in Microsoft Dynamics NAV have two description fields but it is possible to add extra text.

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.

Navigate

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.

Navigation shows both documents and entries. Using the Show option, we can drill down into the records and go even deeper into the information.

Setup tables

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.

When we open a setup from the application, we see several options, including the numbering we discussed earlier.

Posting groups

Microsoft Dynamics NAV is very flexible in its posting to the General Ledger. This is set up in posting groups. These form a matrix which is filtered out by the application.

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.

Pricing

When it comes to pricing and discounts, Microsoft Dynamics NAV has a very simple, yet effective way of calculating prices.

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 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.

Dimensions

Throughout the application, an unlimited number of dimensions can be used to analyze the data. These dimensions are inherited from master data tables.

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.

As discussed earlier, Microsoft Dynamics NAV has built-in OLAP possibilities. It allows us to create cubes to be analyzed within the application or in SQL Server analysis services. .

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.

 

Data model principles

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.

Master data

The data model starts with master data. There are three types or levels of master data. They are all used in transactions. We differentiate helpers, core, and umbrella master data.

Examples of helper master data are Currencies, Locations, and Payment terms. They often do not use a number series but allow us to create our own unique codes.

Examples of core master data are G/L Accounts, Customers, Vendors, Items, Resources, and Fixed assets. They are numbered using number series and have their own journal structure.

Umbrella master data consists of data tables such as Contacts, Jobs, and Production orders. They allow us to group other master data and documents.

The combination of all above combine the information allows us to quickly analyse the created data.

Journals

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.

This diagram shows how a journal is structured. PK means Primary Key which is the unique identifier of the table.

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.

The general ledger

To see how this works in the application we can best go to the Chart of Accounts and the General Journals.

If we select G/L Account 1140 and drill down, we see the details of this record.

These are created through journals, so let's open a journal.

This journal contains two documents on the same posting date and the balance is zero. When we post this journal, the system will create the ledger entries and a register.

This is the basic building block for Dynamics NAV. Everything in Dynamics NAV is built on top of a journal, registers, and entries.

Balancing

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.

Notice that these are all fields of type decimal. This is mandatory for SumIndexfields.

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.

Flow fields and flow filters

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.

Flow filters can have seven types; Sum, Average, Exist, Count, Min, Max, and Lookup and contain a Query to the database. For example, Balance at Date (31) shows:

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.

More journals and entries

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:

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.

Posting Schema

When we combine all this information in a schema, we can create the following basic Microsoft Dynamics NAV posting schema.

Here you can clearly see what journal is responsible for creating what entry. An entry table is always maintained by one process.

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.

Sub and detailed entries

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.

Documents—combining the journals into processes

The journal and entry tables make it possible for us to do the basic balancing in our company but people in companies are not used to working with journals.

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.

Document structure

A document in Microsoft Dynamics NAV always has a header and lines. The header contains the basic information about the transaction like shipment dates, addresses, and payment terms.

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.

Document transactions

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.

Other structures

The previously discussed structure with journals and documents is by far the most important transaction structure. But Microsoft Dynamics NAV has other structures as well.

The three most important other structures are CRM, Jobs, and Manufacturing. These structures are all 'umbrella' structures for other processes.

Relationship management

Microsoft Dynamics NAV RM helps you to maintain master data and analyze transactional data. It is both at the very start of the data process and at the end.

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.

There is also a need to analyze the data we have created with the document and journal structure.

Jobs

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 jobs module also allows you to do a calculation before you start the project and balance this calculation throughout the process.

Manufacturing

When you produce your own items, you have different needs in your ERP process than when you only purchase the items you sell.

The manufacturing module of Microsoft Dynamics NAV allows you to handle this process. Basically what it does is create an item out of one of the other items and resources.

 

Summary

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.

About the Author
  • Marije Brummel

    Author, programmer, consultant, project manager, presenter, evangelist, sales person, and a trainer. It's next to impossible to find someone as experienced as Marije Brummel in the Business Central community. Marije received the Microsoft MVP and the NAVUG All-Star awards among several others. She has chaired the Dynamics Credentialing committee and has authored official Microsoft Exam materials. She's the go-to girl for performance troubleshooting and upgrade challenges. One of her biggest achievements was introducing Design Patterns into the Business Central community. Her books, blog articles, and YouTube videos have influenced almost everyone involved with Business Central. She enjoys the outdoors with her dog and loves spending time with her family.

    Browse publications by this author
Latest Reviews (1 reviews total)
Dobra pozycja.
Microsoft Dynamics NAV 2009 Application Design
Unlock this book and the full library FREE for 7 days
Start now