Vendors Import using Excel Add-in in Dynamics AX 2012

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

  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

Walkthrough:

Connection

 

Add Data

Add Tables

Field Chooser

VendTable

Field Name	Field Description
	Currency
	Group
	Name.Party ID
	Vendor account

Vendor account – Number sequence

DirPartyTable (Person)

Field Name	Field Description
	Name
	Party ID
	Party type
	Display as

Address book ID – Number sequence

LogisticsLocation

Field Name	Field Description
	Location ID
	Name or description
	Postal address

DirPartyLocation

Field Name	Field Description
	Effective:
	Location.Location ID
	Name.Party ID
	Expiration:
	Postal address

DirPartyLocationRole

Field Name	Field Description
	Location role.Role
	Party location relationships.Effective:
	Party location relationships.Location ID

LogisticsPostalAddress

Field Name	Field Description
	Country / region
	Effective:
	Location.Location ID
	State
	County
	City
	District
	Street
	ZIP / postal code
	Expiration:

LogisticsElectronicAddress

Field Name	Field Description
	Effective:
	Location.Location ID
	Type
	Contact number / address
	Expiration:

Sequence:

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

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

<!--[if !supportLists]-->3. <!--[endif]-->DirPartyLocation – Publish Selected

<!--[if !supportLists]-->4. <!--[endif]-->DirPartyLocationRole – Publish Selected

<!--[if !supportLists]-->5. <!--[endif]-->LogisticsElectronicAddress – Publish Selected

<!--[if !supportLists]-->6. <!--[endif]-->LogisticsPostalAddress – Publish Selected

<!--[if !supportLists]-->7. <!--[endif]-->CustTable – Publish Selected

Result:

Dynamics AX – Address book

Dynamics AX – Address book Address

Dynamics AX – Address book Contact information

Dynamics AX – Vendor

Dynamics AX – Vendor Address

Dynamics AX – Vendor Contact information

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.

Note: This document is intended for information purposes only, presented as it is with no warranties from the author.