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 |
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".AmountWHERE (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.
