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

Dynamics 365 Business Central

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?

Company Name Total Sales
Company A1000
Company B2000
Company C4000

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

コメント

タイトルとURLをコピーしました