Dynamics 365 Business Central: How to export Work Description (Blob) in Sales Documents to excel

Hi, Readers.
Last week I saw a question on the Business Central forum, how to export the Work Description in Sales Documents to excel?

As far as I know, Microsoft added this new field in Sales Documents since NAV 2017. It help users to add more details about Sales Quote or Sales Invoice.

There is no limitation on number of characters and it gets copied over to Posted Sales Invoice.

Okay, let’s see what he tried.
First, he tried using the Configuration Package.

Choose Export to Excel.

The value of Work Description is displayed with only an asterisk.

Then he tried adding the Work Description field to the Sales Order List page and using the Open in Excel feature.

Same as Configuration Pakcage, just an asterisk.

So it is not possible to export the value of Work Description field using the standard features. What about customization?
Let’s look at the base application, the Work Description field is a Blob data type.

Blob Data Type: Is a complex data type. Variables of this data type differ from normal numeric and string variables in that BLOBs have a variable length. The maximum size of a BLOB(binary large object) is 2 GB.

We have discussed How to use Excel Buffer to Export data before, and actually we have two standard functions to refer to this time.

1. report 1305 “Standard Sales – Order Conf.”

The work description is printed on this report.

The key points:

Let’s apply this to our own export code.

Test video:

Source Code:

pageextension 50101 SalesOrderListExt extends "Sales Order List"
{
    actions
    {
        addfirst(processing)
        {
            action(ExportToExcel)
            {
                Caption = 'Export to Excel';
                ApplicationArea = All;
                Promoted = true;
                PromotedCategory = Process;
                PromotedIsBig = true;
                Image = Export;
                trigger OnAction()
                var
                begin
                    ExportSalesOrders(Rec);
                end;
            }
        }
    }
    local procedure ExportSalesOrders(var SalesHeader: Record "Sales Header")
    var
        TempExcelBuffer: Record "Excel Buffer" temporary;
        SalesOrdersLbl: Label 'Sales Orders';
        ExcelFileName: Label 'Sales Orders_%1_%2';
        WorkDescriptionInstream: InStream;
        WorkDescriptionLine: Text;
    begin
        TempExcelBuffer.Reset();
        TempExcelBuffer.DeleteAll();
        TempExcelBuffer.NewRow();
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption("No."), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption("Sell-to Customer No."), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption("Sell-to Customer Name"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption("Document Type"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption("Document Date"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption(Status), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption(Amount), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption("Amount Including VAT"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption("Work Description"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        SalesHeader.SetAutoCalcFields("Work Description");
        if SalesHeader.FindSet() then
            repeat
                WorkDescriptionLine := '';
                TempExcelBuffer.NewRow();
                TempExcelBuffer.AddColumn(SalesHeader."No.", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
                TempExcelBuffer.AddColumn(SalesHeader."Sell-to Customer No.", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
                TempExcelBuffer.AddColumn(SalesHeader."Sell-to Customer Name", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
                TempExcelBuffer.AddColumn(SalesHeader."Document Type", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
                TempExcelBuffer.AddColumn(SalesHeader."Document Date", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Date);
                TempExcelBuffer.AddColumn(SalesHeader.Status, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
                TempExcelBuffer.AddColumn(SalesHeader.Amount, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number);
                TempExcelBuffer.AddColumn(SalesHeader."Amount Including VAT", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number);
                SalesHeader."Work Description".CreateInStream(WorkDescriptionInstream, TEXTENCODING::UTF8);
                WorkDescriptionInstream.ReadText(WorkDescriptionLine);
                TempExcelBuffer.AddColumn(WorkDescriptionLine, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
            until SalesHeader.Next() = 0;
        TempExcelBuffer.CreateNewBook(SalesOrdersLbl);
        TempExcelBuffer.WriteSheet(SalesOrdersLbl, CompanyName, UserId);
        TempExcelBuffer.CloseBook();
        TempExcelBuffer.SetFriendlyFilename(StrSubstNo(ExcelFileName, CurrentDateTime, UserId));
        TempExcelBuffer.OpenExcel();
    end;
}

2. page 42 “Sales Order” and table 36 “Sales Header”

The value of Work Description is displayed on the page.

The key points:
trigger OnAfterGetRecord() on the page 42 “Sales Order”

procedure GetWorkDescription() WorkDescription: Text

So we can just use this method.

The result is the same as above.

Source Code:

pageextension 50101 SalesOrderListExt extends "Sales Order List"
{
    actions
    {
        addfirst(processing)
        {
            action(ExportToExcel)
            {
                Caption = 'Export to Excel';
                ApplicationArea = All;
                Promoted = true;
                PromotedCategory = Process;
                PromotedIsBig = true;
                Image = Export;
                trigger OnAction()
                var
                begin
                    ExportSalesOrders(Rec);
                end;
            }
        }
    }
    local procedure ExportSalesOrders(var SalesHeader: Record "Sales Header")
    var
        TempExcelBuffer: Record "Excel Buffer" temporary;
        SalesOrdersLbl: Label 'Sales Orders';
        ExcelFileName: Label 'Sales Orders_%1_%2';
        WorkDescriptionLine: Text;
    begin
        TempExcelBuffer.Reset();
        TempExcelBuffer.DeleteAll();
        TempExcelBuffer.NewRow();
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption("No."), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption("Sell-to Customer No."), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption("Sell-to Customer Name"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption("Document Type"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption("Document Date"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption(Status), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption(Amount), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption("Amount Including VAT"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(SalesHeader.FieldCaption("Work Description"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        SalesHeader.SetAutoCalcFields("Work Description");
        if SalesHeader.FindSet() then
            repeat
                WorkDescriptionLine := '';
                TempExcelBuffer.NewRow();
                TempExcelBuffer.AddColumn(SalesHeader."No.", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
                TempExcelBuffer.AddColumn(SalesHeader."Sell-to Customer No.", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
                TempExcelBuffer.AddColumn(SalesHeader."Sell-to Customer Name", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
                TempExcelBuffer.AddColumn(SalesHeader."Document Type", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
                TempExcelBuffer.AddColumn(SalesHeader."Document Date", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Date);
                TempExcelBuffer.AddColumn(SalesHeader.Status, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
                TempExcelBuffer.AddColumn(SalesHeader.Amount, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number);
                TempExcelBuffer.AddColumn(SalesHeader."Amount Including VAT", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number);
                WorkDescriptionLine := SalesHeader.GetWorkDescription();
                TempExcelBuffer.AddColumn(WorkDescriptionLine, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
            until SalesHeader.Next() = 0;
        TempExcelBuffer.CreateNewBook(SalesOrdersLbl);
        TempExcelBuffer.WriteSheet(SalesOrdersLbl, CompanyName, UserId);
        TempExcelBuffer.CloseBook();
        TempExcelBuffer.SetFriendlyFilename(StrSubstNo(ExcelFileName, CurrentDateTime, UserId));
        TempExcelBuffer.OpenExcel();
    end;
}

PS:
1. There is also a GetWorkDescription() method in table 112 “Sales Invoice Header”

2. The Work Description feature is not available in Purchase Documents.

3. The following methods are available on instances of the Blob data type.

Method name	Description
CreateInStream(InStream [, TextEncoding])	Creates an InStream object for a binary large object (BLOB). This enables you to read data from the BLOB.
CreateOutStream(OutStream [, TextEncoding])	Creates an OutStream object for a binary large object (BLOB). This enables you to write data to the BLOB.
Export(Text)	Exports a binary large object (BLOB) to a file.
HasValue()	Determines whether a binary large object (BLOB) has a value.
Import(Text)	Imports a binary large object (BLOB) from a file.
Length()	Returns the number of bytes in the binary large object (BLOB).

4. How to split a string into fixed length (Error: The length of the string is xxx, but it must be less than or equal to xxx characters)

END

Hope this will help.

Thanks for reading.

ZHU