Hi, Readers.
Today I would like to briefly talk about how to export Links and Notes (System Parts) in Business Central.
Early this morning, I got a question from my LinkedIn. Their users enter Notes for the approver to read/view on the Purchase Invoices, they want to export these Notes before posting. How to do it?
As you might know, 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. |
These two system parts were also mentioned when we discussed How to add Attachments, Links, Notes Factbox to any pages.
Actually their data is saved in the same table.
RecordLinks (2000000068):
Just different types.
PS: You cannot use system table 2000000068 in the Configuration Package.
Well, it’s actually not difficult, as long as we know the structure of this table, we can do it very simply.
table 2000000068 “Record Link”: Link (URL) is not too bad as it is text type field. But the Note is stored in a Blob.
Don’t worry, Microsoft has prepared a standard conversion method for us in codeunit 447 “Record Link Management”.
/// <summary>
/// Read the Note BLOB
/// </summary>
/// <param name="RecordLink">The record link from which the note is read.</param>
/// <returns>The note as a text.</returns>
procedure ReadNote(RecordLink: Record "Record Link"): Text
begin
exit(RecordLinkImpl.ReadNote(RecordLink));
end;
PS: Of course, you can also use InStream Data Type to export the Blob type to Text without using the standard method. However, please try to use the standard method for better performance. More details: How to export Work Description (Blob) in Sales Documents to excel
Next, let’s assume a requirement where the user needs to export all Links and Notes to an excel file on the Item List page.
There are several points to note here.
1. How to export BC data to an Excel file. More details: How to use Excel Buffer to Export data
2. How to filter the RecordLinks (2000000068) table.
We can add filters to the “Record ID” field.
More details: How to quickly get the primary key values of the record (Record.GetPosition Method and RecordId Method)
For example,
3. How to convert Note (Blob data type) to Text to export. This has already been mentioned above.
Okay, let me do a simple test.
Export Links to Excel:
Export Notes to Excel:
Add two actions:
Test:
Export Links to Excel:
Export Notes to Excel:
It looks good.
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(ExportLinksToExcel)
{
Caption = 'Export Links to Excel';
ApplicationArea = All;
Promoted = true;
PromotedCategory = Process;
PromotedIsBig = true;
Image = Export;
trigger OnAction()
begin
ExportLinksToExcel(Rec);
end;
}
action(ExportNotesToExcel)
{
Caption = 'Export Notes to Excel';
ApplicationArea = All;
Promoted = true;
PromotedCategory = Process;
PromotedIsBig = true;
Image = Export;
trigger OnAction()
begin
ExportNotesToExcel(Rec);
end;
}
}
}
local procedure ExportLinksToExcel(var Item: Record Item)
var
TempExcelBuffer: Record "Excel Buffer" temporary;
LinksLbl: Label 'Links';
ExcelFileName: Label 'Links_%1_%2';
RecordLink: Record "Record Link";
begin
TempExcelBuffer.Reset();
TempExcelBuffer.DeleteAll();
TempExcelBuffer.NewRow();
TempExcelBuffer.AddColumn(Item.FieldCaption("No."), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(Item.FieldCaption(Description), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(RecordLink.FieldCaption(URL1), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(RecordLink.FieldCaption(Description), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(RecordLink.FieldCaption(Created), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(RecordLink.FieldCaption("User ID"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
if Item.FindSet() then
repeat
RecordLink.Reset();
RecordLink.SetRange("Record ID", Item.RecordId);
RecordLink.SetRange(Type, RecordLink.Type::Link);
if RecordLink.FindSet() then
repeat
TempExcelBuffer.NewRow();
TempExcelBuffer.AddColumn(Item."No.", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(Item.Description, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(RecordLink.URL1, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(RecordLink.Description, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(RecordLink.Created, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(RecordLink."User ID", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
until RecordLink.Next() = 0;
until Item.Next() = 0;
TempExcelBuffer.CreateNewBook(LinksLbl);
TempExcelBuffer.WriteSheet(LinksLbl, CompanyName, UserId);
TempExcelBuffer.CloseBook();
TempExcelBuffer.SetFriendlyFilename(StrSubstNo(ExcelFileName, CurrentDateTime, UserId));
TempExcelBuffer.OpenExcel();
end;
local procedure ExportNotesToExcel(var Item: Record Item)
var
TempExcelBuffer: Record "Excel Buffer" temporary;
NotesLbl: Label 'Notes';
ExcelFileName: Label 'Notes_%1_%2';
RecordLink: Record "Record Link";
RecordLinkMgt: Codeunit "Record Link Management";
begin
TempExcelBuffer.Reset();
TempExcelBuffer.DeleteAll();
TempExcelBuffer.NewRow();
TempExcelBuffer.AddColumn(Item.FieldCaption("No."), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(Item.FieldCaption(Description), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(RecordLink.FieldCaption(Note), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(RecordLink.FieldCaption(Created), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(RecordLink.FieldCaption("User ID"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
if Item.FindSet() then
repeat
RecordLink.Reset();
RecordLink.SetRange("Record ID", Item.RecordId);
RecordLink.SetRange(Type, RecordLink.Type::Note);
if RecordLink.FindSet() then
repeat
TempExcelBuffer.NewRow();
TempExcelBuffer.AddColumn(Item."No.", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(Item.Description, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
RecordLink.CalcFields(Note);
TempExcelBuffer.AddColumn(RecordLinkMgt.ReadNote(RecordLink), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(RecordLink.Created, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(RecordLink."User ID", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
until RecordLink.Next() = 0;
until Item.Next() = 0;
TempExcelBuffer.CreateNewBook(NotesLbl);
TempExcelBuffer.WriteSheet(NotesLbl, CompanyName, UserId);
TempExcelBuffer.CloseBook();
TempExcelBuffer.SetFriendlyFilename(StrSubstNo(ExcelFileName, CurrentDateTime, UserId));
TempExcelBuffer.OpenExcel();
end;
}
PS: You can find more details about Manage Attachments, Links, and Notes on Cards and Documents in MS Learn (Docs).
Update:
1. How to copy Links and Notes (System Parts) – Customization
2. How to import Links and Notes (System Parts) – Customization
END
Hope this will help.
Thanks for reading.
ZHU
コメント