Tuesday, January 17, 2012

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:

image

Walkthrough:

Connection

image

Add Data

image

Error

image

Solution

- Add replacement key to CustInvoiceTable table

- Add replacement key to CustInvoiceLine table

Error

image

image

Data Model Changes

image

Temporary Key (Import ID)

image

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.

image

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

image

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).

image

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.

image

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

Generate Incremental CIL

image

Add Tables

image

Field Chooser

image

CustInvoiceTable

Field Name

Field Description

Currency

Customer account

Date

Group

Import ID

Invoice account

Language

image

CustInvoiceLine

Field Name

Field Description

Customer free text invoice.Import ID

Description

LedgerDimension.Main account

Quantity

Unit price

Amount

image

Sequence:

1. CustInvoiceTable - Publish Selected

2. CustInvoiceLine – Publish Selected

Result:

Dynamics AX – Free text invoice

image

Dynamics AX – Free text invoice (Invoice)

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

No comments:

Post a Comment