Dynamics AX 2012 Excel Add-in - Customer transactions import

Microsoft Dynamics AX 2012 Excel Add-in – Customer transactions Import

Purpose: The purpose of this document is to illustrate how to use Microsoft Dynamics AX 2012 Excel Add-in for import of customer transactions with required level of detail.

Challenge: Data model changes in Microsoft Dynamics related to high normalization and introduction of surrogate keys made some imports more complex. In fact the data model forming General Journal was not dramatically changed and import principle remains the same – populate the journal and then post the journal. Numerous fields are available on General Journal line level in Rich client to achieve required level of detail during import. In order to import customer transactions with required level of detail using Excel Add-in some additional work may be required.

Solution: Microsoft Dynamics AX 2012 ships with General Journal AIF Web Service (LedgerGeneralJournalService) which can be used in integration scenarios. Also General Journal AIF Web Service can be used in Excel for import of customer transactions. However General Journal AIF Web Service may not expose all fields which may be required for import such as Invoice, etc. In this walkthrough in order to import customer transactions using Excel the appropriate tables will be used instead. Some additional work will be done after initial import to align customer account.

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

Data Model:

Table Name	Table Description
LedgerJournalTable	The LedgerJournalTable table contains all the defaulting and state information pertaining to a single journal. The transaction details of a journal are managed in the LedgerJournalTrans table.
LedgerJournalTrans	The LedgerJournalTrans table contains the transaction detail information that pertains to a single journal. The individual transaction lines are also referred to as voucher lines. The journal is a record in the LedgerJournalTable table.

Data Model Diagram:

Walkthrough:

AxdLedgerGeneralJournal Query

General Journal AIF Web Service doesn’t expose all fields which may be required for import such as Invoice, etc.

In order to bring Customer account information over to AX I introduced CustAccount field into LedgerJournalTrans table as shown below

CustAccount field is based on CustAccount EDT which in its turn has relation to CustTable table

After mentioned customizations will be introduced you will have to regenerate AIF Proxy classes in order to reflect changes in the data model. I used LedgerGeneralJournalService AIF Web Service because it has LedgerJournalTable and LedgerJournalTrans tables as data sources, this is exactly the tables I need AIF Proxy classes to be regenerated for.

Update document service

After you complete steps required to Update document service as it’s described in “Import Data” walkthrough you will need to generate incremental CIL.

Generate Incremental CIL

Connection

Add Tables

Dynamics AX Error

Solution:

- Add replacement key to VendTrans table {Field, RecId}

- Add replacement key to CustTrans table {Field, RecId}

- Add replacement key to LedgerJournalTrans table {Field, RecId}

- Add replacement key to ReasonTableRef table {Field, RecId}

- Add replacement key to VendBankAccount table {Field, RecId}

- Add replacement key to CustBankAccount table {Field, RecId}

- Add replacement key to BudgetSource table {Field, RecId}

- Add replacement key to BudgetTransactionLine table {Field, RecId}

Field Chooser

Accounting structure

Dynamics AX Error

When you unpress Field Chooser button you may see “The number of joins in the statement is X. This exceeds the maximum of 26. …” error

This error occurs because of numerous relations specified on LedgerJournalTrans and LedgerJournalTable tables. As opposite to AIF Web Service where the data set is strictly defined by the Query, in the case when raw tables are being used the system is building composite data set based on relations specified on tables/EDT level

In order to bypass this error you will have to change “Maximum buffer size (KB)” setting in Dynamics AX Server Configuration Utility and restart AX AOS

Dynamics AX Server Configuration Utility

LedgerJournalTable

Field Name	Field Description
	Journal batch number
	Name
	Description

LedgerJournalTrans

Field Name	Field Description
	Journal batch number
	Voucher
	Date
	Company accounts
	Account type
LedgerJournalTrans.CustAccount <custom>	Customer account
	DefaultDimension.Department
	Description
	Debit
	Credit
	Currency
LedgerJournalTrans.Invoice	Invoice
	Offset company accounts
	Offset account type
	OffsetLedgerDimension
	OffsetLedgerDimension.Main Account
	OffsetLedgerDimension.Department
	Offset-transaction text

1)

2)

3)

Please note that I added Invoice field which is not included in General Journal AIF Web Service data set by default. Also to manipulate with Customer account I added custom CustAccount field, after initial import this field will be used in X++ Job to populate LedgerDimension field in LedgerJournalTrans table based on Customer account specified. In my previous walkthroughs I used different approach which doesn’t require the customization of AX data model (the structure of LedgerJournalTrans table) when I used Description field to bring Customer account info over to AX.

When you will try to publish LedgerJournalTrans table data you will see “Method AxdBase.getDimensionId must be overridden” error

Dynamics AX Error

This error occurs because there’s no behavior defined in Generic document AIF document class (AxdGenericDocument) which is used when you work with raw tables as it relates to LedgerDimension field(s) processing.

Please note that General Journal AIF document class does have this behavior defined, that’s why it can handle LedgerDimension field(s) fine.

Actually the actual LedgerDimension field(s) handling happens during deserialization in AxdLedgerGeneralJournal class, here in getDimensionId method the values get stored for future processing.

Solution:

The next logical step is to override getDimensionId method in AxdGenericDocument class assuming that we just want to import customer transactions for now

However with this code (stub) we can bypass the error but not populate LedgerDimension field(s) values appropriately. In order to populate LedgerDimension field(s) values I modified this method to look like the following

This will take care of populating Offset account on General Journal line level properly when using raw tables. Please note that Customer account field will be populated separately and then X++ job will be used to populate appropriate LedgerDimension value which corresponds to particular Customer account.

X++ Job

Sequence:

1. LedgerJournalTable – Publish Selected

2. LedgerJournalTrans – Publish Selected

Result:

Dynamics AX – General Journal

Dynamics AX – Execute X++ Job

Dynamics AX – General Journal lines

Dynamics AX – General Ledger Posting

Alternatives:

Alternatively General Journal AIF Web Service (LedgerGeneralJournalService) Query may be modified to accommodate for more fields required for import such as Invoice, etc. This approach is much easier to implement by the user.

After you customize the Query document service must be updated using Update document service function in Developer workspace and then incremental CIL must be generated.

As an alternative to introduction of brand new field CustAccount to bring over Customer account information to AX you can make use LedgerDimension.MainAccount field.

LedgerJournalTrans

This trick will require the customization of AxdGenericDocument AIF document class in order to handle special behavior associated with LedgerDimension field(s) (LedgerDimension, OffsetLedgerDimension) as shown below

Here I assume that in Account field I specify Customer account and in Offset account field I specify Ledger account (Clearing account).

Using these ideas to facilitate import effort the same result will be achieved! The difference is in the amount of customizations introduced in order to import the data.

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 customer transactions into Microsoft Dynamics AX 2012. General Journal AIF Web Service (LedgerGeneralJournalService) can be used in Excel for import of customer transactions. However General Journal AIF Web Service (LedgerGeneralJournalService) doesn’t expose all fields which may be required for import. In this case General Journal AIF Web Service (LedgerGeneralJournalService) Query may be customized to accommodate for more fields, or raw tables may be used instead. Excel template can be created and used for import of customer transactions.

In this document raw tables approach was implemented to import customer transactions with required level of detail to demonstrate the mechanics of the process. In another similar walkthrough dedicated to import of vendor transactions with required level of detail I’ll focus on General Journal AIF Web Service modification approach which is much easier to implement for the user.

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, Customer transactions.

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. Please remove all Data Model changes introduced as a part of this demonstration once you complete data import exercise.