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
| Value | Description |
|---|---|
| Links | Allows the user to add links to a URL or path on the record shown in the page. For example, on an Item card, a user can add a link to the supplier’s item catalog. The links will appear with the record when it is viewed. When a user chooses a link, the target file opens. |
| Notes | Allows the user to write a note on the record shown in the page. For example, when creating a sales order, a user can add a note about the order. The note will appear with the item when it is viewed. |
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
コメント