Bond Consulting Services

How to Examine Microsoft Business Central Tables (Deep Dive)

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.

---

by Bond Consulting Services

Auditing Security and End User Updates

Field Level Security

Microsoft Dynamics 365 Business Central lets you monitor important fields and be notified by email when someone updates a field. This provides, in essence, “field level security” for Business Central. To specify the fields that you want to monitor, click the “Tell Me” button, type “assisted setup”, and click the hyperlink for “Assisted Setup”.

Next, click “Set up field monitoring”.

This will launch the wizard for the Field Monitoring Assisted Setup Guide.

Clicking “Next” on the first page of the wizard takes you to “Let’s Get Started”:

The above window means that you may choose to monitor fields based on their Data Classifications, as follows:

• Sensitive – private data (i.e., political, or religious beliefs)
• Personal – any data that can be used to identify an end user
• Company Confidential – business data that you want kept as a secret (i.e., ledger entries)

If you do not want to make any selections at this time, simply click “Next”.

The final section of this article discusses these Data Classification settings, in more detail.

In addition to choosing the Business Central user who will be notified when someone changes the value in a field, choose the “Notification Email Account” containing that user’s e-mail address.

Clicking “Finish” not only opens the Monitored Fields Worksheet window but, also, generates the following e-mail:

Once the Monitored Fields Worksheet opens, choose the tables and fields to monitor.

Choosing Table No. 79 and Field No. 22 and clicking “Notify”, as shown above, means that any changes to the “Ship-To Name” field in the “Company Information” window will generate an e-mail to the “ADMIN” user in Business Central.

Important Note: You must sign out of Business Central and sign in, again, for it to take effect.

In this example, we’ll be changing the Ship-to Name of 3Q LLC.

If an end user accessed the Company Information window of the 3Q LLC company and removed “LLC” from this field, the following e-mail would be sent to ADMIN:

Then, if you return to the Monitored Fields Worksheet > Select “Field Change Entries” at the top of the worksheet to open the “Monitored Field Log Entries” window:

This log shows that ADMIN changed the value of Ship-To Name from “3Q LLC” to “3Q”. Although ADMIN changed the Ship-To Name in this example, a different user would typically have made this change.

Change Log

Business Central’s Change Log, also, provides for field monitoring “light”.  The Change Log, however, doesn’t transmit e-mails. To open the “Change Log Setup” window, click the “Tell Me” Button, type “change log setup”, and click the “Change Log Setup” hyperlink:

Before selecting Change Log Activated, choose the table and field that you want to track in the log.  This is done by navigating, within the “Change Log Setup” window, to Setup (Table) List:

In the “Search” button in the upper left, type “company information”:

Select “Some fields” in the “Log Modification” drop-down list, and click its ellipsis button:

Once Ship-to Name is found as shown above, check the “Log Modification” box.

Return to Change Log Setup, to enable the Change Log:

After choosing to close the window, click “Yes” to the following message:

Just as Field Monitoring does, Change Log Entries shows that ADMIN removed “LLC” from Ship-to Name in Company Information:

Note: Using the Change Log can hinder performance and increase the size of the database.  Users lose time, and time is money.  To save money, then, take the following steps:

• Do not add ledger entries and posted documents but, instead, prioritize system fields such as Created By and Created Date;
• Use the “Some Fields” Tracking Type and not “All Fields”; and
• Track only the most important fields.

Troubleshooting Security

If you’re having issues in investigating and eliminating problems with user security, review Effective Permissions. Open the “Users” window using the “Tell Me” button, type “users”, and click the “Users” hyperlink:

We’ll click on a User Name, such as “ACCOUNTING”:

Next, click the “Effective Permissions” button in the User Card:

The “Permissions” part lists all of the database objects that the user has access to.  This section cannot be edited.

The “By Permission Set” part shows the assigned Permission Sets through which the Permissions are granted, the source and type of the Permission Set, and to which extent the different Access Types are permitted.

The five Access Types are “Read Permission”, “Insert Permission”, “Modify Permission”, “Delete Permission”, and “Execute Permission”.

For each row that you select in the “Permissions” part, the “By Permission Set” part shows which Permission Set or Sets that the Permission has been granted.

To edit a Permission Set, select a “By Permission Set” part.

Next, click the “Permissions Sets” button to display the “Permissions Sets” window:

Here, we’ll select the “BANKING AND FINANCE” User-Defined Permission Set and click the “Permissions” button.  The “Permission Set” window will appear:

Here, you can edit the value in each of the five access type columns: Read Permission, Insert Permission, Modify Permission, Delete Permission, and Execute Permission.

To edit the Permission Set, click on one of the drop-down lists and select a different value:

Important Note: When you edit a Permission Set, the changes will also apply to other users that have the permission set assigned.

If you don’t want to make changes to the Permission Set but want to simply view the tables that the Set has access to, simply click the “View all permissions” button.

 

Data Classification Worksheet

In the first section of this blog article, we discussed monitoring fields.  There, we discussed the Data Classification Worksheet.

As shown at the top of the worksheet, Microsoft issues the following disclaimer:

“Microsoft is providing this Data Classification feature as a matter of convenience only. It's your responsibility to classify the data appropriately and comply with any laws and regulations that are applicable to you.”

At the top of this window, you can access the “Process” and “View” menu selections that perform the following respective tasks:

Process:

Set Up Data Classifications – classify fields based on sensitivity

Find New Fields – search for new fields and add them to the worksheet

Set as Sensitive – set fields to “Sensitive”

Set as Personal – set fields to “Personal”

Set as Normal – set fields to “Normal”

Set as Company Confidential – set fields to “Company Confidential”

Set as Unclassified – set fields to “Unclassified”

Show Field Content – shows the values contained within the field

 

View:

View Similar Fields – view fields with similar names

View Unclassified – view only unclassified fields

View Sensitive – view only sensitive fields

View Personal – view only personal fields

View Normal – view only normal fields

View Company Confidential – view only confidential fields

View All – view all fields

 

For questions about this process or how to streamline your Business Central environment please reach out to us at Support@BondConsultingServices.com or click here to schedule a free consultation with one of our experts.

---

by Bond Consulting Services