Dynamics AX 2012 Excel Add-in - Add Data

Dynamics AX 2012 Excel Add-in – Add Data

Purpose: The purpose of this document is to illustrate how to enable AIF Web Service for Dynamics AX 2012 Excel Add-in.

Task: In order to enable AIF Web Service for Dynamics AX 2012 Excel Add-in 2 main prerequisites have to be met: AIF Web Service has to be deployed as a part of Service Group and AIF Web Service has to be added to Document Data Sources. This document describes the sequence of steps on how to enable existing (standard) or brand-new (custom) AIF Web Service for Dynamics AX 2012 Excel Add-in.

Data Model:

Table Name	Table Description
AlexTable	Sample table

Data Model Diagram:

Sample Data Model

Walkthrough:

Existing (standard) AIF Web Service

In this demonstration I will enable existing VendVendTableService (Vendor) AIF Web Service for Dynamics AX 2012 Excel Add-in

At the beginning VendVendTableService AIF Web Service is not available in Excel Add-in

In order to deploy VendVendTableService AIF Web Service I will add it to AccountsPayableServices Service Group which is currently not deployed

I added VendVendTableService AIF Web Service to AccountsPayableService Service Group in AOT and then deployed Service Group

After Service Group is deployed you will see the following infolog

Now Service Group will be activated in Inbound ports form in Administration > Setup > AIF

Please note that you can see WSDL in Web Browser as shown below

Finally VendVendTableService AIF Web Service has to be added to Document Data Sources form

Result:

VendVendTableService AIF Web Service is now available in Excel Add-in

Brand-new (custom) AIF Web Service

In this demonstration I will create custom AlexQueryService AIF Web Service

Project

Create Document Service

Welcome

Select document parameters

Select code generation parameters

Generate code

Completed

AxdAlexQuery Project

Generate incremental CIL

AIF Web Service

Add AIF Web Service to Service Group

Deploy Service Group

After Service Group is deployed you will see the following infolog

AlexServiceGroup will now be activated in Inbound ports form

Also you can see WSDL in Web Browser

Finally you will have to add AIF Web Service in Document data sources form

Result:

AlexQueryService AIF Web Service is now available in Excel Add-in

Summary: In order to enable AIF Web Service for Dynamics AX 2012 Excel Add-in 2 main prerequisites have to be met: AIF Web Service has to be deployed as a part of Service Group from AOT or from Inbound ports form in Administration > Setup > AIF and AIF Web Service has to be added to Document Data Sources on Document data sources form in Organization administration > Setup > Document management. You can add your AIF Web Service to newly created custom Service Group for deployment, or you can add your AIF Web Service to existing Service Group and redeploy it.

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, Web Service, Add Data.

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.

Dynamics AX 2012 Sales Agreements Import using Excel Add-in

Microsoft Dynamics AX 2012 Excel Add-in – Sales agreements Import

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

Challenge: Data model changes in Microsoft Dynamics related to high normalization and introduction of surrogate keys made some imports more complex. Moreover Sales Blanket orders functionality was completely redesigned and in Microsoft Dynamics AX 2012 it’s called Sales Agreements now.

Solution: Microsoft Dynamics AX 2012 ships with a number of AIF Web Service which can be used in integration scenarios. However there’s no AIF Web Service out-of-the-box that can be used in Excel at the moment for import of sales agreements. In order to import a sales agreement using Excel the appropriate tables will be used.

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

Data Model:

Table Name	Table Description
AgreementClassification	The AgreementClassification table contains the agreement classifications.
AgreementClassificationTranslation	The AgreementClassificationTranslation table contains the translations for the agreement classifications.
AgreementHeader	The AgreementHeader table contains each of the purchase and sales agreements.
AgreementHeaderDefault	The AgreementHeaderDefault table contains the release order defaulting policies.
AgreementHeaderDefaultHistory	The AgreementHeaderDefaultHistory table contains a snapshot of the release order defaulting policies.
AgreementHeaderHistory	The AgreementHeaderHistory table is a base table for the sales and purchase agreement header history tables.
AgreementLine	The AgreementLine table stores information about the purchase agreement details.
AgreementLineDefault	The AgreementLineDefault table contains the line release order defaulting policies.
AgreementLineDefaultHistory	The AgreementLineDefaultHistory table contains a snapshot of line release order defaulting policies.
AgreementLineHistory	The AgreementLineHistory table contains a snapshot of the agreement lines.
AgreementLineQuantityCommitment	The AgreementLineQuantityCommitment table contains each of the agreement lines of the quantity type.
AgreementLineQuantityCommitmentHistory	The AgreementLineQuantityCommitmentHistory table contains a snapshot of agreement quantity lines.
AgreementLineReference	The AgreementLineReference table contains the relation between intercompany sales agreement lines and intercompany purchase agreement lines.
AgreementLineReleasedLine	The AgreementLineReleasedLine table contains released agreement lines.
AgreementLineReleasedLineHistory	The AgreementLineReleasedLineHistory table contains a snapshot of released agreement lines.
AgreementLineVolumeCommitment	The AgreementLineVolumeCommitment table contains each of the agreement lines of the volume type.
AgreementLineVolumeCommitmentHistory	The AgreementLineVolumeCommitmentHistory table contains a snapshot of agreement volume lines.
AgreementReference	The AgreementReference table contains the relation between intercompany sales agreement headers and intercompany purchase agreement headers.
AgreementReleaseHeaderMatch	The AgreementReleaseHeaderMatch table contains matchings between agreements and orders.
SalesAgreementHeader	The SalesAgreementHeader table contains all of the sales agreement header specific information.
SalesAgreementHeaderDefault	The SalesAgreementHeaderDefault table contains the release sales order defaulting policies.
SalesAgreementHeaderDefaultHistory	The SalesAgreementHeaderDefaultHistory table contains a snapshot of release sales order defaulting policies.
SalesAgreementHeaderHistory	The SalesAgreementHeaderHistory table contains a snapshot of sales agreement headers.
SalesTable	The SalesTable table contains all sales order headers regardless of whether they have been posted.
SalesLine	The SalesLine table contains all sales order lines regardless of whether they have been posted.

Data Model Diagram:

Agreements

Red area highlights tables forming Agreements – Headers data model

Green area highlights tables forming Agreements – Lines data model

Yellow area highlights tables forming Sales Agreements data model

Release orders

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

Walkthrough:

Connection

Add Tables

Dynamics AX Error

Solution:

- Add replacement key to AgreementHeader table

I decided to re-use existing DocumentTitle field as Replacement key without introduction of brand new field (Temporary key) and regeneration of AIF Proxy classes here. That’s why I re-populated DocumentTitle field for all existing records using RecID and assigned Index1 = {DocumentTitle} as Replacement key to AgreementHeader table.

Field Chooser

SalesAgreementHeader

Field Name	Field Description
	Agreement classification.AgreementRelationType
	Agreement classification.Name
	Currency
	Customer account
	InstanceRelationType
	Sales agreement ID
	IsDeleted
	Customer account.CustomerDataAreaId
	Document title
	Legal entities.DataArea
	Legal entities.Name
	Party ID
	Legal entities.Party ID
	Legal entities.Party type

AgreementLine

Field Name	Field Description
	Dimension No..InventDimDataAreaId
	Dimension No.
	Item number
	Item number.ItemDataAreaId
	Agreement header record ID.InstanceRelationType
	Agreement header record ID.Document title
	Effective date
	Expiration date
	InstanceRelationType
	Line number
	Quantity
	Unit
	Unit price
	IsDeleted

InventDim

Excel VLookup function may be used to find appropriate InventDimId automatically based on criteria

Sequence:

1. SalesAgreementHeader - Publish Selected

2. AgreementLine – Publish Selected

Result:

Dynamics AX – Sales Agreement

Dynamics AX – Release Order

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 sales agreements into Microsoft Dynamics AX 2012. Although there’s no AIF Web Service out-of-the-box that can be used at the moment in Excel for import of sales agreements, appropriate tables can be used instead. Excel template can be created and used for import of sales agreements.

Author: Alex Anikiev, PhD, MCP

Dynamics AX 2012 Purchase Agreements Import using Excel Add-in

Microsoft Dynamics AX 2012 Excel Add-in – Purchase agreements Import

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

Challenge: Data model changes in Microsoft Dynamics related to high normalization and introduction of surrogate keys made some imports more complex. Moreover Purchase Blanket orders functionality was completely redesigned and in Microsoft Dynamics AX 2012 it’s called Purchase Agreements now.

Solution: Microsoft Dynamics AX 2012 ships with a number of AIF Web Service which can be used in integration scenarios. However there’s no AIF Web Service out-of-the-box that can be used in Excel at the moment for import of purchase agreements. In order to import a purchase agreement using Excel the appropriate tables will be used.

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

Data Model:

Table Name	Table Description
AgreementClassification	The AgreementClassification table contains the agreement classifications.
AgreementClassificationTranslation	The AgreementClassificationTranslation table contains the translations for the agreement classifications.
AgreementHeader	The AgreementHeader table contains each of the purchase and sales agreements.
AgreementHeaderDefault	The AgreementHeaderDefault table contains the release order defaulting policies.
AgreementHeaderDefaultHistory	The AgreementHeaderDefaultHistory table contains a snapshot of the release order defaulting policies.
AgreementHeaderHistory	The AgreementHeaderHistory table is a base table for the sales and purchase agreement header history tables.
AgreementLine	The AgreementLine table stores information about the purchase agreement details.
AgreementLineDefault	The AgreementLineDefault table contains the line release order defaulting policies.
AgreementLineDefaultHistory	The AgreementLineDefaultHistory table contains a snapshot of line release order defaulting policies.
AgreementLineHistory	The AgreementLineHistory table contains a snapshot of the agreement lines.
AgreementLineQuantityCommitment	The AgreementLineQuantityCommitment table contains each of the agreement lines of the quantity type.
AgreementLineQuantityCommitmentHistory	The AgreementLineQuantityCommitmentHistory table contains a snapshot of agreement quantity lines.
AgreementLineReference	The AgreementLineReference table contains the relation between intercompany sales agreement lines and intercompany purchase agreement lines.
AgreementLineReleasedLine	The AgreementLineReleasedLine table contains released agreement lines.
AgreementLineReleasedLineHistory	The AgreementLineReleasedLineHistory table contains a snapshot of released agreement lines.
AgreementLineVolumeCommitment	The AgreementLineVolumeCommitment table contains each of the agreement lines of the volume type.
AgreementLineVolumeCommitmentHistory	The AgreementLineVolumeCommitmentHistory table contains a snapshot of agreement volume lines.
AgreementReference	The AgreementReference table contains the relation between intercompany sales agreement headers and intercompany purchase agreement headers.
AgreementReleaseHeaderMatch	The AgreementReleaseHeaderMatch table contains matchings between agreements and orders.
PurchAgreementHeader	The PurchAgreementHeader table contains purchase agreement headers.
PurchAgreementHeaderDefault	The PurchAgreementHeaderDefault table contains the release purchase order defaulting policies.
PurchAgreementHeaderDefaultHistory	The PurchAgreementHeaderDefaultHistory table contains a snapshot of the release purchase order defaulting policies.
PurchAgreementHeaderHistory	The PurchAgreementHeaderHistory table contains a snapshot of the purchase agreement header record.
PurchTable	The PurchTable table contains all the purchase order headers regardless of whether they have been posted.
PurchLine	The PurchLine table contains all purchase order lines regardless whether they have been posted or not.

Data Model Diagram:

Agreements

Red area highlights tables forming Agreements – Headers data model

Green area highlights tables forming Agreements – Lines data model

Blue area highlights tables forming Purchase Agreements data model

Release orders

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

Walkthrough:

Connection

Add Tables

Dynamics AX Error

Solution:

- Add replacement key to AgreementHeader table

I decided to re-use existing DocumentTitle field as Replacement key without introduction of brand new field (Temporary key) and regeneration of AIF Proxy classes here. That’s why I re-populated DocumentTitle field for all existing records using RecID and assigned Index1 = {DocumentTitle} as Replacement key to AgreementHeader table.

Field Chooser

PurchAgreementHeader

Field Name	Field Description
	Agreement classification.AgreementRelationType
	Agreement classification.Name
	Currency
	Vendor account
	InstanceRelationType
	Purchase agreement ID
	IsDeleted
	Vendor account.VendorDataAreaId
	Document title
	Legal entities.DataArea
	Legal entities.Name
	Party ID
	Legal entities.Party ID
	Legal entities.Party type

AgreementLine

Field Name	Field Description
	Dimension No..InventDimDataAreaId
	Dimension No.
	Item number
	Item number.ItemDataAreaId
	Agreement header record ID.InstanceRelationType
	Agreement header record ID.Document title
	Effective date
	Expiration date
	InstanceRelationType
	Line number
	Quantity
	Unit
	Unit price
	IsDeleted

InventDim

Excel VLookup function may be used to find appropriate InventDimId automatically based on criteria

Sequence:

1. PurchAgreementHeader - Publish Selected

2. AgreementLine – Publish Selected

Result:

Dynamics AX – Purchase Agreement

Dynamics AX – Release Order

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 purchase agreements into Microsoft Dynamics AX 2012. Although there’s no AIF Web Service out-of-the-box that can be used at the moment in Excel for import of purchase agreements, appropriate tables can be used instead. Excel template can be created and used for import of purchase agreements.

Author: Alex Anikiev, PhD, MCP