Tuesday, January 17, 2012

Dynamics AX 2012 Data Import using Excel Add-in

Dynamics AX 2012 Excel Add-in – Data Import

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

Challenge: Data model changes in Dynamics AX related to high normalization and introduction of surrogate keys made import of data more complex. Additionally there are number of differences between Excel and AX Rich Client from UI standpoint when it comes to defaulting logic, use of number sequences, segmented controls and complex UI logic. AX Rich Client is obviously more dynamic comparing to Excel. However a lot of post-processing is usually done behind the scenes by means of AIF Proxy classes when the information is published into Dynamics AX.

Solution: Dynamics AX 2012 ships with the number of AIF Web Service which can be used in integration scenarios. However not all of them can be used in Excel at the moment for import of data. In order to import data using Excel the appropriate tables can be used instead.

Data Model:

Table Name

Table Description

AlexParent

Sample table

AlexTable

Sample header table which extends from sample table

AlexLine

Sample lines table

Data Model Diagram:

Sample Data Model

image

Walkthrough:

Connection

image

Add Tables

image

Dynamics AX Error

image

image

Solution

<!--[if !supportLists]-->- <!--[endif]-->Add replacement key to AlexParent table

<!--[if !supportLists]-->- <!--[endif]-->Add replacement key to AlexTable table

<!--[if !supportLists]-->- <!--[endif]-->Add replacement key to AlexLine table

Project

image

AlexParent table

image

AlexTable table

image

AlexLine table

image

Once Replacement key is added on AlexTable table Excel Add-in will be able to interpret Surrogate key relation between AlexTable table and AlexLine table using Natural key (Replacement key).

Table/Field Properties

image

Query

image

Generate AIF Proxy classes

Select Document Parameters

image

Select Code Generation Parameters

image

Completed

image

Project AxdAlex

image

Generate Incremental CIL

image

Field Chooser

Excel Add-in takes into account table inheritance

Also please note that ID field in AlexTable table (and ParentID field in AlexParent table) is used for Replacement Key <-> Surrogate Key translation

image

AlexTable

Field Name

Field Description

InstanceRelationType

ParentID

ID

A

B

C

D

image

AlexLine

Field Name

Field Description

AlexTable.ParentID

AlexTable.ID

E

F

image

Sequence:

<!--[if !supportLists]-->1. <!--[endif]-->AlexTable – Publish Selected

<!--[if !supportLists]-->2. <!--[endif]-->AlexLine – Publish Selected

Result:

Dynamics AX – Table Browser

AlexParent

image

AlexTable

image

AlexLine

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 data into Dynamics AX 2012. Although some of AIF Web Services can’t be used at the moment in Excel for import of data, appropriate tables can be used instead. Excel template can be created and used for import of data.

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, Table Inheritance, Surrogate Key, Natural Key, Replacement Key.

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.

12 comments:

  1. Alex, you are a genius!!!
    That is exactly what I has been looking for for a long time.
    I had problems importing data to the Postal/Zip Code table cause it has a foreign key relation with the surrogate key based on RecId in the City table.
    Will try your solution soon.

    ReplyDelete
  2. Hi Alex,

    I have a big problem. I have done this everythig. But, publish buttons are not enebled for me. Can you please help me out, like what to check and where to check?

    Abhishek

    ReplyDelete
    Replies
    1. Hi Abhishek!

      Please UNpress "Field Chooser" button (by other words click on it again) in the ribbon bar, then "Publish" button will be enabled

      Excel Add-in distinguishes between design mode when you compose your data set adding fields from "Field Chooser" area and runtime mode when you manipulate with the data using "Publish" button

      Best Regards,
      /Alex

      Delete
  3. Hi Alex,

    Thanks for your reply. It works..Thanks..!!! :)

    I have a diff problem now. When I am going to import data of hcmEmplpyement table, it shows me error, AxhcmEmployement could not be instatained. pls help.

    Abhishek

    ReplyDelete
  4. Alex,

    When i try to upload InventModelgroupitem table using excel add in ,The look up is available for itemmodelgroup table. But The look up for item number and company is not enabled. So i could not able to select the item number.

    Then i tried to give it manually and uploaded, but getting the below issue.

    Is there any specific setting needs to be applied to some tables? please clarify.


    issue:
    =====
    " If the ModelGroupId field is not blank, there must be a record in the InventModelGroup table. If the record is not found, make sure the user has permission to perform a cross-company select on this table. The security framework will allow the select statement to return records for the companies in which the current user has explicit access of the tables involved. Therefore, it will return 0 records if the user does not have explicit access to the tables even if the tables are not AOSAuthorization-protected.
    Document Generic document could not be created. Error details: If the ModelGroupId field is not blank, there must be a record in the InventModelGroup table. If the record is not found, make sure the user has permission to perform a cross-company select on this table. The security framework will allow the select statement to return records for the companies in which the current user has explicit access of the tables involved. Therefore, it will return 0 records if the user does not have explicit access to the tables even if the tables are not AOSAuthorization-protected.
    "
    Thanks
    Uday

    ReplyDelete
    Replies
    1. Hi Uday!

      Using InventModelGroupItem table you can specify all 4 fields: Company for item, Item number, Item model group, Item model group.Company for item model group, and have the system create an entry for you. It may be handy if you created released product but haven't specified Item model group during creation, afterwards you can use Excel to specify Item model group for released product. I could create new record with no problems after I typed in values for all 4 fields.

      There's a lookup available for "Item model group" and "Item model group.Company for item model group" fields. Please note that it's joint lookup meaning that when you select one value the second value will be defaulted automatically.

      Initially there's no lookup for "Company for item" and "Item number" fields provided.

      Best Regards,
      /Alex

      Delete
  5. Hi Alex,

    Did you try to add custom fields to any of existing table and then populate data into the custom fields?

    I have been having difficulties with this. I added new custom fields
    like Name 2 to CustGroup or AssetTable. I compiled the objects couple times and the ran Generate Incremental CIL.
    I could add the new fields for tables above in Excel using Field Chooser. I tried add new record in excel to AssetTable

    Fixed asset group Fixed asset number Name Name 2
    TEST01 TEST01 Test Test Name2

    as a result I got
    Fixed asset group Fixed asset number Name Name 2
    TEST01 TEST01 Test

    the field Name 2 was empty.

    I made several attempts I got the same result Excel does not populate a value to custom fields.

    Did you experience the similar problem?

    Thank you,
    Victor

    ReplyDelete
  6. Hi Alex,

    I am trying to import all number sequences from AX 4.0 to AX 2102 through excel add-in.
    I belive that NumberSequenceTable and NumberSequenceScope are the right tables to load data in. I don't have any problem loading these tables but I have problem with the data.
    I am not sure which data should I put in "NumberSequenceScope.PartyID" and "NumberSequenceScope.PartyID1". Which ever data I am trying to put in it is giving me this error :

    "Invalid document schema. The following error was returned: The element 'NumberSequenceScope' in namespace 'http://schemas.microsoft.com/dynamics/2008/01/documents/NumberSequenceTable' has invalid child element 'OperatingUnit_PartyNumber' in namespace 'http://schemas.microsoft.com/dynamics/2008/01/sharedtypes'. List of possible elements expected: 'LegalEntity_DataArea' in namespace 'http://schemas.microsoft.com/dynamics/2008/01/sharedtypes'.

    Would you please help me to figure out solution for this?
    I will really appriciate your help.

    Thanks and Regards,
    Ekta Mehta

    ReplyDelete
  7. Hi Alex,

    When I press on publish select for (EcoResStorageDimensionGroupItem)
    to import the storage dimension, I faced this error:
    Document Generic document could not be created. Error details:

    Kindly advice if you have solution for this error.

    Thanks in Advance
    Amr Esam

    ReplyDelete
  8. hi alex,
    i added due date in axdledgergeneraljournal query,in excel i got the field,
    but when i added new record and publish the due date is not saved.can u give me a solution.

    ReplyDelete
  9. FYI: This comment seems to be about termite control. https://translate.google.com/#auto/en/M%D8%B4%D8%B1%D9%83%D8%A9%20%D9%85%D9%83%D8%A7%D9%81%D8%AD%D8%A9%20%D8%AD%D8%B4%D8%B1%D8%A7%D8%AA%20%D8%A8%D8%A8%D8%B1%D9%8A%D8%AF%D8%A9%0A%D8%B4%D8%B1%D9%83%D8%A9%20%D9%85%D9%83%D8%A7%D9%81%D8%AD%D8%A9%20%D8%AD%D8%B4%D8%B1%D8%A7%D8%AA%20%D8%A8%D8%A7%D9%84%D9%82%D8%B5%D9%8A%D9%85%0A%D8%B4%D8%B1%D9%83%D8%A9%20%D9%85%D9%83%D8%A7%D9%81%D8%AD%D8%A9%20%D8%AD%D8%B4%D8%B1%D8%A7%D8%AA%20%D8%A8%D8%B9%D9%86%D9%8A%D8%B2%D8%A9%0A%D8%B4%D8%B1%D9%83%D8%A9%20%D9%85%D9%83%D8%A7%D9%81%D8%AD%D8%A9%20%D8%AD%D8%B4%D8%B1%D8%A7%D8%AA%20%D8%A8%D8%A7%D9%84%D8%B1%D8%B3%0A%D9%85%D9%83%D8%A7%D9%81%D8%AD%D8%A9%20%D8%A7%D9%84%D9%86%D9%85%D9%84%20%D8%A7%D9%84%D8%A7%D8%A8%D9%8A%D8%B6%20%D8%A8%D8%A8%D8%B1%D9%8A%D8%AF%D8%A9%0A%D9%85%D9%83%D8%A7%D9%81%D8%AD%D8%A9%20%D8%A7%D9%84%D9%86%D9%85%D9%84%20%D8%A7%D9%84%D8%A7%D8%A8%D9%8A%D8%B6%20%D8%A8%D8%A7%D9%84%D9%82%D8%B5%D9%8A%D9%85%0A%D9%85%D9%83%D8%A7%D9%81%D8%AD%D8%A9%20%D8%A7%D9%84%D9%86%D9%85%D9%84%20%D8%A7%D9%84%D8%A7%D8%A8%D9%8A%D8%B6%20%D8%A8%D8%B9%D9%86%D9%8A%D8%B2%D8%A9%0A%D9%85%D9%83%D8%A7%D9%81%D8%AD%D8%A9%20%D8%A7%D9%84%D9%86%D9%85%D9%84%20%D8%A7%D9%84%D8%A7%D8%A8%D9%8A%D8%B6%20%D8%A8%D8%A7%D9%84%D8%B1%D8%B3

    ReplyDelete