Tuesday, January 17, 2012

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

Dynamics AX 2012 Excel Add-in – Customer 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 customer 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 customer open transactions. In order to import customer open transactions using Excel the mentioned AIF Web Service will be used. 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:

image

Walkthrough:

Connection

image

Add Data

image

Field Chooser

image

Accounting structure

image

LedgerJournalTable

Field Name

Field Description

Journal batch number

Name

Description

image

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

image

2) image

Please note that Description field contains a valid Customer number in there. This is because currently in Excel there’s no means to specify Customer account using LedgerDimension field and as a workaround Customer 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 Customer 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 Customer number into Description like “[Customer number] Description” and then use regular expression in X++ Job to extract Customer number itself, and using X++ string functions you can also adjust Description to be “Description” only.

image

Import XPO

image

Execute X++ Job

image

Sequence:

1. Publish All

2. Execute X++ Job

Result:

Dynamics AX – General Journal

image

Dynamics AX – Execute X++ Job

image

Dynamics AX – General Journal lines

image

Dynamics AX – General Ledger Posting

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 customer open transactions into Microsoft Dynamics AX 2012. General Journal AIF Web Service (LedgerGeneralJournalService) can be used in Excel for import of customer open transactions. Excel template can be created and used for import of customer 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, 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. It’s recommended that all Data Model changes introduced as a part of this demonstration will be removed once you complete data import exercise.

10 comments:

  1. If offset Account Type is bank, how we will select Bank Accounts?

    We need to specify Bank Account instead of Main Account, Is it possible?

    Thanks

    Regards
    Uday

    ReplyDelete
  2. Hi Alex,

    The class DimensionConversionHelper is not found in my AOT, is there something wrong in my AX 2012?

    Please give me advice.
    Regards,
    Trung

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. DimensionConversionHelper Class is a part of "Foundation Upgrade" Model and Version 6.0.947.0

    Below are the Model Details.

    Name = "Foundation Upgrade"
    DisplayName = "Upgrade"
    Description = "Upgrade model for upgrading from Microsoft Dynamics AX 2009 or Microsoft Dynamics AX 4.0 to Microsoft Dynamics AX 2012."
    Publisher = "Microsoft Corporation"
    Signed = 1
    VersionMajor = 6
    VersionMinor = 0
    VersionRevision = 0
    VersionBuildNo = 947
    Category = 1

    regards,
    Chaitan

    ReplyDelete
  5. what if i don't have the DimensionConversionHelper class? any other class i can use?

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. When i Published the data it throws following error.

    The following error was returned: The element 'LedgerJournalTable' in namespace 'http://schemas.microsoft.com/dynamics/2008/01/documents/LedgerGeneralJournal' has invalid child element 'JournalNum' in namespace 'http://schemas.microsoft.com/dynamics/2008/01/documents/LedgerGeneralJournal'. List of possible elements expected: 'JournalName' in namespace 'http://schemas.microsoft.com/dynamics/2008/01/documents/LedgerGeneralJournal'.Invalid document schema.

    ReplyDelete
  8. for my case, i have done all add-in method, but when i published the add-ins, my debit/credit amount is not reflected in the journal lines.
    kindly give your inputs.
    reg
    prasad
    prasathr29@hotmail.com
    9884630280

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. is it possible to import multiple lines for the same customer as I'm importing the opening balace with transaction details?

    ReplyDelete