Dynamics 365 Business Central: Collect data from different companies and Drilldown into the data of another company

Hi, Readers.
Last week I saw an interesting question in the forum, the client want to show sales data of all companies in the same Tenant on one page and open that company’s data after clicking on the amount. Like below. How to do it? And is this possible?

Yes, it’s actually not that complicated. This time we can use Record.ChangeCompany([String]) Method. We briefly discussed this method last year, more details: https://yzhums.com/6362/

Let’s make a small update.

1. Create a new temporary table to store company sales data.

2. Create a new page to display the sales data.

When opening the page, insert the current sales data.

3. Drilldown into the sales data of selected company.

Test Video:

Sample code:

table 50100 ZYCompanySales
{
    DataClassification = CustomerContent;
    TableType = Temporary;

    fields
    {
        field(1; CompanyName; Text[30])
        {
            Caption = 'Company Name';
            DataClassification = CustomerContent;
        }
        field(2; TotalSales; Decimal)
        {
            Caption = 'Total Sales';
            DataClassification = CustomerContent;
        }
    }

    keys
    {
        key(Key1; CompanyName)
        {
            Clustered = true;
        }
    }
}

page 50100 CompanySales
{
    Caption = 'Company Sales';
    PageType = List;
    UsageCategory = Lists;
    ApplicationArea = All;
    SourceTable = ZYCompanySales;

    layout
    {
        area(Content)
        {
            repeater(Group)
            {
                field(CompanyName; Rec.CompanyName)
                {
                    ApplicationArea = All;
                }
                field(TotalSales; Rec.TotalSales)
                {
                    ApplicationArea = All;

                    trigger OnDrillDown()
                    var
                        Cust: Record Customer;
                    begin
                        Cust.ChangeCompany(Rec.CompanyName);
                        Page.Run(Page::"Customer List", Cust);
                    end;
                }
            }
        }
    }

    trigger OnOpenPage()
    var
        Companylist: Record Company;
        Cust: Record Customer;
        TotalSales: Decimal;
    begin
        Rec.Reset();
        if not Rec.IsEmpty then
            Rec.DeleteAll();
        Companylist.Reset();
        if Companylist.FindSet() then
            repeat
                TotalSales := 0;
                Cust.ChangeCompany(Companylist.Name);
                Cust.Reset();
                Cust.SetAutoCalcFields("Sales (LCY)");
                if Cust.FindSet() then
                    repeat
                        TotalSales += Cust."Sales (LCY)";
                    until Cust.Next() = 0;
                Rec.Init();
                Rec.CompanyName := Companylist.Name;
                Rec.TotalSales := TotalSales;
                Rec.Insert();
            until Companylist.Next() = 0;
    end;
}

END

Hope this will help.

Thanks for reading.

ZHU