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.
![](https://yzhums.com/wp-content/uploads/2022/07/image-250-1024x503.png)
There is no limitation on number of characters and it gets copied over to Posted Sales Invoice.
![](https://yzhums.com/wp-content/uploads/2022/07/image-260-1024x581.png)
Okay, let’s see what he tried.
First, he tried using the Configuration Package.
![](https://yzhums.com/wp-content/uploads/2022/07/image-253-1024x503.png)
Choose Export to Excel.
![](https://yzhums.com/wp-content/uploads/2022/07/image-254-1024x503.png)
The value of Work Description is displayed with only an asterisk.
![](https://yzhums.com/wp-content/uploads/2022/07/image-255.png)
Then he tried adding the Work Description field to the Sales Order List page and using the Open in Excel feature.
![](https://yzhums.com/wp-content/uploads/2022/07/image-256-1024x503.png)
![](https://yzhums.com/wp-content/uploads/2022/07/image-257-1024x503.png)
Same as Configuration Pakcage, just an asterisk.
![](https://yzhums.com/wp-content/uploads/2022/07/image-258-1024x389.png)
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.
![](https://yzhums.com/wp-content/uploads/2022/07/image-252-1024x605.png)
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.
![](https://yzhums.com/wp-content/uploads/2022/07/image-251-1024x503.png)
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.”
![](https://yzhums.com/wp-content/uploads/2022/08/image-22-1024x582.png)
The work description is printed on this report.
![](https://yzhums.com/wp-content/uploads/2022/08/image-21-1024x582.png)
The key points:
![](https://yzhums.com/wp-content/uploads/2022/08/image-26-1024x727.png)
![](https://yzhums.com/wp-content/uploads/2022/08/image-23.png)
![](https://yzhums.com/wp-content/uploads/2022/08/image-27.png)
![](https://yzhums.com/wp-content/uploads/2022/08/image-25-1024x620.png)
Let’s apply this to our own export code.
![](https://yzhums.com/wp-content/uploads/2022/08/image-28-1024x721.png)
![](https://yzhums.com/wp-content/uploads/2022/08/image-29-1024x318.png)
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.
![](https://yzhums.com/wp-content/uploads/2022/08/image-33-1024x582.png)
The key points:
trigger OnAfterGetRecord() on the page 42 “Sales Order”
![](https://yzhums.com/wp-content/uploads/2022/08/image-30.png)
procedure GetWorkDescription() WorkDescription: Text
![](https://yzhums.com/wp-content/uploads/2022/08/image-31.png)
So we can just use this method.
![](https://yzhums.com/wp-content/uploads/2022/08/image-32-1024x690.png)
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”
![](https://yzhums.com/wp-content/uploads/2022/08/image-34.png)
2. The Work Description feature is not available in Purchase Documents.
![](https://yzhums.com/wp-content/uploads/2022/08/image-35-1024x582.png)
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). |
END
Hope this will help.
Thanks for reading.
ZHU
コメント