Hi, Readers.
Today I would like to talk about an interesting topic, how to split a string into fixed length in Business Central.
In August, we discussed how to export Work Description (Blob) in Sales Documents to excel.
The Work Description field is a Blob data type. And there is no limitation on number of characters and it gets copied over to Posted Sales Invoice.
We canāt export this value with Configuration Package or Open in Excel features, so at that time I introduced Excel Buffer to export.
But last week I received an question that they ran into an error similar to the one below.
The length of the string is 937, but it must be less than or equal to 250 characters. Value: 01-1234567890ā¦ā¦
On the page:
Why? This is because when we export the value in Text data type to a cell in Excel, this length is limited to 250. We cannot modify the length of standard fields.
In table 370 āExcel Bufferā
As you might know, the maximum number of characters in a text or code field is 2048. So even if it is not a Blob field, you may encounter this problem.
More details: Specifications for Tables
How to deal with it? The easiest way to do is to export only the fields of length 250, and delete all the ones after that. But this should be difficult for customers to accept, so another way is to split.
Using the above (Export Sales Documents) as an example, we just need to split the value in the Blob field into multiple Text fields of length 250, and then export to the cell in Excel.
Let me update the 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;
WorkDescriptionLine2: Text;
i: Integer;
SplitTo: Integer;
DivValue: Integer;
ModValue: Integer;
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 := '';
WorkDescriptionLine2 := '';
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();
DivValue := StrLen(WorkDescriptionLine) div 250;
ModValue := StrLen(WorkDescriptionLine) mod 250;
if ModValue = 0 then
SplitTo := DivValue
else
SplitTo := DivValue + 1;
for i := 1 to SplitTo do begin
WorkDescriptionLine2 := CopyStr(WorkDescriptionLine, 1, 250);
if StrLen(WorkDescriptionLine) >= 251 then
WorkDescriptionLine := CopyStr(WorkDescriptionLine, 251, StrLen(WorkDescriptionLine));
TempExcelBuffer.AddColumn(WorkDescriptionLine2, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
end;
//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;
}Excel:
Test Video:
My solution is simpler, using the div and mod operator (Integer division and Modulus).
To make it easier to understand, Iāve made another simpler example.
Enter a text and the maximum characters, split the text based on the maximum characters:
Test Video:
Test Code: This is just one of the methods, if you have a better and more convenient method please share it with meš.
tableextension 50111 MyExtension extends Customer
{
fields
{
field(50100; MyText; Text[1024])
{
Caption = 'My Text';
DataClassification = CustomerContent;
}
field(50101; Maximumcharacters; Integer)
{
Caption = 'Maximum characters';
MinValue = 0;
DataClassification = CustomerContent;
}
}
}
pageextension 50111 MyExtension extends "Customer Card"
{
layout
{
addafter(Name)
{
field(Maximumcharacters; Rec.Maximumcharacters)
{
ApplicationArea = All;
trigger OnValidate()
begin
SplitStringIntoFixedLength();
end;
}
field(MyText; Rec.MyText)
{
ApplicationArea = All;
MultiLine = true;
trigger OnValidate()
begin
SplitStringIntoFixedLength();
end;
}
}
}
local procedure SplitStringIntoFixedLength()
var
Msg: Label 'The field length is %1, the maximum length is %2, and it is split into %3\\%4';
AllValue: Text[2048];
DivValue: Integer;
ModValue: Integer;
i: Integer;
SplitTo: Integer;
RemainValue: Text[1024];
SplitResult: Text[1024];
ErrorMsg: Label 'The Maximum character cannot be less than the original field length.';
begin
if (Rec.Maximumcharacters <> 0) and (Rec.MyText <> '') then begin
if Rec.Maximumcharacters > StrLen(Rec.MyText) then
Error(ErrorMsg);
DivValue := StrLen(Rec.MyText) div Rec.Maximumcharacters;
ModValue := StrLen(Rec.MyText) mod Rec.Maximumcharacters;
if ModValue = 0 then
SplitTo := DivValue
else
SplitTo := DivValue + 1;
RemainValue := Rec.MyText;
for i := 1 to SplitTo do begin
SplitResult := CopyStr(RemainValue, 1, Rec.Maximumcharacters);
if StrLen(RemainValue) >= Rec.Maximumcharacters + 1 then
RemainValue := CopyStr(RemainValue, Rec.Maximumcharacters + 1, StrLen(RemainValue));
AllValue := AllValue + ' ' + SplitResult;
end;
Message(Msg, StrLen(Rec.MyText), Rec.Maximumcharacters, SplitTo, AllValue);
end;
end;
}END
Hope this will help.
Thanks for reading.
ZHU
ć³ć”ć³ć