Tuesday, January 17, 2012

Dynamics AX 2012 Sales orders Import using Excel Add-in

Dynamics AX 2012 Excel Add-in – Sales orders Import



Purpose: The purpose of this document is to illustrate how to use Microsoft Dynamics AX 2012 Excel Add-in for import of sales orders.

Challenge: Data model changes in Microsoft Dynamics related to high normalization and introduction of surrogate keys made some imports more complex. In fact the data model forming Sales Orders was not dramatically changed and import principle remains the same – populate the header and related lines. However some information which is usually automatically generated in Microsoft Dynamics AX 2012 Rich Client by means of number sequences such as sales order ID will have to be provided.

Solution: Microsoft Dynamics AX 2012 ships with Sales Orders AIF Web Service (SalesSalesOrderService) which can be used in integration scenarios. However Sales Order AIF Web Service can’t be used in Excel at the moment for import of sales orders. In order to import a sales order using Excel the appropriate tables will be used.

Assumption: The assumption is that appropriate reference data such as customers, etc. was created in advance.

Data Model:

Table Name

Table Description

SalesTable

The SalesTable table contains all sales order headers regardless of whether they have been posted.

SalesLine

The SalesLine table contains all sales order lines regardless of whether they have been posted.

Data Model Diagram:

image

Walkthrough:

Connection

image

Add Data

Error

image

Solution

- Add replacement key to CreditCardCust table

- Add replacement key to AgreementHeader table

- Add replacement key to SourceDocumentHeader table

- Add replacement key to DocuValue table

- Add replacement key to CustInvoiceLineIdRef table

- Add replacement key to MarkupTrans table

- Add replacement key to CustInvoiceLineTemplate table

- Add replacement key to BankLC table

Error

image

No Solution yet

Add Tables

image

Field Chooser

image

Sales order ID number sequence

image

SalesTable

Field Name

Field Description

Currency

Customer account

Customer group

Invoice account

Language

Requested ship date

Sales order

image

SalesLine

Field Name

Field Description

Sales order

Currency

Customer

Group

Requested ship date

Source document line.Reference table ID

Item number

Quantity

Unit price

Net amount

Dimension No.

image

InventDim

image

Excel VLookup function may be used to find appropriate InventDimId automatically based on criteria

Sequence:

1. SalesTable - Publish Selected

2. SalesLine – Publish Selected

Result:

Dynamics AX – Sales Order

image

Dynamics AX – Sales Order Invoice

image

SQL Trace:

Summary: For the purposes of small or medium data migration (data conversion) where performance is not a concern Excel can be used for import of sales orders into Microsoft Dynamics AX 2012. Although Sales Order AIF Web Service (SalesSalesOrderService) can’t be used at the moment in Excel for import of sales orders, appropriate tables can be used instead. Excel template can be created and used for import of sales orders.

Author: Alex Anikiev, PhD, MCP

Tags: Dynamics ERP, Dynamics AX 2012, Excel, Dynamics AX 2012 Excel Add-in, Data Import, Data Conversion, Data Migration, Application Integration Framework, Sales orders.

Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the concepts and describe the examples. It’s recommended that all Data Model changes introduced as a part of this demonstration will be removed once you complete data import exercise.

3 comments:

  1. Hi Alex,

    How is the replacement key created for the SourceDocumentHeader table?

    I use the createdDateTime field for the replacement key of the SourceDocumentHeader table but it does not work.

    Thanks advance,
    Yingjie

    ReplyDelete
  2. Hi Yingjie,
    Try to use SourceRelationType field in the replacement key it does work for me.

    ReplyDelete
    Replies
    1. That doesn't work.
      SourceRelationType is a reference to the source tableid and there will be multiple records containing the same SourceRelationType.
      e.g. SourceRelationType 366 => SalesTable
      I can't find any suitable replacement key here...
      The only way to get the SalesTable into Excel is to delete the relationship to the SourceDocumentHeader table. This will however create a data inconsistency and is no solution.

      Love to hear from Alex how he managed to do this.

      Delete