Dynamics 365 Business Central: 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)

Dynamics 365 Business Central

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

コメント

Copied title and URL