Dynamics 365 Business Central: Viewing table data (Six ways)

Hi, Readers.
Today I would like to talk about how to view table data in Business Central.
For developers, administrators, and support personnel, it can be useful to inspect table data in the tenant database, particularly when debugging or troubleshooting.To support this need, you can view table objects in the Web client. In this post, I will share six ways, hoping to give you some hints.

View a table object directly from the client (URL)

To view a table, you add the table=<TableID> parameter to the client’s address (URL), replacing <TableID> with the ID of the table that you want to view.

For example, if your URL starts with https://businesscentral.dynamics.com/7e47da45-7f7d-448a-bd3d-1f4aa2ec8f62/Sandbox254, then to view table 18 Customer in your current company, you could use the following URL:

https://businesscentral.dynamics.com/7e47da45-7f7d-448a-bd3d-1f4aa2ec8f62/Sandbox254/?table=18

Or, for a specific company, such as “Demo”:

https://businesscentral.dynamics.com/7e47da45-7f7d-448a-bd3d-1f4aa2ec8f62/Sandbox254/?company=Demo&table=18

Note the use of & when table=<TableID> isn’t located directly after the domain name.

PS:
1. Tables with the Access property set to Internal cannot be opened directly.

Sorry, the current permissions prevented the action. (TableData 7775 Copilot Settings Read: )

2. Some system tables cannot be opened directly, and you need to create a page to see the records. For example,

The table Table Relations Metadata cannot be read because it contains system data.

Tables with the Scope property set to OnPrem cannot be opened from the cloud

View a table from Page Inspection

The page inspection feature in Business Central enables you to get details about a page, providing insight into the page design, the different elements that comprise the page, and the source behind the data it displays.

You can start page inspection from the Help & Support page. Choose the question mark in the top right corner, choose Help & Support, and then choose Inspect pages and data.

Or, you can just use the keyboard shortcut Ctrl+Alt+F1.

You can find the View table action in Page Inspection.

View table: The URL to view all records and fields of the page’s source table in a separate browser window.

The result is the same as opening it directly from the client (URL)

PS:
1. Dynamics 365 Business Central: How to hide page inspection details (Table Fields, Extensions, Page Filters and View table action)

2. If the table of the page is set as a temporary table, it cannot be opened by this method.

The source table of this page is temporary.

3. When using this method, you must have a page to open it. If there is no page, Page Inspection cannot be opened.

4. Dynamics 365 Business Central: How to export all pages with SourceTable number (list of pages with corresponding tables)

View a table from 8700 Table Information page

The 8700 Table Information page provides information about the number of records in all system and business tables in Business Central, and how much data each table contains.

This information is useful for troubleshooting performance problems because it shows the distribution of data sizes across tables. To open this page, select the Tell Me icon, enter Table Information, and then choose the related link.

PS: From the Business Central administration center, you can launch a list of all tables

You can find the table you need to open by Company Name, Table No., and then click the number in No. of Records to open the table in a new tab.

PS:
1. Dynamics 365 Business Central: How to check tenant size, environment size, company size and table size (Database storage)

2. If the DataPerCompany Property is set to false, the Company Name will be empty.

Export table data from Configuration Package

When you onboard a prospect, you can use configuration packages to set up Business Central according to your best practices and their requirements. Apply the relevant configuration packages to an empty company in the customer’s tenant. And we can export table data in the Configuration Package. Let’s look at a simple example.

Choose the New action on the Configuration Packages page.

On the General FastTab, fill in the fields as appropriate. Hover over a field to read a short description.

Enter or select the table number of customer, 18.

Choose Export to Excel on the Header (Export all the tables on the lines) or Lines (Export the tables only selected).

Please note that the table only exports filtered data and the fields included.

PS:
1. You can find the No. of Database Records field.

No. of Database Records
Specifies how many database records have been created in connection with the migration.

But unlike the 8700 Table Information page, clicking it will only open the page set in the Page ID, and will not directly open the table.

For tables that do not have a set Page ID, an error message will be displayed after clicking.

Define the drill-down page in the Page ID field.

2. You cannot add virtual tables and system tables

3. Dynamics 365 Business Central: How to Export and Import Data (Using Configuration Packages)

4. Dynamics 365 Business Central: How to use Configuration Package to post documents (Sales Order, Purchase Order, Transfer Order, General Journal, etc.)

5. Dynamics 365 Business Central: How to bulk delete data on a list page that includes a Card page (For example, bulk delete items on the Item List page)

View a table object from an AL project in Visual Studio Code

In a development environment, in addition to viewing a table directly from the Web client, developers can view a table automatically when they publish/debug an AL project from Visual Studio Code.

In the launch.json file for the project, set the "startupObjectType" parameter to "table" and the "startupObjectId" parameter to the ID of the table. You can also set the the "startupCompany" parameter to the name of the company. More details: Launch JSON file
For example:

{
    "version": "0.2.0",
    "configurations": [
        {
            "name": "Microsoft cloud sandbox",
            "request": "launch",
            "type": "al",
            "environmentType": "Sandbox",
            "environmentName": "Sandbox253",
            "startupObjectId": 18,
            "startupObjectType": "Table",
            "startupCompany": "Demo 2",
            "tenant": "7e47da45-7f7d-448a-bd3d-1f4aa2ec8f62",
            "schemaUpdateMode": "ForceSync",
            "breakOnError": "All",
            "launchBrowser": true,
            "enableLongRunningSqlStatements": true,
            "enableSqlInformationDebugger": true
        }
    ]
}

Publish or debug the project (selecting F5 or Ctrl+F5 )

Then the table was opened directly.

PS: You can also select the table you want to open in AL Explorer and click Run.
More details: Business Central 2023 wave 1 (BC22): AL Explorer and AL Home in Visual Studio Code AL extension

Customization

We can also made an extension to do this. For the SourceTable, we are going to use Table 2000000058 AllObjWithCaption.

Next, because Table Object does not have Run functions like Page Object, Report Object, or Xmlport Object. So we have to use System.Hyperlink Method.
System.Hyperlink Method: Passes a URL as an argument to an Internet browser, such as Windows Internet Explorer.

A Simple Example:

Source code: GitHub (Please note that the source code is for reference only, you can improve it according to your own needs)

page 50100 "ZY Table List"
{
    Caption = 'Table List';
    Editable = false;
    PageType = List;
    ApplicationArea = All;
    UsageCategory = Administration;
    SourceTable = AllObjWithCaption;
    SourceTableView = where("Object Type" = filter(= Table));

    layout
    {
        area(Content)
        {
            repeater(TableList)
            {
                field("Object Type"; Rec."Object Type")
                {
                    ApplicationArea = All;
                }
                field("Object ID"; Rec."Object ID")
                {
                    ApplicationArea = All;
                }
                field("Object Name"; Rec."Object Name")
                {
                    ApplicationArea = All;
                }
                field("Object Caption"; Rec."Object Caption")
                {
                    ApplicationArea = All;
                }
                field("Object Subtype"; Rec."Object Subtype")
                {
                    ApplicationArea = All;
                }
                field("App Name"; GetAppName(Rec."App Package ID"))
                {
                    ApplicationArea = All;
                }
            }
        }
    }
    actions
    {
        area(Processing)
        {
            action("Run Selected Table")
            {
                Caption = 'Run Selected Table';
                ApplicationArea = All;
                Image = ExecuteBatch;
                Promoted = true;
                PromotedOnly = true;
                PromotedCategory = Process;
                Scope = Repeater;
                trigger OnAction()
                begin
                    Hyperlink(GetUrl(ClientType::Current, CompanyName, ObjectType::Table, Rec."Object ID"));
                end;
            }
        }
    }
    local procedure GetAppName(var AppPackageID: Guid): Text[250]
    var
        NavInstalledApp: Record "NAV App Installed App";
    begin
        NavInstalledApp.Reset();
        NavInstalledApp.SetRange("Package ID", AppPackageID);
        if NavInstalledApp.FindFirst() then
            exit(NavInstalledApp.Name);
    end;
}

Give it a try!!!😁

PS:
1. Whether viewing the table directly from the client or from Visual Studio Code, your Dynamics 365 user account must have the following permissions:

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

2. You cannot view virtual tables or the following system tables: Constraints

END

Hope this will help.

Thanks for reading.

ZHU