Dynamics 365 Business Central: How to export all TableRelation information (For example, find all tables and fields related to Customer Table)

Hi, Readers.
Last week I was asked an interesting question, is there a way to find all tables and fields related to Customer (18) Table? Yes, there is indeed an easy way. So in this post, I will briefly discuss how to export all TableRelation information.

In Business Central, the TableRelation property lets you establish lookups into other tables. For example, on the Item Card you can select a vendor from who you usually purchase an item.

This is done through a table relationship.

The same is true for other fields such as Country/Region Code on the Customer Card and No. on the Sales Order Subform.

On the page (UI), there is a downward arrow on the far right. Clicking it will open a Drop-Down List (Include header). The fields are set in the fieldgroup -> DropDown. If not set, the primary key(s) will be displayed.

If we want to export this information, we can use system table 2000000141 “Table Relations Metadata”.

But this system table cannot be opened directly from the client (URL).

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

So we need to create a simple list page, for example,

In BC:

Regarding the question asked, how to find all tables and fields related to Customer Table. We only need to filter the Related Table ID to get all the details.

Very simple. Let’s look at another complex example.
“No.” field in table 37 “Sales Line”:

In table 2000000141 “Table Relations Metadata”: This requires a simple conversion

In addition the values ​​of TestTableRelation Property and ValidateTableRelation Property can also be found here.

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

page 50103 TableRelationsList
{
    ApplicationArea = All;
    Caption = 'Table Relations List';
    PageType = List;
    SourceTable = "Table Relations Metadata";
    UsageCategory = Lists;
    InsertAllowed = false;
    DeleteAllowed = false;
    ModifyAllowed = false;

    layout
    {
        area(Content)
        {
            repeater(General)
            {
                field("Table ID"; Rec."Table ID")
                {
                    ToolTip = 'Specifies the value of the Table ID field.';
                }
                field("Table Name"; Rec."Table Name")
                {
                    ToolTip = 'Specifies the value of the Table Name field.';
                }
                field("Field No."; Rec."Field No.")
                {
                    ToolTip = 'Specifies the value of the Field No. field.';
                }
                field("Field Name"; Rec."Field Name")
                {
                    ToolTip = 'Specifies the value of the Field Name field.';
                }
                field("Relation No."; Rec."Relation No.")
                {
                    ToolTip = 'Specifies the value of the Relation No. field.';
                }
                field("Related Table ID"; Rec."Related Table ID")
                {
                    ToolTip = 'Specifies the value of the Related Table ID field.';
                }
                field("Related Table Name"; Rec."Related Table Name")
                {
                    ToolTip = 'Specifies the value of the Related Table Name field.';
                }
                field("Related Field No."; Rec."Related Field No.")
                {
                    ToolTip = 'Specifies the value of the Related Field No. field.';
                }
                field("Related Field Name"; Rec."Related Field Name")
                {
                    ToolTip = 'Specifies the value of the Related Field Name field.';
                }
                field("Condition No."; Rec."Condition No.")
                {
                    ToolTip = 'Specifies the value of the Condition No. field.';
                }
                field("Condition Type"; Rec."Condition Type")
                {
                    ToolTip = 'Specifies the value of the Condition Type field.';
                }
                field("Condition Field No."; Rec."Condition Field No.")
                {
                    ToolTip = 'Specifies the value of the Condition Field No. field.';
                }
                field("Condition Value"; Rec."Condition Value")
                {
                    ToolTip = 'Specifies the value of the Condition Value field.';
                }
                field("Test Table Relation"; Rec."Test Table Relation")
                {
                    ToolTip = 'Specifies the value of the Test Table Relation field.';
                }
                field("Validate Table Relation"; Rec."Validate Table Relation")
                {
                    ToolTip = 'Specifies the value of the Validate Table Relation field.';
                }
            }
        }
    }
}

In fact, similar information can be found in table 2000000041 “Field”, more details: How to export all fields in all tables in Dynamics 365 Business Central

But it will not be displayed if multiple IF(<Conditions> are set. For example, the example above, “No.” field in table 37 “Sales Line”:

Give it a try!!!😁

PS:
1. Dynamics 365 Business Central: Can we extend TableRelation Property through a table extension??? (Yes, But…)

2. Dynamics 365 Business Central: How to Customize Drop-Down List (Field Groups)

3. Business Central 2023 wave 2 (BC23): Add a new FieldGroup (Brick & DropDown) to an existing table

4. Dynamics 365 Business Central: TableRelation, Enum/Option, Lookup and AssistEdit – What’s the difference on the page (UI)

END

Hope this will help.

Thanks for reading.

ZHU