Dynamics 365 Business Central: Using the query as a data source for a page (Query.Open Method)

Hi, Readers.
In my previous blog, we have discussed about QueryCategory property, a new feature of Business Central 2020 Release Wave 2.
With this property, you can open Queries from the pages. Similar to Smart List.
For more details.
Dynamics 365 Business Central: QueryCategory property (Execute Queries from Pages)

This time I want to share another way to open query on the page. Using the query as a data source for a page. To do this, you have to copy the query resulting dataset into a temporary table and set it as the source table for the page. So Let’s try it.

1. First, create a new Query.

Source Code:

query 50100 "ZY Purchase Order Query"
{
    Caption = 'ZY Purchase Order Query';
    OrderBy = Descending(Buy_from_Vendor_No_);

    elements
    {
        dataitem(Purchase_Header; "Purchase Header")
        {
            column(Buy_from_Vendor_No_; "Buy-from Vendor No.")
            {
            }
            column(Buy_from_Vendor_Name; "Buy-from Vendor Name")
            {
            }
            column(Order_Date; "Order Date")
            {
            }
            column(Currency_Code; "Currency Code")
            {
            }
            column(Amount_Including_VAT; "Amount Including VAT")
            {
            }
            dataitem(Purchase_Line; "Purchase Line")
            {
                DataItemLink = "Document Type" = Purchase_Header."Document Type",
                "Document No." = Purchase_Header."No.";
                column(No_; "No.")
                {
                }
                column(Description; Description)
                {
                }
                column(Quantity; Quantity)
                {
                }
                column(Amount; Amount)
                {
                }
                dataitem(Item; Item)
                {
                    DataItemLink = "No." = Purchase_Line."No.";
                    column(Inventory; Inventory)
                    {
                    }
                }
            }
        }
    }
}

Publish it and try to open Query directly with URL.

Ok, Data can be displayed.

2. Next, create a table that contains the fields you want to shown in page and set the TableType property to Temporary.

Source Code:

table 50106 ZYPurchaseOrderQueryTable
{
    DataClassification = CustomerContent;
    TableType = Temporary;

    fields
    {
        field(1; RowNo; Integer)
        {
            Caption = 'Row No.';
            DataClassification = CustomerContent;
        }
        field(10; "Buy-from Vendor No."; Code[20])
        {
            Caption = 'Buy-from Vendor No.';
            DataClassification = CustomerContent;
        }
        field(20; "Buy-from Vendor Name"; Text[100])
        {
            Caption = 'Buy-from Vendor Name';
            DataClassification = CustomerContent;
        }
        field(30; "Order Date"; Date)
        {
            Caption = 'Order Date';
            DataClassification = CustomerContent;
        }
        field(35; "Currency Code"; Code[10])
        {
            Caption = 'Currency Code';
            TableRelation = Currency;
            DataClassification = CustomerContent;
        }
        field(40; "Amount Including VAT"; Decimal)
        {
            AutoFormatExpression = "Currency Code";
            AutoFormatType = 1;
            Caption = 'Amount Including VAT';
            DataClassification = CustomerContent;
        }
        field(50; "No."; Code[20])
        {
            Caption = 'No.';
            DataClassification = CustomerContent;
        }
        field(60; Description; Text[100])
        {
            Caption = 'Description';
            DataClassification = CustomerContent;
        }
        field(70; Quantity; Decimal)
        {
            Caption = 'Quantity';
            DecimalPlaces = 0 : 5;
            DataClassification = CustomerContent;
        }
        field(80; Amount; Decimal)
        {
            Caption = 'Amount';
            AutoFormatExpression = "Currency Code";
            AutoFormatType = 1;
            DataClassification = CustomerContent;
        }
        field(90; Inventory; Decimal)
        {
            Caption = 'Inventory';
            DecimalPlaces = 0 : 5;
            DataClassification = CustomerContent;
        }

    }

    keys
    {
        key(PK; RowNo)
        {
            Clustered = true;
        }
    }
}

3. Create a new page to show the fields.

4. Transfer the data in Query to the table.
I executed it when page is opened. You can also create an action to execute it, or use report to add some filters before data transfer. “Query.SETFILTER(Column1, String)”;

Source Code:

page 50106 ZYPurchaseOrderQueryPage
{
    Caption = 'ZY Purchase Order Query';
    PageType = List;
    UsageCategory = Lists;
    ApplicationArea = All;
    SourceTable = ZYPurchaseOrderQueryTable;

    layout
    {
        area(Content)
        {
            repeater(Group)
            {

                field(RowNo; Rec.RowNo)
                {
                    ApplicationArea = All;
                }
                field("Buy-from Vendor No."; Rec."Buy-from Vendor No.")
                {
                    ApplicationArea = All;
                }
                field("Buy-from Vendor Name"; Rec."Buy-from Vendor Name")
                {
                    ApplicationArea = All;
                }
                field("Currency Code"; Rec."Currency Code")
                {
                    ApplicationArea = All;
                }
                field("Amount Including VAT"; Rec."Amount Including VAT")
                {
                    ApplicationArea = All;
                }
                field("Order Date"; Rec."Order Date")
                {
                    ApplicationArea = All;
                }
                field("No."; Rec."No.")
                {
                    ApplicationArea = All;
                }
                field(Description; Rec.Description)
                {
                    ApplicationArea = All;
                }
                field(Quantity; Rec.Quantity)
                {
                    ApplicationArea = All;
                }
                field(Amount; Rec.Amount)
                {
                    ApplicationArea = All;
                }
                field(Inventory; Rec.Inventory)
                {
                    ApplicationArea = All;
                }
            }
        }
    }

    trigger OnOpenPage()
    var
        ZYPurchaseOrderQuery: Query "ZY Purchase Order Query";
    begin
        if ZYPurchaseOrderQuery.Open() then begin
            while ZYPurchaseOrderQuery.Read() do begin
                Rec.Init();
                Rec.RowNo := Rec.RowNo + 1;
                Rec."Buy-from Vendor No." := ZYPurchaseOrderQuery.Buy_from_Vendor_No_;
                Rec."Buy-from Vendor Name" := ZYPurchaseOrderQuery.Buy_from_Vendor_Name;
                Rec."Currency Code" := ZYPurchaseOrderQuery.Currency_Code;
                Rec."Amount Including VAT" := ZYPurchaseOrderQuery.Amount_Including_VAT;
                Rec."Order Date" := ZYPurchaseOrderQuery.Order_Date;
                Rec."No." := ZYPurchaseOrderQuery.No_;
                Rec.Description := ZYPurchaseOrderQuery.Description;
                Rec.Quantity := ZYPurchaseOrderQuery.Quantity;
                Rec.Amount := ZYPurchaseOrderQuery.Amount;
                Rec.Inventory := ZYPurchaseOrderQuery.Inventory;
                Rec.Insert();
            end;
            ZYPurchaseOrderQuery.Close();
        end;
    end;
}

5. Test.

This is a very simple sample, hope this will help.

Find out more about Query.Open Method from Microsoft Docs.

Thanks for your reading.

ZHU