Dynamics AX 2012 Vendor transactions/balances Import using Excel Add-in

Dynamics AX 2012 Excel Add-in – Vendor transactions/balances Import

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

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. However some information which is usually automatically generated in Microsoft Dynamics AX 2012 Rich Client by means of number sequences such as voucher number will have to be provided. Additionally with introduction of segmented control to specify multi-type account on General Journal line it became more challenging to work with customer and vendor transactions using Excel.

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 vendor open transactions. In order to import vendor open transactions using Excel the mentioned AIF Web Service will be used. Some additional work will be done after initial import to align vendor account.

Assumption: The assumption is that appropriate reference data such as vendors, 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:

Connection

Add Data

Field Chooser

Accounting structure

LedgerJournalTable

Field Name	Field Description
	Journal batch number
	Name
	Description

LedgerJournalTrans

Field Name	Field Description
	Journal batch number
	Voucher
	Date
	Company accounts
	Account type
	DefaultDimension.Department
	Description
	Debit
	Credit
	Currency
	Offset company accounts
	Offset account type
	OffsetLedgerDimension
	OffsetLedgerDimension.Main Account
	OffsetLedgerDimension.Department
	Offset-transaction text

1)

Please note that Description field contains a valid Vendor number in there. This is because currently in Excel there’s no means to specify Vendor account using LedgerDimension field and as a workaround Vendor account information will be first brought to AX and then special X++ Job will be executed to populate correct LedgerDimension field on General Journal line based on Vendor account information provided as a part of Description field. Description field was chosen for these purposes because it is free form text field and it is exposed to Excel via Web Service.

You can also incorporate Vendor number into Description like “[Vendor number] Description” and then use regular expression in X++ Job to extract Vendor number itself, and using X++ string functions you can also adjust Description to be “Description” only.

Import XPO

Execute X++ Job

Sequence:

1. Publish All

2. Execute X++ Job

Result:

Dynamics AX – General Journal

Dynamics AX – Execute X++ Job

Dynamics AX – General Journal lines

Dynamics AX – General Ledger Posting

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 vendor open transactions into Microsoft Dynamics AX 2012. General Journal AIF Web Service (LedgerGeneralJournalService) can be used in Excel for import of vendor open transactions. Excel template can be created and used for import of vendor open transactions.

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, Vendor 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. It’s recommended that all Data Model changes introduced as a part of this demonstration will be removed once you complete data import exercise.