Tuesday, January 17, 2012

Dynamics AX 2012 Vendors Import using Excel Add-in

Dynamics AX 2012 Excel Add-in – Vendors Import



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

Challenge: Data model changes in Microsoft Dynamics related to high normalization and introduction of surrogate keys made import of vendors more complex. In order to create a vendor in Microsoft Dynamics AX 2012 both vendor 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 vendor number will also have to be provided.

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

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

Data Model:

Table Name

Table Description

VendTable

The VendTable table contains vendors for accounts payable.

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

Vendors

image

Walkthrough:

Connection

image

Add Data

image

Dynamics AX Error

image

Add Tables

image

Field Chooser

image

VendTable

Field Name

Field Description

Currency

Group

Name.Party ID

Vendor account

image

Vendor 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

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 – Vendor

image

Dynamics AX – Vendor Address

image

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

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

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. i did as what u mention.
    Export the following tables.
    dirpartylocation
    dirpartylocationrole
    dirpartytable
    logiscselectronicaddress
    logisticslocation
    logisticspostaladdress
    vendtable.

    But im facing the problem of sarrogate key on "Logisticspostaladdress"

    Please Help.

    ReplyDelete
  2. dear export the following tables, i have problem of emplacement key on "logisticspostaladdress" for the table "logisticsaddresscity" "logisiticsaddressdistrict" and "logisticsaddresszipcode"

    thank,

    ReplyDelete
  3. This is my first time trying to use your excellent guides. Can you advise why we do not have the "Publish Data" available to us. It is greyed out

    Many thanks

    ReplyDelete
    Replies
    1. Hi Michael!

      Please unpress 'Field Chooser' button and 'Publish' button will become available
      If 'Field Chooser' button is pressed you are in Design mode, when it's unpressed you are in Runtime mode

      Best Regards,
      /Alex

      Delete
    2. Dear Alex.

      i did as what u mention.
      Export the following tables.
      dirpartylocation
      dirpartylocationrole
      dirpartytable
      logiscselectronicaddress
      logisticslocation
      logisticspostaladdress
      vendtable.

      But im facing the problem of sarrogate key on "Logisticspostaladdress"

      Please Help.

      Delete
  4. Alex, thanks for the helpful article! Right now I'm getting an error message regarding the Party ID when I try to import Vendors via Excel.

    Here's what I've done so far:
    1) Add tables- VendTable only. I may add some of the other tables later for the addresses, but for now I'm only concerned with the VendTable. This means I have the following required fields "Currency, Group, Name.PartyID, Vendor Account" as well as "Name" (I added this one using the Field Chooser).

    2) Publish Data- I do this to see my existing Vendor data to validate that it is pulling from AX correctly.

    3) Create a new test record. Currency, Group, Vendor Account and Name are easy. It's the Name.PartyID that's giving me trouble.
    If I leave it blank, AX tells me I have to fill in a value in order to publish it. If I fill it with the next number in the sequence, I get the following error:

    VendTable.createList Line=4, Pos=4, Xpath=/VendTable/VendTable[1] The value in field Party is invalid. Creation has been canceled.

    When I see this error, it seems like a Number Sequences issue. Could you please provide some info on how you have this sequence set up?

    Looking at your example, it shows that you have entered 'AX2012-ALEX' as the PartyID. Is this a constant? How is that possible?

    Any other ideas as to why this is working correctly?

    ReplyDelete
  5. Hi Alex really helpful article but after following steps just you have mentioned i get bellow error while publishing LogisticPostalAddress table
    "Cannot create a record in Addresses (Logistics Postal Address). Insert not supported with the values specified for 'Effective:' and 'Expiration:'. New record overlaps with multiple existing records.
    "

    Thanks.

    ReplyDelete
  6. Hi Alex..

    Account Payable not exist in 'Add Data'
    Need help

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

    ReplyDelete
  8. In AX 2012, I have added address field in Vendor form DirPartyNameAddress table. But I am not able to see vendor in vendor form if I do not add address to the Party table.Can you let me know why this happens.

    ReplyDelete
  9. Hi Alex,

    Could you help for the issues below. I have set the date lower than of first record but still cannot publish. Thank you so much:

    Cannot create a record in Person name (DirPersonName). Invalid value specified for 'Effective:'. It cannot be greater than or equal to the 'Effective:' of the first record.

    Document Generic document could not be created. Error details: Cannot create a record in Person name (DirPersonName). Invalid value specified for 'Effective:'. It cannot be greater than or equal to the 'Effective:' of the first record.

    ReplyDelete
  10. Hi
    Can anybody help on how to import employees master, which table need to import in excel ?

    ReplyDelete