Customizing Microsoft Dynamics Business Central on Premises Using Tables and Table Types

Customizing Microsoft Dynamics Business Central on Premises Using Tables and Table Types

In Microsoft Dynamics 365 Business Central, there is a table where customer information is stored. In addition, there is a table for vendor data, a table for item data, and so on. Tables let you organize and structure the data within your solution. There are different types of tables based on their technical implementation as well as their functional use.

Table types by technical implementation

  1. Database tables
  2. System tables
  3. Virtual tables
  4. Temporary tables.

Type 1: Database Tables

Most tables in the database are database tables. You will have read and write privileges on database tables.

Examples: Customer table, Vendor table, and Item table.

Figure 1 – Example for Database Table in Dynamics Business Central on Premises

Type 2: System Tables

System tables are a special kind of table, because these tables and their data are necessary for the system to function. They are saved in the database and they will have object numbers above two million.

Examples: Profile table, Company table, and Permission table.

Figure 2 – Example for System Table n Dynamics Business Central on Premises
Figure 2 – Example for System Table n Dynamics Business Central on Premises

Type 3: Virtual Table

In this table the data is created at runtime, so it’s not stored in the database. As a developer will not have write privileges to virtual tables. The tables are read-only, therefore, information present in these tables cannot be changed. You cannot insert, modify, or delete information in virtual tables. You cannot view the virtual table or run it from table object designer. However, you can create a page with the source table view property as any virtual table name and run it.

Example: Active Session Table

Figure 3 – Example for Virtual Table n Dynamics Business Central on Premises
Figure 3 – Example for Virtual Table n Dynamics Business Central on Premises

Type 4: Temporary Table

A temporary table only exists in the memory of a client. It keeps an image or a copy of another database table. It has no data and it’s frequently used in posting routines and document reports.

Example: Item Journal

Figure 4 – Example for Temporary Table before posting n Dynamics Business Central on Premises
Figure 4 – Example for Temporary Table before posting n Dynamics Business Central on Premises

Figure 5 – Example for Temporary Table after posting in Dynamics Business Central on Premises; notice that the Document No. has been incremented and the Quantity has reset to zero
Figure 5 – Example for Temporary Table after posting in Dynamics Business Central on Premises; notice that the Document No. has been incremented and the Quantity has reset to zero

Table types by functional use

Type 1: Master tables

Master tables contain information about the most important entities of that module, the primary focus subjects of its functional area. For instance, if you don’t have an item table, you can’t do anything in sales. Similarly, if you don’t have a vendor table, then you can’t create the purchase order. These primary tables are referred to as Master Tables.

Examples: Vendor, Customer, and Item tables

Figure 6 – Example of the Vendor Master Table in Dynamics Business Central on Premises
Figure 6 – Example of the Vendor Master Table in Dynamics Business Central on Premises

Type 2: Supplemental Tables

Supplemental Tables are not as important as Master Tables, but they give extra information to the master data. You can store data related to countries, like country code and description, in supplemental tables.

Examples: Language and Currency tables

Figure 7 – Example of a Language Supplemental Table in Dynamics Business Central on Premises
Figure 7 – Example of a Language Supplemental Table in Dynamics Business Central on Premises

Type 3: Setup Tables

When you create a solution, you will probably need to store configuration information for a certain module. You can set up and organize the data using a Setup Table.

Example: Sales and Receivables Setup, G/L Setup

Figure 8 – Example of a Setup Table in Dynamics Business Central on Premises
Figure 8 – Example of a Setup Table in Dynamics Business Central on Premises

Type 4: Register tables

Register Tables function as a table of contents for its corresponding ledger tables. It registers historical or transactional types of information.

 Examples: Item Register tables, G/L Register

Figure 9 – Example of a Register Table in Dynamics Business Central on Premises
Figure 9 – Example of a Register Table in Dynamics Business Central on Premises

Type 5: Subsidiary Tables

These tables contain a combination of information from master and/or supplemental tables, like item/vendor information.

Examples: Item Vendor, FA Depreciation Book tables

Figure 10 – Example of a Subsidiary Table in Dynamics Business Central on Premises
Figure 10 – Example of a Subsidiary Table in Dynamics Business Central on Premises

Type 6: Ledger Tables

Ledger Tables let you find the transactional information of that functional domain. When you post the item journal, the item ledger entry will be created. When you post the cash or vendor payment journal, the general ledger entry will be created. It will hold your history information. After the transaction is completed, all the information will be in ledger table.

Examples: Customer Ledger Entry, Item Ledger Entry tables

Figure 11 – Example of a Ledger Table in Dynamics Business Central on Premises
Figure 11 – Example of a Ledger Table in Dynamics Business Central on Premises

Type 7: Journal Tables

All transactions are booked through journals, so it is the primary transactional entry. Journal Tables contain data to perform specific tasks. For example, the item journal is used to increase or decrease inventory with a negative and positive adjustment. When you post your transaction, the data is deleted from the table, so you don’t see the item journal line after posting. That is, they are used to perform certain tasks, and they hold data only until you post the information. Journal Tables usually have a specific name that ends with journal line, and they have specific type of primary key.

Examples: Purchase Journal, Item Journal tables

Figure 12 – Example for Journal Table in Dynamics Business Central on- Premises
Figure 12 – Example for Journal Table in Dynamics Business Central on- Premises

Type 8: Document Tables

When you perform certain transactions, you will need documents, for example, a sales quote and a sales order. Document Tables are secondary transactional tables. These tables are always built out of two tables—a table with the header information and a table with the line details. When the customer is placing an order, you want to store common information in the Sales Header, such as: When the order is created, when it’s placed, when it is ready to ship, and when it is delivered. The Sales Line holds information like, “What are the items we need?” or “What are the different prices we selling those contains in sales line?”

Example: Sales Header and Sales Lines—contain information like Sales Orders and Sales Quotes

Figure 13 – Example for Sales Header Table of Document Table in Dynamics Business Central on Premises
Figure 13 – Example for Sales Header Table of Document Table in Dynamics Business Central on Premises

Figure 14 – Example for Sales Line Table of Document Table in Dynamics Business Central on Premises
Figure 14 – Example for Sales Line Table of Document Table in Dynamics Business Central on Premises

Type 9: Document History Tables

The Document History Tables are the historical version of the document tables. When you post documents from the document tables, they will go through a journal table and end up in a document history table. That means when you create a sales order or a sales invoice,  it will move to the corresponding history table once you post it. It will also create ledger entries, but the details will be available in the document history table.

Examples: Sales Invoice Header and Sales Invoice Lines

Figure 15 – Example of the Sales Invoice Header of a Document History Table in Dynamics Business Central on Premises
Figure 15 – Example of the Sales Invoice Header of a Document History Table in Dynamics Business Central on Premises

Figure 16 – Example of the Sales Invoice Line of a Document History Table in Dynamics Business Central on Premises
Figure 16 – Example of the Sales Invoice Line of a Document History Table in Dynamics Business Central on Premises

If you have any questions about Tables and their uses in Microsoft Dynamics NAV or Business Central, contact ArcherPoint.

Read more “How To” blogs from ArcherPoint for practical advice on using Microsoft Dynamics Business Central or NAV.

If you are interested in NAV/Business Central development, check out our collection of NAV and Business Central Development Blogs.

Trending Posts

Stay Informed

Choose Your Preferences
First Name
*required
Last Name
*required
Email
*required
Subscription Options
Your Privacy is Guaranteed