How To: 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
- Database tables
- System tables
- Virtual tables
- 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
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
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 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
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
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
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
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
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
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
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 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 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.