Tuesday, January 17, 2012

Dynamics AX 2012 Purchase orders Import using Excel Add-in

Dynamics AX 2012 Excel Add-in – Purchase orders Import



Purpose: The purpose of this document is to illustrate how to use Microsoft Dynamics AX 2012 Excel Add-in for import of purchase 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 Purchase 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 purchase order ID will have to be provided.

Solution:

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

Data Model:

Table Name

Table Description

PurchTable

The PurchTable table contains all the purchase order headers regardless of whether they have been posted.

PurchLine

The PurchLine table contains all purchase order lines regardless whether they have been posted or not.

Data Model Diagram:

image

Walkthrough:

Connection

image

Error

image

image

Add Tables

image

Field Chooser

image

Purchase order ID number sequence

image

PurchTable

Field Name

Field Description

Currency

Invoice account

Language

Purchase order

Vendor account

Vendor group

image

PurchLine

Field Name

Field Description

Currency

Group

Lot ID

Purchase order

Vendor account

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 – Purchase Order

image

Dynamics AX – Purchase Order Invoice

image

SQL Trace:

Summary:

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, Purchase 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.

9 comments:

  1. Esta parte no funciona por medio de addins para AX 2012! =(

    ReplyDelete
    Replies
    1. Hola rmzgbc, al día de hoy has encontrado alguna manera de importar órdenes de compra en AX 2012?

      Saludos

      Alex

      Delete
  2. Hi Alex, I tried this but I don´t know which lotid put for every record, the add-in show me the next error: Invalid lotid.
    And then I tried without putting a lotid but 0 from 0 registers upload message appear. What can I do ?

    Thanks in Advance

    Regards

    Arturo

    ReplyDelete
  3. Hello Alex,

    How did you get the Purchase order to be invoiced at import?
    another question,

    why are we using SalesTable and SalesLine instead?

    ReplyDelete
    Replies
    1. Yes need to know - what if we want to import the GRN and invoice information as well for the POs ?

      Delete
  4. Hi Alex,

    Thanks for your great demonstration, I want to know why the system doesn't retrieve the purchase price for trade agreement that attached to item , although it is working good when I import distinct product (without dimension) I don't catch the unit price filed in the excel and after finishing import the system automatically retrieve it purchase price from the trade agreement

    but when I follow the same steps for Product master (with dimension) it doesn't retrieve the price although I checked For purchase price & for sales Price Check boxes in storage and product dimension group

    ReplyDelete
  5. Hi Alex,

    Nice post to understand importing data.

    I want to know about that how we can generate number sequence for purchase order/sales order while importing data from excel?

    Kindly assist me for the same.

    Thanks,
    Kishor Jadhav
    kishorworld1@gmail.com

    ReplyDelete
  6. I think SalesTable is mentioned by mistake. It must be PurchTable and PurcLine

    ReplyDelete
  7. Thank you for describing the topic of Dynamics microsoft ax 2012 Purchase orders Import using Excel Add-in, this tutorial helped me a lot!

    ReplyDelete