Structured data import with Xmlports

An often needed functionality is importing existing data into nav. In many cases the data is structured and not only a simple csv file. there you maybe have connected data on at least 2 levels. Samples for that are sales orders, sales quotes, sales invoices.

As a sample i show, how to import a sales order containing 3 sales lines using a xmlport. Needed tables in this scenario are tables Sales Header and Sales Line. They are used as nodes in the xmlport. As you can see in the image below, first line is called SalesHeader (the node name), node type = element, source type = table, data source = sales header.

after that first step add the needed fields at structure level 1 (for that click -> button to shift right). the node name on a single level must be unique.  when selecting field as source type you can select the table field via assistant.

Next step is adding sub table “Sales Line” as new node on the same level as the “Sales Header” fields. now add the fields, you want to import,  under the new node. shift them to next level, level 2.

A really important thing now is to link the nodes! For that use node’s “Sales Line” properties LinkTable and LinkFields. Set LinkTable to Sales Header as shown above. Additional set property LinkFields to Document No.=Document No., Sell-to Customer No.=Sell-to Customer No., Document No.=No.. Save the changes.

With that xmlport i imported a sample sales order containing 3 sales lines without any problem .

the sample xml import file:
<SalesHeader>
<DocType>1</DocType>
<DocNo>101006</DocNo>
<CustNo>30000</CustNo>
<DocDate>2016-01-14</DocDate>
<SalesLine>
<DocType>1</DocType>
<DocNo>101006</DocNo>
<CustNo>30000</CustNo>
<LineNo>10000</LineNo>
<Type>2</Type>
<No>1920-S</No>
</SalesLine>
<SalesLine>
<DocType>1</DocType>
<DocNo>101006</DocNo>
<CustNo>30000</CustNo>
<LineNo>20000</LineNo>
<Type>2</Type>
<No>1000</No>
</SalesLine>
<SalesLine>
<DocType>1</DocType>
<DocNo>101006</DocNo>
<CustNo>30000</CustNo>
<LineNo>30000</LineNo>
<Type>2</Type>
<No>1001</No>
</SalesLine>
</SalesHeader>

cheers
Advertisements

2 thoughts on “Structured data import with Xmlports

  1. Your import example above contains all the key data fields in the import file, which can be mapped directly to the tables. I’m attempting to create a similar import, but need to insert the records via code, as the import file only contains some of the fields, not the key fields. The error that’s returning indicates the Sales Header file hasn’t been created by the time the Sales Line fields are being added, ie. Record cannot be found Sales Header.No.
    I’m guessing there’s something simple that I’m missing, but not sure what. When I just import and create the header file, it works fine, just doesn’t work to add the Lines.
    Can you help?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: