Dynamics AX 2012 Free text invoices Import using Excel Add-in

Dynamics AX 2012 Excel Add-in – Free text invoices Import

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

Challenge: Data model changes in Microsoft Dynamics related to high normalization and introduction of surrogate keys made some imports more complex. The data model forming Free text invoices was not dramatically changed and import principle remains the same – populate the header and related lines. In fact the link between the header and lines in build based on RecID which adds complexity.

Solution: Microsoft Dynamics AX 2012 ships with Free Text Invoice AIF Web Service (CustFreeTextInvoiceService) which can be used in integration scenarios. However Free Text Invoice AIF Web Service can’t be used in Excel at the moment for import of free text invoices. In order to import a free text invoices 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
CustInvoiceTable	The CustInvoiceTable table contains free text invoices.
CustInvoiceLine	The CustInvoiceLine table contains line items for an accounts receivable free text invoice.

Data Model Diagram:

Walkthrough:

Connection

Add Data

Error

Solution

- Add replacement key to CustInvoiceTable table

- Add replacement key to CustInvoiceLine table

Error

Data Model Changes

Temporary Key (Import ID)

ImportID is custom String field that is introduced to overcome the problem with Surrogate key based relations between tables in Excel add-in. Index1 is as I call it “Temporary Key” used during data conversion (data migration) process only. This Temporary key will be assigned to the header table as Non Surrogate Replacement key.

Lines table will still have relation to header table based on Surrogate key.

But because header table has Natural Replacement key Excel add-in will be able to translate Surrogate key based relation into Natural Key relation as design time (data entry) and vice versa, Natural Key relation to Surrogate key relation, during run time (publishing).

Proxy AIF classes

Table	Class
CustInvoiceTable	AxCustInvoiceTable
CustInvoiceLine	AxCustInvoiceLine

In order to regenerate Proxy AIF classes involved into Free text invoices import I used AIF Free Text Invoice Web Service (CustFreeTextInvoiceService) Update document service function available through Tools > AIF > Update document service in Development Workspace.

As the result appropriate AIF Proxy classes will be updated or created to support the translation ImportID - RecID.

Generate Incremental CIL

Add Tables

Field Chooser

CustInvoiceTable

Field Name	Field Description
	Currency
	Customer account
	Date
	Group
	Import ID
	Invoice account
	Language

CustInvoiceLine

Field Name	Field Description
	Customer free text invoice.Import ID
	Description
	LedgerDimension.Main account
	Quantity
	Unit price
	Amount

Sequence:

1. CustInvoiceTable - Publish Selected

2. CustInvoiceLine – Publish Selected

Result:

Dynamics AX – Free text invoice

Dynamics AX – Free text invoice (Invoice)

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

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, Free text invoices.

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.