Hi, Readers.
We discussed how to import and export data in Business Central last week. we can use the standard function Configuration Packages, or use Excel Buffer to export and import data.
For Details:
Microsoft Dynamics 365 Business Central: How to Export and Import Data (Using Configuration Packages)
Microsoft Dynamics 365 Business Central: How to use Excel Buffer to Export data
Microsoft Dynamics 365 Business Central: How to use Excel Buffer to Import data
This time I want to discuss another method to import or export data in Business Central by XMLports object.
XMLport Overview
An XMLport is composed of the following items:
・An XMLport object
・An XMLport schema
・A Request page
・Properties, triggers, and code
XMLport object
XMLports are used to export and import data between an external source and Dynamics 365 Business Central. First, let us look at some important properties.
Format: Sets the formats of the source expression for various data types. (Xml, VariableText, FixedText)
Direction: Sets the XmlPort to import, export, or import and export data in XML format. (Import, Export, Both)
TextEncoding: Specifies the text encoding format to use when you use an XmlPort to export or import data as text. (MSDOS, UTF8, UTF16, WINDOWS)
UseRequestPage: Sets whether a request page is presented to the user.
TableSeparator: Specifies the table separator for a table.
| Value | Description |
|---|---|
| <None> | There is no table separator. |
| <NewLine> | Any combination of CR and LF characters. |
| <CR/LF> | CR followed by LF. |
| <CR> | CR alone. |
| <LF> | LF alone. |
| <TAB> | Tabulator alone. |
| Other strings | The literal string entered. |
XMLport schema
The schema determines which and how data is extracted from or inserted into the Dynamics 365 Business Central database tables through an XMLport.
You build the XMLport schema from nodes. A node can be:
A text element
A text attribute
A table element
A field element
A field attribute
You nest nodes inside other nodes in order to define the structure of the XMLport schema. Use the following keywords to define the structure.
| Keyword | Description |
|---|---|
textelement | Defines a new text element in the XMLport. It is used for XML elements that do not map to a database item or when the database does not need the information. |
textattribute | Defines a new text attribute in the XMLport. It is used for XML attributes that do not map to a database item or when the database does not need the information. |
tableelement | Defines a new table element in the XMLport. It is used for XML elements that map to a table in the database, which is specified in the SourceTable Property. When the XMLport is run, the code nested inside the table element is iterated for all records in the underlying table. |
fieldelement | Defines a new field element in the XMLport. It is used for XMLport elements that map to a field in the database, which is specified in the SourceField Property. You must specify it inside the parent table element of the field. |
fieldattribute | Defines a new field attribute in the XMLport. It is used for XMLport attributes that map to a field in the database, which is specified in the SourceField Property. |
Request page
Request pages are dialog boxes that enable the user to set a filter on the data, sort the data, or choose whether to export or import the data. Unlike report request pages, XMLport request pages cannot be bookmarked by users from the user interface.
Properties, triggers, and code
XMLport objects include triggers, methods, and properties that can be used to work with the object.
Find out more about XMLportsfrom Microsoft Docs.
Export Data
To use an XMLport to import or export data, you first create an XMLport object. Once created, you can run the XMLport from a page or codeunit object.
For Example: Export Item list.
1. Create a new XMLport object.
2. Add titles of export fields
3. Add fields you want to export.
4. Add a new action on Item List page to run the Export XMLport.
Run(Number: Integer, [RequestWindow: Boolean], [Import: Boolean], var [Record: Table])
Xmlport.Run(50100, true, false);
5. Test.
Source Code of Export XMLPORT:
xmlport 50100 ExportItemsXmlPort
{
Format = VariableText;
Direction = Export;
TextEncoding = UTF8;
UseRequestPage = false;
TableSeparator = '';//New line
schema
{
textelement(Root)
{
tableelement(Integer; Integer)
{
XmlName = 'ItemHeader';
SourceTableView = SORTING(Number) WHERE(Number = CONST(1));
textelement(ItemNoTitle)
{
trigger OnBeforePassVariable()
begin
ItemNoTitle := Item.FieldCaption("No.");
end;
}
textelement(ItemDescTitle)
{
trigger OnBeforePassVariable()
begin
ItemDescTitle := Item.FieldCaption(Description);
end;
}
textelement(ItemTypeTitle)
{
trigger OnBeforePassVariable()
begin
ItemTypeTitle := Item.FieldCaption(Type);
end;
}
textelement(ItemInventoryTitle)
{
trigger OnBeforePassVariable()
begin
ItemInventoryTitle := Item.FieldCaption(Inventory);
end;
}
textelement(ItemBaseUnitofMeasureTitle)
{
trigger OnBeforePassVariable()
begin
ItemBaseUnitofMeasureTitle := Item.FieldCaption("Base Unit of Measure");
end;
}
textelement(ItemBaseCostisAdjustedTitle)
{
trigger OnBeforePassVariable()
begin
ItemBaseCostisAdjustedTitle := Item.FieldCaption("Cost is Adjusted");
end;
}
textelement(ItemUnitCostTitle)
{
trigger OnBeforePassVariable()
begin
ItemUnitCostTitle := Item.FieldCaption("Unit Cost");
end;
}
textelement(ItemUnitPriceTitle)
{
trigger OnBeforePassVariable()
begin
ItemUnitPriceTitle := Item.FieldCaption("Unit Price");
end;
}
textelement(ItemVendorNoTitle)
{
trigger OnBeforePassVariable()
begin
ItemVendorNoTitle := Item.FieldCaption("Vendor No.");
end;
}
}
tableelement(Item; Item)
{
XmlName = 'Item';
RequestFilterFields = "No.";
fieldelement(No; Item."No.")
{
}
fieldelement(Description; Item.Description)
{
}
fieldelement(Type; Item.Type)
{
}
fieldelement(Inventory; Item.Inventory)
{
}
fieldelement(BaseUnitofMeasure; Item."Base Unit of Measure")
{
}
fieldelement(CostisAdjusted; Item."Cost is Adjusted")
{
}
fieldelement(UnitCost; Item."Unit Cost")
{
}
fieldelement(UnitPrice; Item."Unit Price")
{
}
fieldelement(VendorNo; Item."Vendor No.")
{
}
}
}
}
}Import Data
For Example: Import Items.
1. Create a new XMLport object.
2. Add fields you want to import. (Title is not needed for importing)
3. Add a new action on Item List page to run the Import XMLport.
Run(Number: Integer, [RequestWindow: Boolean], [Import: Boolean], var [Record: Table])
Xmlport.Run(50101, false, true);
4. Create a new file to be imported
5. Test.
Source Code of Import XMLPORT:
xmlport 50101 ImportItemsXmlPort
{
Format = VariableText;
Direction = Import;
TextEncoding = UTF8;
UseRequestPage = false;
TableSeparator = '';//New line
schema
{
textelement(Root)
{
tableelement(Item; Item)
{
XmlName = 'Item';
fieldelement(No; Item."No.")
{
}
fieldelement(Description; Item.Description)
{
}
fieldelement(Type; Item.Type)
{
}
fieldelement(Inventory; Item.Inventory)
{
}
fieldelement(BaseUnitofMeasure; Item."Base Unit of Measure")
{
}
fieldelement(CostisAdjusted; Item."Cost is Adjusted")
{
}
fieldelement(UnitCost; Item."Unit Cost")
{
}
fieldelement(UnitPrice; Item."Unit Price")
{
}
fieldelement(VendorNo; Item."Vendor No.")
{
}
}
}
}
}PageExtension:
pageextension 50100 ItemExt extends "Item List"
{
actions
{
addafter(History)
{
action(ExportItems)
{
Caption = 'Export Items';
Promoted = true;
PromotedCategory = Process;
Image = Export;
ApplicationArea = All;
trigger OnAction()
begin
Xmlport.Run(50100, true, false);
end;
}
action(ImportItems)
{
Caption = 'Import Items';
Promoted = true;
PromotedCategory = Process;
Image = Import;
ApplicationArea = All;
trigger OnAction()
begin
Xmlport.Run(50101, false, true);
end;
}
}
}
}Hope this will help.
Thanks for reading.
ZHU
コメント