Business Central 2024 wave 2 (BC25): Support for SaveAsJson on the Query object

Hi, Readers.
The public preview for Dynamics 365 Business Central 2024 release wave 2 (BC25) is available. Learn more: Link.

I will continue to test and share some new features that I hope will be helpful.

In this post, I would like to talk about Support for SaveAsJson on the Query object in Business Central 2024 release wave 2 (BC25). This is not yet documented in the Business Central 2024 release wave 2 (BC25) release plan. But it is mentioned in AL Language extension changelog Version 14.0

We have added support for SaveAsJson on the Query object. This functionality is now available alongside the existing SaveAsXml and SaveAsCsv methods.

procedure Test();
var
    result : Boolean;
    os: OutStream;
begin
    result := Query.SaveAsJson(10, os);
end;

https://marketplace.visualstudio.com/items/ms-dynamics-smb.al/changelog

procedure SaveAsJson(Number: Integer, OutStream: OutStream): Boolean
Saves the resulting data set of a query as an .json file.

PS: In previous versions

Let’s look at two simple examples. This time I used the Query I tested before, but the standard one will also work.

Source code:

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

    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(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)
                    {
                    }
                }
            }
        }
    }
}

1. Save the query as a Json file and download it.

It is just the resulting data set of a query and does not contain multiple layers.

Source code:

pageextension 50100 ItemListExt extends "Item List"
{
    actions
    {
        addafter(AdjustInventory)
        {
            action(SaveQueryAsJson)
            {
                ApplicationArea = All;
                Caption = 'Save Query As Json';
                Image = Download;
                Promoted = true;
                PromotedCategory = Process;
                trigger OnAction()
                begin
                    SaveQueryAsJson();
                end;
            }
        }
    }

    procedure SaveQueryAsJson();
    var
        OutS: OutStream;
        InS: InStream;
        TempBlob: Codeunit "Temp Blob";
        FileName: Text[250];
    begin
        TempBlob.CreateOutStream(OutS);
        if Query.SaveAsJson(Query::"ZY Purchase Order Query", OutS) then begin
            FileName := 'PurchaseOrderQuery.json';
            TempBlob.CreateInStream(InS);
            DownloadFromStream(InS, '', '', '', FileName);
        end;
    end;
}

2. Read the Query as Text.

Source code:

pageextension 50100 ItemListExt extends "Item List"
{
    actions
    {
        addafter(AdjustInventory)
        {
            action(SaveQueryAsJson)
            {
                ApplicationArea = All;
                Caption = 'Read Query As Json';
                Image = ShowChart;
                Promoted = true;
                PromotedCategory = Process;
                trigger OnAction()
                begin
                    ReadQueryAsJson();
                end;
            }
        }
    }

    procedure ReadQueryAsJson();
    var
        OutS: OutStream;
        InS: InStream;
        TempBlob: Codeunit "Temp Blob";
        TypeHelper: Codeunit "Type Helper";
        JsonString: Text;
    begin
        TempBlob.CreateInStream(InS);
        TempBlob.CreateOutStream(OutS);
        if Query.SaveAsJson(Query::"ZY Purchase Order Query", OutS) then begin
            JsonString := TypeHelper.TryReadAsTextWithSepAndFieldErrMsg(InS, TypeHelper.LFSeparator(), 'ZY Purchase Order Query');
            Message(JsonString);
        end;
    end;
}

PS: Refer to the standard processing in table 36 “Sales Header” -> GetWorkDescription()

Great, give it a try!!!😁

END

Hope this will help.

Thanks for reading.

ZHU