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.