Dynamics 365 Business Central: How to import Links and Notes (System Parts) – Customization

Hi, Readers.
Last week we discussed how to export Links and Notes and how to copy Links and Notes in Business Central, and today I would like to talk about the last topic about them, how to import Links and Notes.

PS: There are two system parts that you can define by using the systempart() keyword: Links and Notes

System Parts:

Their data is saved in the same table.
RecordLinks (2000000068):

And you cannot use system table 2000000068 in the Configuration Package.

As with exporting and copying, as of now, we can only do that with customization.

table 2000000068 “Record Link”:

In this post, I mainly demonstrate how to import from Excel files. More details: How to use Excel Buffer to Import data

There are differences in the fields that Links and Notes actually need to import, and you can view the individual fields you need after adding them manually on the page.

The only thing to note this time is that, as with the export, the Note field is Blob type. Microsoft has prepared a standard conversion method for us in codeunit 447 “Record Link Management”.

The following is the template I will import this time. The Description field is for reference only and not actually imported.

Links:

Notes:

I added two actions to import Links and Notes, and for testing purposes, I also added a clear action.

Test video:

Source Code: Github (Please note that the source code is for reference only, you can improve it according to your own needs)

pageextension 50101 ZYItemListExt extends "Item List"
{
    actions
    {
        addfirst(processing)
        {
            action(ImportLinksFMExcel)
            {
                Caption = 'Import Links from Excel';
                ApplicationArea = All;
                Promoted = true;
                PromotedCategory = Process;
                PromotedIsBig = true;
                Image = Import;
                trigger OnAction()
                begin
                    ReadExcelSheet();
                    ImportLinksFromExcel();
                end;
            }
            action(ImportNotesFMExcel)
            {
                Caption = 'Import Notes From Excel';
                ApplicationArea = All;
                Promoted = true;
                PromotedCategory = Process;
                PromotedIsBig = true;
                Image = Import;
                trigger OnAction()
                begin
                    ReadExcelSheet();
                    ImportNotesFromExcel();
                end;
            }
            action(ClearAllLinksAndNotes)
            {
                Caption = 'Clear All Links and Notes';
                ApplicationArea = All;
                Promoted = true;
                PromotedCategory = Process;
                PromotedIsBig = true;
                Image = Import;
                trigger OnAction()
                begin
                    ClearAllLinksAndNotes(Rec);
                end;
            }
        }
    }
    var
        TempExcelBuffer: Record "Excel Buffer" temporary;
        UploadExcelMsg: Label 'Please Choose the Excel file.';
        NoFileFoundMsg: Label 'No Excel file found!';
        ExcelImportSucess: Label 'Excel is successfully imported.';
        LastLinkID: Integer;

    local procedure ImportLinksFromExcel()
    var
        RowNo: Integer;
        ColNo: Integer;
        LineNo: Integer;
        MaxRowNo: Integer;
        RecordLink: Record "Record Link";
        Item: Record Item;
    begin
        RowNo := 0;
        ColNo := 0;
        MaxRowNo := 0;
        LineNo := 0;
        TempExcelBuffer.Reset();
        if TempExcelBuffer.FindLast() then
            MaxRowNo := TempExcelBuffer."Row No.";
        LastLinkID := 0;
        GetLastLinkID();
        for RowNo := 2 to MaxRowNo do begin
            LastLinkID += 1;
            RecordLink.Init();
            RecordLink."Link ID" := LastLinkID;
            RecordLink.Company := CompanyName;
            RecordLink.Type := RecordLink.Type::Link;
            RecordLink.Created := CurrentDateTime;
            RecordLink."User ID" := UserId;
            Item.Get(GetValueAtCell(RowNo, 1));
            RecordLink."Record ID" := Item.RecordId;
            Evaluate(RecordLink.URL1, GetValueAtCell(RowNo, 3));
            Evaluate(RecordLink.Description, GetValueAtCell(RowNo, 4));
            RecordLink.Insert();
        end;
        Message(ExcelImportSucess);
    end;

    local procedure ImportNotesFromExcel()
    var
        RowNo: Integer;
        ColNo: Integer;
        LineNo: Integer;
        MaxRowNo: Integer;
        RecordLink: Record "Record Link";
        Item: Record Item;
        RecordLinkMgt: Codeunit "Record Link Management";
    begin
        RowNo := 0;
        ColNo := 0;
        MaxRowNo := 0;
        LineNo := 0;
        TempExcelBuffer.Reset();
        if TempExcelBuffer.FindLast() then
            MaxRowNo := TempExcelBuffer."Row No.";
        LastLinkID := 0;
        GetLastLinkID();
        for RowNo := 2 to MaxRowNo do begin
            LastLinkID += 1;
            RecordLink.Init();
            RecordLink."Link ID" := LastLinkID;
            RecordLink.Insert();
            RecordLink.Company := CompanyName;
            RecordLink.Type := RecordLink.Type::Note;
            RecordLink.Created := CurrentDateTime;
            RecordLink."User ID" := UserId;
            Item.Get(GetValueAtCell(RowNo, 1));
            RecordLink."Record ID" := Item.RecordId;
            RecordLinkMgt.WriteNote(RecordLink, GetValueAtCell(RowNo, 3));
            RecordLink.Modify();

        end;
        Message(ExcelImportSucess);
    end;

    local procedure ClearAllLinksAndNotes(Item: Record Item)
    var
        RecordLink: Record "Record Link";
    begin
        Item.Reset();
        if Item.FindSet() then
            repeat
                RecordLink.Reset();
                RecordLink.SetRange("Record ID", Item.RecordId);
                if RecordLink.FindSet() then
                    RecordLink.DeleteAll();
            until Item.Next() = 0;
    end;

    local procedure GetLastLinkID()
    var
        RecordLink: Record "Record Link";
    begin
        RecordLink.Reset();
        if RecordLink.FindLast() then
            LastLinkID := RecordLink."Link ID"
        else
            LastLinkID := 0;
    end;

    local procedure ReadExcelSheet()
    var
        FileMgt: Codeunit "File Management";
        IStream: InStream;
        FromFile: Text[100];
    begin
        UploadIntoStream(UploadExcelMsg, '', '', FromFile, IStream);
        if FromFile = '' then
            Error(NoFileFoundMsg);
        TempExcelBuffer.Reset();
        TempExcelBuffer.DeleteAll();
        TempExcelBuffer.OpenBookStream(IStream, TempExcelBuffer.SelectSheetsNameStream(IStream));
        TempExcelBuffer.ReadSheet();
    end;

    local procedure GetValueAtCell(RowNo: Integer; ColNo: Integer): Text
    begin

        TempExcelBuffer.Reset();
        If TempExcelBuffer.Get(RowNo, ColNo) then
            exit(TempExcelBuffer."Cell Value as Text")
        else
            exit('');
    end;
}

END

Hope this will help.

Thanks for reading.

ZHU