The Master table type contains primary data (such as Customers, Vendors, Items, Employees, and so on). In any enhancement project, these are the tables that should be designed first because everything else will be based on these tables. When working on a modification, necessary changes to Master tables should be defined first. Master tables always use card pages as their primary user input method. The Customer table is a Master table. A Customer record is shown in the following screenshot:

The preceding screenshot shows how the card page segregates the data into categories on different FastTabs (such as General, Communication, and Invoicing) and includes primary data fields (for example, No., Name, Address), reference fields (for example, Salesperson Code, Responsibility Center), and a FlowField (for example, Balance (LCY)).

The Journal table type contains unposted activity detail—data that other systems refer to as transactions. Journals are where most repetitive data entry occurs in NAV. In the standard system, all Journal tables are matched with corresponding Template tables (one Template table for each Journal table). The standard system includes journals for Sales, Cash Receipts, General Journal entries, Physical Inventory, Purchases, Fixed Assets, and Warehouse Activity, among others.

The transactions in a Journal can be segregated into batches for entry, edit review, and processing purposes. Journal tables always use Worksheet pages as their primary user input method. The next two screenshots show two Journal Entry screens. They both use the General Journal table, but each has quite a different appearance, and are based on different pages and different templates.

Comparing the preceding two screenshots, the differences include not only which fields are visible, but also what logic applies to data entry defaults and validations.

The Template table type operates behind the scenes, providing control information for a Journal, which operates in the foreground. By using a Template, multiple instances of a Journal can each be tailored for different purposes. Control information contained in a Template includes the following:

For example, General Journal Templates allow the General Journal table to be tailored in order to display fields and perform validations that are specific to the entry of particular transaction categories such as Cash Receipts, Payments, Purchases, Sales, and other transaction entry types. Template tables always use tabular pages for user input. The following screenshot shows a list of the various General Journal Templates defined in the Cronus International Ltd. demonstration database:

In addition to the Templates, there are Batch tables which allow us to set up any number of batches of data under each journal template. The Batch, Template, Journal Line structure provides a great deal of flexibility in data organization and definition of required fields while utilizing a common underlying table definition (the General Journal).

The Ledger table type contains posted activity detail: the data other systems call history. NAV data flows from a Journal through a Posting routine into a Ledger. A significant advantage of NAV Ledger design is the fact that it allows retention of all detail indefinitely. While there are routines supporting compression of the Ledger data, if at all feasible we should retain the full historical detail of all activity. This allows users to have total flexibility for historical comparative or trend data analysis.

Ledger data is considered accounting data in NAV. We are not allowed to directly enter the data into a Ledger or change the existing data in a Ledger, but must "Post" to a Ledger. Posting is done by creating Journal Lines, validating the data as necessary, then posting those journal lines into the appropriate ledgers. Although we can physically force data into a Ledger with our Developer tools, we should not do so.

Because Ledger data is accounting data, we are not permitted to delete data from a Ledger table. Corrections are done by posting adjustments or reversing entries. We can compress or summarize Ledger data (very carefully), eliminating detail, but we should not change anything that would affect accounting totals for money or quantities.

User views of Ledger data are generally through use of List pages. The following screenshots show a Customer Ledger Entries list (financially oriented data) and an Item Ledger Entries list (quantity-oriented data). In each case, the data represents historical activity detail with accounting significance. There are other data fields in addition to those shown in the following screenshots. The fields shown here are representative. The users can utilize page-customization tools (which we will discuss in Chapter 4, Pages - the Interactive Interface) in order to create personalized page displays in a wide variety of ways. First, the Customer Ledger Entries list:

Second, the Item Ledger Entries list:

The Customer Ledger Entries page displays critical information such as Posting Date (the effective accounting date), Document Type (the type of transaction), Customer No., and the Original and Remaining Amount of the transaction. The record also contains Entry No., which uniquely identifies each record. The Open entries are those where the transaction amount has not been fully applied, such as an Invoice amount not fully paid or a Payment amount not fully consumed by Invoices.

The Item Ledger Entries page displays similar information pertinent to inventory transactions. As previously described, Posting Date, Entry Type, and Item No., as well as the assigned Location for the Item, control the meaning of each transaction. Item Ledger Entries are expressed both in Quantity and Amount (Value). Open entries here are tied to Remaining Quantity, such as material that has been received but is still available in stock. In other words, the Open entries represent current inventory. Both the Customer Ledger Entry and Item Ledger Entry tables have underlying tables that provide additional details for entries affecting values.

The Reference (also called Supplemental) table type contains lists of codes, descriptions, or other validation data. Reference table examples are postal zone codes, country codes, currency codes, currency exchange rates, and so on. Reference tables are often accessed by means of one of the Setup menu options because they must be set up prior to being used for reference purposes by other tables. In our WDTU example, tables 50001 Radio Show Type and 50007 Publisher are Reference tables.

The following screenshots show some sample Reference tables for Locations, Countries, and Payment Terms. Each table contains data elements that are appropriate for its use as a Reference table, plus, in some cases, fields that control the effect of referencing a particular entry. These data elements are usually entered as part of a setup process and then updated over time as appropriate.

Location List in the preceding screenshot is a simple validation list of the Locations for this implementation. Usually, they represent physical sites, but depending on the implementation, they can also be used simply to segregate types of inventory. For example, locations could be Refrigerated versus Unrefrigerated, or there could be locations for Awaiting Inspection, Passed Inspection, and Failed Inspection.

The Countries/Regions list in the preceding screenshot is used as validation data, defining the acceptable country codes. It also provides control information for the mailing Address Format (general organization address) and the Contact Address Format (for an individual contact's address).

The Payment Terms table shown in the following screenshot provides a list of payment terms codes along with a set of parameters that allows the system to calculate specific terms. In this set of data, for example, the 1M (8D) code will yield payment terms of due in 1 month with a discount of 2% applied for payments processed within 8 days of the invoice date. In another instance, 14D payment terms will calculate the payment as due in 14 days from the date of invoice with no discount available.

The Register table type contains a record of the range of transaction ID numbers for each batch of posted Ledger entries. Register data provides an audit trail of the physical timing and sequence of postings. This, combined with the full detail retained in the Ledger, makes NAV a very auditable system because we can see exactly what activity was done and when it was done.

The user views Register through a tabular page, as shown in the previous screenshot. We see that each Register entry has Creation Date, Source Code, Journal Batch Name, and the identifying Entry No. range for all the entries in that batch. Another NAV feature, the Navigate function, which we will discuss in detail in Chapter 4, Pages - the Interactive Interface, also provides a very useful auditing tool. The Navigate function allows the user (who may be a developer doing testing) to highlight a single Ledger entry and find all the other Ledger entries and related records that resulted from the posting that created that highlighted entry.

The Posted Document type contains the posted copies of the original documents for a variety of data types such as Sales Invoices, Purchase Invoices, Sales Shipments, and Purchase Receipts. Posted documents are designed to provide an easy reference to the historical data in a format similar to what would have stored in paper files. A Posted Document looks very similar to the original source document. For example, a Posted Sales Invoice will look very similar to the original Sales Order or Sales Invoice. The Posted Documents are included in the Navigate function.

The following screenshots show a Sales Order before Posting and the resulting Posted Sales Invoice document. Both documents are in a header/detail format, where the information in the header applies to the whole order and the information in the detail is specific to the individual Order Line. As part of the Sales Order page, there is information displayed to the right of the actual order. This is designed to make the user's life easier by providing related information without requiring a separate lookup action.

First, we see the Sales Order document ready to be Posted:

The following screenshot is that of the partial shipment Sales Invoice document after the Invoice has been posted for the shipped goods:

The Setup table type contains system or functional application control information. There is one Setup table per functional application area, for example, one for Sales and Receivables, one for Purchases and Payables, one for General Ledger, one for Inventory, and so on. Setup tables contain only a single record. Since a Setup table has only one record, it can have a primary key field which has no value assigned (this is how all the standard NAV Setup tables are designed). The Singleton (Setup) table Design Pattern can be found at:

https://community.dynamics.com/nav/w/designpatterns/151.singleton-table.

The Inventory Setup page is shown in the following screenshot:

The Temporary table is used within objects to hold temporary data. A Temporary table does not exist outside the instance of the object where it is defined using a permanent table as the source of the table definition. The Temporary table has exactly the same data structure as the permanent table after which it is modeled.

Temporary tables are created empty when the parent object execution initiates, and they disappear along with their data when the parent object execution terminates (that is, when the Temporary table variable goes out of scope).

Temporary tables are not generally accessible to users except on a display only basis. They can directly be the target of Reports, Pages, and XML ports. In general, Temporary tables are intended to be work areas and as such, are containers of data. The definition of a Temporary table can only be changed by changing the definition of the permanent table on which it has been modeled. A YouTube video was created about Temporary Dataset usage in NAV 2013 R2. It is still applicable. It is located at https://www.youtube.com/watch?v=QHn5oEOJv0Q.

The System table type contains user-maintainable information that pertains to the management or administration of the NAV application system. System tables are created by NAV; we cannot create System tables. However, with full developer license rights, we can modify System tables to extend their usage. With full system permissions, we can also change the data in System tables.

An example is the User table, which contains user login information. This particular System table is often modified to define special user access routing or processing limitations. Other System tables contain data on report-to-printer routing assignments, transaction numbers to be assigned, batch job scheduling, and so on. The following are examples of System tables for which definition and content can be modified. The first three relate to system security functions.

The following tables are used to track a variety of system data or control structures:

The following tables contain information about various system internals. Their explanation is outside the scope of this book.

The Virtual table type is computed at runtime by the system. A Virtual table contains data and is accessed like other tables, but we cannot modify either the definition or the contents of a Virtual table. We can think of the Virtual tables as system data presented in the form of a table so it is readily available to C/AL code. Some of these tables (such as the Database File, File, and Drive tables) provide access to information about the computing environment. Other Virtual tables (such as the Table Information, Field, and Session tables) provide information about the internal structure and operating activities of our database. A good way to learn more about any of these tables is to create a list or card page bound to the table of interest. Include all the fields in the page layout, Save the page and Run it. We can then view the field definition and data contents of the target virtual table.

Some virtual tables (such as Date and Integer) provide tools that can be used in our application routines. The Date table provides a list of calendar periods (such as days, weeks, months, quarters, and years) to make it much easier to manage various types of accounting and managerial data handling. The Integer table provides a list of integers from -1,000,000,000 to 1,000,000,000. As we explore standard NAV reports, we will frequently see the Integer table being used to supply a sequential count in order to facilitate a reporting sequence (often in a limited numeric range such as 1 or 1 to 10).

We cannot see these tables presented in the List of Table objects, but can only access them as targets for Pages, Reports, or Variables in C/AL code. Knowledge of the existence, contents, and usage of these Virtual tables is not useful to an end user. However, as developers, we will regularly use some of the Virtual tables. There is educational value in studying the structure and contents of these tables, as well as having the ability to create valuable tools with knowledge of and by accessing of one or more Virtual tables.

The following screenshot shows a list of most of the Virtual and System tables: