Different TABLES in NAV

Microsoft Dynamics NAV has three types of tables that serve special purposes. The types of tables are:

·         Temporary 

·         System 

·         Virtual

Temporary tables are used as a repository for temporary information at run time. System and virtual tables are system generated tables that provide information about the current state of the system.

Temporary Tables

A temporary table is a temporary variable that holds a table. A temporary table is used as a buffer for table data in your C/AL programs. For more information about C/AL, see Introduction to C/AL.

You can use a temporary table just like you use a database table. The differences between a temporary table and a database table are as follows:

·         A temporary table is not stored in the database, but is only held in memory until the table is closed.

·         The write transaction principle that applies to a database table does not apply to a temporary table. For more information, see Write Transactions and Recovery.

Advantage of a Temporary Table

The advantage of using a temporary table is that all the interaction with a temporary table occurs on the client. This reduces the load on both the network and the server.

When you want to perform many operations on the data in a specific table in the database, you can load the data into a temporary table when you modify it. Loading the data into a temporary table speeds up the process because all the operations are performed locally on the client.

Defining and Using a Temporary Table

You must define the temporary table before you can use it in your C/AL code. The variable that holds a temporary table is defined just like any other global or local variable.

To define a temporary table

1.       On the Tools menu, choose Object Designer, and then create a new table.

For information about how to create a table, see How to: Create a Table.

2.       Choose View, and then choose C/AL Globals or C/AL Locals, depending on whether your variable will be global or local.

If you choose C/AL Globals, the C/AL Globals window appears

3.       Enter a name for the temporary table variable, and enter or select Record as the data type. Use the up arrow in the Subtype field to select the table to copy.

4.       With the cursor still on the line that defines the temporary table, choose View, and then choose Properties (Shift+F4) to display the Properties window.

5.       Change the value of the Temporary property to Yes.

After you have created a temporary table, you can use it in your C/AL code. You can apply filters and perform searches just as you can with a database table.

System Tables

System tables are stored in the database just like normal database tables. However, unlike normal database tables, they are created automatically. The information in system tables is closely related to the DBMS, which uses the system tables to manage, for example, system security and permissions in C/SIDE.

You can read, write, modify, and delete the information in system tables.

There are eight system tables in C/SIDE. The following six tables handle system security:

·         User System Table

·         User Role System Table

·         Permission System Table

·         Windows Access Control System Table

The following two tables define data structure:

·         Company System Table

·         ($ T_2000000203 Database Key Groups $)

Virtual Tables

A virtual table contains system information. You cannot change the data in virtual tables. You can only read the information. Virtual tables are not stored in the database but are computed by Microsoft Dynamics NAV at run time.

Using Virtual Tables

You can use the same methods to access information in virtual tables as you use when you are working with ordinary tables. For example, you can use filters to get subsets or ranges of integers or dates from the Integer virtual table or the Date virtual table.

A system administrator usually uses these virtual tables. These tables give the system administrator information about the users who are currently connected to the database and the current state of the system.

The virtual tables provide such information as:

·         Integers in the range –1,000,000,000 to 1,000,000,000.

·         Dates in a given period.

·         Overview of the operating system files.

·         Overview of the logical disk drives.

·         Trace of database requests from your client to the database.

·         Overview of the users who are currently connected to the database.

·         Overview of the operating system files that store the database.

Because virtual tables are not stored in the database, you cannot view them directly. To view a virtual table, you must create a list page based on the virtual table. For more information, see How to: Create a Page to View a Virtual Table.