by John Ellis, Pre-Sales Technical Consultant

Examining Microsoft Dynamics 365 Business Central tables allows you to:

• conduct steps in troubleshooting performance issues,
• generate reports, and
• review valuable datasets all within one window.

Access the list of tables, by clicking the Tell Me button and entering “table information”:

Here in the Table Information window not only can you review tables’ properties, but you can also even search for and export data to certain applications such as Microsoft Excel.

Table Information displays the amount of data that each table contains and in which company.

Since this window displays the distribution of data size across tables, reviewing this window is useful for troubleshooting performance problems.

The following chart describes the data displayed for each table:

Column	Description
Company Name	The name of the company, if any, that the table belongs to
Table Name	The name of the table
Table No.	The ID of the table
No. of Records	The total number of records stored in the table
Record Size	The average record size in KB/record (This value is calculated using the following formula: 1024(Size)/(No. of Records)).
Size (KB)	The total amount of space the table occupies in the database (This value is the sum of the values in the Data Size and Index Size fields.)
Data Size (KB)	How much space the data in the table occupies in the database
Index Size (KB)	How much space the table indexes (keys) occupy in the database
Compression	The type of compression, Row, Page, or None that is applied to the table in the database

Clicking “682” in the “No. of Records” column of the Table Information window will display the Inventory Subledger.

Clicking “694” in the “No. of Records” column of the Table Information window will display Inventory Valuation.

Both objects are seen in the two respective screenshots, on the next page.

 

Security to Table Information

Your Dynamics 365 user account must have the following permissions, to access Table Information:

• Read permission on the table that you want to view and
• Execution permission (direct) on the System object 1350 Run table.

So, place the following Permissions Set in the appropriate User Group:

Caution: Restrict access to this window because it’s easy to accidentally click the “Data Administration” button in Table Information to pull up a window for deleting data.

If you delete data from a table, Business Central ignites several processes to make sure that everything is cleaned up in your database.  The values in Table Information will not update until those processes are complete.  This can take a while.  The amount of time you’ll have to wait can vary, depending on the size of your database.

 

View a Table Directly within Business Central

To view a table directly within Business Central you add the table=<TableID> parameter to Business Central’s URL, replacing <TableID> with the ID of the table that you want to view.

If your URL starts with https://businesscentral.dynamics.com then, to view the customer master table in your current company, use the following URL:

https://businesscentral.dynamics.com/?table=18

Or, to do so for a specific company such as “CRONUS Inc.”, use the following URL:

https://businesscentral.dynamics.com/?company=CRONUS%20Inc.&table=18

Note the use of “&” when table=<TableID> is not located directly after the domain name.

 

Other Means of Seeing Table Data

Configuration Package

Alternatively, a table is viewed by clicking “Export to Excel” within a Configuration Package:

bcentral.dev

The bcentral.dev domain name provides a comprehensive listing of tables and the fields within them:

https://bcentral.dev/nav/2018/w1/

Clicking the “Bank Account” folder on the left displays the following:

Now, clicking “Bank Account Ledger Entry” displays this information:

Page Inspection

You will see data by clicking “View table” within Page Inspection:

 

Specific Use Cases

Auditing

In the vendor card, click Business Central’s question mark icon in the upper right-hand corner.  Then, choose Help & Support:

Click the green “Inspect pages and data” hyperlink, appearing within the “Troubleshooting” section.

Scroll down to the bottom of Page Inspection, and you will see “SystemCreatedBy” and “SystemModifiedBy” on the right:

Reporting

Reviewing Page Inspection to find a table’s name and number can be used in such reporting tools as Excel and Power BI.

Above, Page Inspection shows that open purchase orders reside within the Purchase Header (38) table.

Armed with this information, you would type “web services” in the Tell Me button and create an OData link so that you can pull open purchase orders into Power Bi.  (You may need multiple tables such as a header and detail.)

For any questions you may have about Business Central, please reach out to us at Support@BondConsultingServices.com or click here to schedule a free consultation with one of our experts.