Tuesday, January 17, 2012

Dynamics AX 2012 Customers Import using Excel Add-in

Dynamics AX 2012 Excel Add-in – Customers Import



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

Challenge: Data model changes in Microsoft Dynamics related to high normalization and introduction of surrogate keys made import of customers more complex. In order to create a customer in Microsoft Dynamics AX 2012 both customer and party information will have to be provided. Additionally some information which is usually automatically generated in Microsoft Dynamics AX 2012 Rich Client by means of number sequences such as customer number will also have to be provided.

Solution: Microsoft Dynamics AX 2012 ships with Customer AIF Web Service (CustCustomerService) which can be used in integration scenarios. However Customer AIF Web Service can’t be used in Excel at the moment for import of customers. In order to import a customer with communication details and address information using Excel the appropriate tables will be used.

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

Data Model:

Table Name

Table Description

CustTable

The CustTable table contains a list of customers for the accounts receivable and customer relationship management.

DirAddressBook

The DirAddressBook table contains address book records.

DirAddressBookParty

The DirAddressBookParty table is a relationship table that contains a link between an address book and party records.

DirPartyTable

The DirPartyTable table contains all the parties. The global address book stores all of the people and organizations that are used in the system.

DirPerson

The DirPerson table contains the party records of person.

DirOrganizationBase

The DirOrganizationBase table contains definitions for internal and external organizations.

DirOrganization

The DirOrganization table contains details for the external organizations in the system.

DirPersonName

The DirPersonName table contains the date effective names for each person in the system.

DirOrganizationName

The DirOrganizationName table contains the date effective names for each of the organizations in the system.

DirDunsNumber

The DirDunsNumber table contains definitions of the DUNS numbers.

DirNameAffix

The DirNameAffix table contains the name titles and suffixes that are defined in the system.

LogisticsLocation

The LogisticsLocation table contains the attributes for a location for postal address and contact information.

LogisticsLocationExt

The LogisticsLocationExt table contains additional information about the locations.

LogisticsLocationRole

The LogisticsLocationRole table contains the roles that are played by the locations in the system.

LogisticsLocationRoleTranslation

The LogisticsLocationRoleTranslation table contains the translation values for the location role descriptions in the languages that are supported in the system.

DirPartyLocation

The DirPartyLocation table contains the relationship between the party and location.

DirPartyLocationRole

The DirPartyLocationRole table is the relationship table between the location and function of the party.

LogisticsElectronicAddress

The LogisticsElectronicAddress table contains communication values that are defined for various parties.

LogisticsPostalAddress

The LogisticsPostalAddress table contains addresses in the system that could be associated with various entities.

LogisticsAddressCountryRegion

The LogisticsAddressCountryRegion table contains definitions of countries or regions. Addresses are associated with countries and regions through CountryRegionId field.

LogisticsAddressCountryRegionTranslation

The LogisticsAddressCountryRegionTranslation table contains the translations of the country region long and short names.

LogisticsAddressState

The LogisticsAddressState table contains states that are used by addresses.

LogisticsAddressCounty

The LogisticsAddressCounty table contains definitions of counties.

LogisticsAddresssCity

The LogisticsAddresssCity table contains the definitions of the cities.

LogisticsAddressDistrict

The LogisticsAddressDistrict table contains the definitions of the districts.

LogisticsAddressZipCode

The LogisticsAddressZipCode table contains zip codes that can be used by addresses.

Data Model Diagram:

Global Address Book

image


VSD: https://docs.google.com/open?id=0B3rbAZy5q2ExZjI2ZmQwNTctMzZjMi00MDViLThiM2EtZDY3YjQ5OGJlNDA0


Red area highlights tables forming Address book – Person data model

Green area highlights tables forming Address book – Organization data models

Yellow area highlights tables implementing Electronic Address data model

Blue area highlights tables implementing Postal Address data model

Customers

image


Walkthrough:

Connection

image

Add Data

image

Dynamics AX Error

image

Add Tables

image

Field Chooser

image

CustTable

Field Name

Field Description

Currency

Customer account

Customer group

Name.Party ID

image

Customer account – Number sequence

image

DirPartyTable (Person)

Field Name

Field Description

Name

Party ID

Party type

Display as

image

Address book ID – Number sequence

image

LogisticsLocation

Field Name

Field Description

Location ID

Name or description

Postal address

image

DirPartyLocation

Field Name

Field Description

Effective:

Location.Location ID

Name.Party ID

Expiration:

Postal address

image

DirPartyLocationRole

Field Name

Field Description

Location role.Role

Party location relationships.Effective:

Party location relationships.Location ID

image

LogisticsPostalAddress

Field Name

Field Description

Country / region

Effective:

Location.Location ID

State

County

City

District

Street

ZIP / postal code

Expiration:

image

LogisticsElectronicAddress

Field Name

Field Description

Effective:

Location.Location ID

Type

Contact number / address

Expiration:

image image

Sequence:

1. DirPartyTable – Publish Selected

2. LogisticsLocation – Publish Selected

3. DirPartyLocation – Publish Selected

4. DirPartyLocationRole – Publish Selected

5. LogisticsElectronicAddress – Publish Selected

6. LogisticsPostalAddress – Publish Selected

7. CustTable – Publish Selected

Result:

Dynamics AX – Address book

image

Dynamics AX – Address book Address

image

Dynamics AX – Address book Contact information

image

Dynamics AX – Customer

image

Dynamics AX – Customer Address

image

Dynamics AX – Customer Contact information

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 customers into Microsoft Dynamics AX 2012. Although Customer AIF Web Service (CustCustomerService) can’t be used at the moment in Excel for import of customers, appropriate tables can be used instead. Excel template can be created and used for import of customers.

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

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.

23 comments:

  1. Alex,

    Nice post. But when I try to add table LogisticsPostalAddress in the forth step I received a message error that "The table [LogisticsPostalAddres] has a surrogate foreign key expansion errors: LogisticsAddressCity, LogisticsAddressDistrict, LogisticsAddressZipCode dosnot have a replacement key.

    ReplyDelete
    Replies
    1. Hi Orlando!

      Please add mentioned below Replacement keys to the following tables to get LogisticsPostalAddress table successfully added to Excel Add-in:
      - LogisticsAddresssCity table, Replacement key = Index1 {Name, RecId}
      - LogisticsAddressDistrict table, Replacement key = Index1 {Name, RecId}
      - LogisticsAddressZipCode table, Replacement key = Index1 {ZipCode, RecId}

      Please Refresh Dictionary in Development workspace after you add Replacement keys to tables, and restart Excel

      Please see your options as it relates to Replacement keys in this post:http://ax2012exceldataimport.blogspot.com/2012/01/dynamics-ax-2012-excel-add-in.html

      Best Regards,
      /Alex

      Delete
    2. You may also look into below link to resolve index issue.
      http://eprogrammers.blogspot.com/2011/12/data-import-error-in-microsoft-dynamics.html

      Delete
    3. This comment has been removed by the author.

      Delete
  2. Hi Alex,
    This is a nice post. Did you try if a customer has more than one contact then how to import using Excel Add-in?

    ReplyDelete
  3. Hi,

    While loading the data, i am getting an error for Surrogate key in LogisticsPostalAddress table.

    I would like to know how to add surrogate key for its connected tables.

    Thanks,
    Yash

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Alex,

    Thanks for doing these blogs. Have been a big help.

    I believe that you did the initial article before Feature Pack 1 came out. Has FP1 improved the use of the Excel add-in?

    Thanks!

    Paul S.

    ReplyDelete
  6. Hi Alex,

    Thank you for this blog, it has been very helpful.I have a question. I can see by the error message which table I have to place the foreign key, but I do not know which fields must have the new Index. Is there a way to know the fields I have to setup to fix this kind of error?

    Thank you in advanced

    Miguel

    ReplyDelete
  7. Alex.... I dont how to say thanks to you....

    You are "GENIUS" in AX

    ReplyDelete
  8. Hello ALex,
    How can import the customer table and Dirpartytable but with auto generated numbers for customer account and party id according to the number sequence assigned in the parameters?

    ReplyDelete
  9. I am getting below error. any idea

    The request failed with the following error: Invalid document schema. The following error was returned: The element 'DirPartyLocation' in namespace 'http://schemas.microsoft.com/dynamics/2008/01/documents/Party' has invalid child element 'LogisticsLocation' in namespace 'http://schemas.microsoft.com/dynamics/2008/01/documents/Party'. List of possible elements expected: 'Location' in namespace 'http://schemas.microsoft.com/dynamics/2008/01/documents/Party'.

    ReplyDelete
  10. Hi Alex,

    I can import customer list. everything is fine and display on form. however, i dont know why everytime i try to import DirPartyLocationRole table, system didnt show any error message but all data also didnt import. no successful or fail record and no new line was imported into table. What happend to it? can you help me? i just want to know there is any problem if i use this customer list or vendor list for any transaction.

    Im waiting for your support

    Thank you very much

    Rabia

    ReplyDelete
  11. Hi Alex,
    How can import multiple postal address in different location id using Excel. I'm created multiple Location id for a particular Party id and posted data successfully. But it won't be published in ax2012.
    Please help me as soon as possible.

    Thanks and Regards,
    Sathish.

    ReplyDelete
  12. Hi

    1) How to get excel template for "Employees" and related tables.
    2) How to get excel template for "Fixed Assets" and related tables

    can you provide any relational diagram for both of the above master data.

    Hussain Ali

    ReplyDelete
  13. How can a new contact be created using aif web service c#. I have create a new person to add to the contact but I am not sure how to? Any answers?

    ReplyDelete
  14. Hello Alex,
    How do I import the contact person for customer, Dynamics AX 2012 RTM

    ReplyDelete
  15. after addition of tables in excel while clicking on "Fields" button system is giving error "'you must include at least 2 items in any list used for updating data" can any body help

    ReplyDelete
    Replies
    1. Hi!

      Please add at least 2 fields into a table to bypass this error

      For example, when you add CustGroup table in Add Table by default the system will insert "Customer group" field into Excel table structure. All you need to do is to at least drag one more field, say "Default tax group". This is the requirement in Excel Add-in

      Best Regards,
      /Alex

      Delete
  16. Hi Alex Anikiev,

    i have a problem to create customer in Microsoft Dynamics with AIF, i want to assign the name for AddressBook in customer other information.

    Which file i need to assign the value to populate the data in to that.

    Thanks,
    Sri

    ReplyDelete
  17. Hi Alex, great post. Despite being a year old, this post still remains holistically pretty valuable. My only request of you would be to kindly post a better resolution entity relation diagram above, or perhaps point us to where it can be found on-line. Thanks // Denis

    ReplyDelete