Microsoft Dynamics 365 Business Central: How to Export and Import Data (Using XMLports)

Dynamics 365 Business Central

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.

ValueDescription
<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 stringsThe 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.

KeywordDescription
textelementDefines 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.
textattributeDefines 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.
tableelementDefines 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.
fieldelementDefines 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.
fieldattributeDefines 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

コメント

Copied title and URL