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:

Walkthrough:

Connection

Add Data

Error

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

No Solution yet

Add Tables

Field Chooser

Sales order ID number sequence

SalesTable

Field Name	Field Description
	Currency
	Customer account
	Customer group
	Invoice account
	Language
	Requested ship date
	Sales order

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.

InventDim

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

Dynamics AX – Sales Order Invoice

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.