Using AX2012 Document Management Services for GL Journal Imports

Data Import , Document Management , Dynamics AX , General Ledger Add comments

Author Steve Boccio

Microsoft Certified Business Intelligence Developer
Microsoft Certified Technology Specialist More ...

I’d like to share an interesting case I encountered when using the new AX2012 Document Management Services to provide an interface for importing GL entries for my fellow application consultants.  What seemed like a simple concept proved to be a bit more challenging than anyone could have expected.

Our financial application consultants needed a way for their end-users to import GL entries into AX2012R2. Historically we would either develop something specific to allow a client import records, or we would suggest a product like Atlas XL. We thought it would be fairly straight-forward to use the new LedgerGeneralJournalService provided by Microsoft natively in AX2012 to do this type of import.  We took the steps to add and activate the service into the Document data sources. (Feel free to email me for these steps if you need them). Then we made sure we had the Dynamics AX Office Excel Add in and its prerequisites installed on the AX client where the import would take place. We open Excel and on a new sheet, we click the Dynamics AX tab and set our Configure Options to the AX environment of choice (screen shot below) and click OK.

Once the connection is made, we click on Add Data and found that our new document service was advertised in the dialog as expected.

Once General Ledger is selected and you click OK, you may have a little time on your hands. Be thankful you’ve been informed of this. The first few times we thought something might be wrong with the client, Excel Add in, or the service, however patience prevails. We’ve seen the spreadsheet take anywhere from 10 minutes to half an hour to come back with the fields menu. Once it does though, we were off and running with our field additions for import.

The following steps are what we took to eventually get to a service-enabled spreadsheet, using the document services to import records of choice into the LedgerJournalTable and LedgerJournalTrans tables in AX2012R2.

1. Click the Fields button.
2. From the header table, drag the following fields onto the worksheet:  
- Journal Batch Number
- Description
- Name
- RecID
3. From the lines table, drag the following fields onto the worksheet:
- Journal Batch Number
- Line number.Date
- RecID (will come over with Line Number.Date)
- Company Accounts
- Line Number. Account Type
- Description
- LedgerDimension.MainAccount
- Debit
- Credit
- Currency
4. Close the Fields button.
5. Click Refresh.  You should see a large amount of data
6. Go to the next empty line under the header fields, and put in the data you wish to import:
- Journal batch number:  000100_010
- Description: General Journal
- Name: GenJrn
- RecID: 8675309    <- enter a random value and the service will populate it properly for you.
7. Go to the first blank line after the lines data, and put in the following data:
- Journal batch number: 000100_010
-  Line number.Date: 4/2/2013
-  RecId:  90210          <- enter a random value and the service will import it properly.  
-  Company accounts:  ceu
-  Line Number.Account type:  Ledger
-  Description: leave blank
-  LedgerDimenion.MainAccount: 403120
-  Debit:  150
-  Credit: 0
-  Currency:  USD
8. Click the Publish button.
9. Go to the Dynamics AX Status tab in workbook.  Total Records imported should be 1, and records published was 1.
If you click refresh, you will NOT see the new record in the spreadsheet.
 
Confirm the Import: 
OPEN DYNAMICS AX 2012 R2 client:
1. General Ledger >> Journals >> General Journal.  You should see the newly created journal
2. Click the Lines button.  Notice this is the newly created line.  Information now shows as desired.
 

With this step by step procedure, life will be easier for you than it was for us. The first one through the trenches always gets the muddiest, I guess.

Ready for the kicker?  You won’t get this far without the following in place:

Make sure that you are on AT LEAST kernel build 6.2.1000.43.  Microsoft released a ‘stabilization fix’ for the Office Add-ins.  If you don’t have this installed, you won’t see the RecId columns in both the header and line table and the import will fail without error or reason because it cannot insert the records without the proper RecID fields.  Using the above procedure (modified for your data and needs of course) you should be able to import the records entered into the Add In into AX.  The above steps do work quite well in AX 2012 R2 with an updated kernel.

We were originally on AX2012 RTM and experienced the failure without error or reason.

Author Steve Boccio

Microsoft Certified Business Intelligence Developer
Microsoft Certified Technology Specialist More ...


Microsoft Dynamics AX is a robust ERP and Lean manufacturing enabler. The latest release, Dynamics AX 2012, combines the powerful planning and execution features required of comprehensive ERP integrated with the features of Lean to assist the "blended" manufacturer. Contact Agility Business Solutions, Inc. to learn more.

9 response s to “Using AX2012 Document Management Services for GL Journal Imports ”

  1. Faheem Ahmad Usmani Says:
    Dear Steve,

    Thanks for such a nice work on importing data through Excel Add Ins., Please send me that procedure by which I can create or add Document Service for JOURNAL LINES because in Add Data option I don't find it.


    Regards,
    Faheem Ahmad
  2. Karen Says:
    May I please have the steps to add and activate the service into the Document data sources that you referenced: (Feel free to email me for these steps if you need them)
    Thank you!
  3. Andy B Says:
    Many thanks for the post, looks like just what I've been looking for. Can I also request the steps for creating/activating the service into the document data sources.

    Many thanks..
  4. Steve Says:
    Thanks for the feedback. I believe I have responded to everyone who left a request for me. Let me know if I missed your response.

    Thanks,
    Steve
  5. Ralph Says:
    Can you please send me the steps for creating/activating the service into the document data sources?

    Thank you.
  6. Sonali Says:
    can I have steps to create /Activating the Service into the Document Data sources? Right Now I have activated the LedgerGeneralJournalService and AXdLedgergeneralJournal Query reference in the Document Datasource .But still I cannot publish through Excel Addins.
  7. Rakesh Says:
    Hi,

    Can you please send me the steps on how to get the general doata source..

    Thanks,
    Rakesh
  8. Lori Says:
    Hello Steve,
    Thank you for your great post. Could you please send me the procedure for setting up and activating the Document Data source. We have already done this, but it is still not working. I have tried setting up as you describe but I cannot get it to work. It runs but nothing is uploaded and no errors are detected. Do you have any other ideas? Our kernel version is 6.3.164.0, so we should have the excel add-in fix. Thank you for your help and sharing your knowledge!
  9. Andre Says:
    Hi
    I did had in the document date source the service for budget and GL journal, but for some reason I dont see them in the Excel side.
    I just see the data source wiht Query type.
    Can you send me your information.
    Or any idea why?
    Thanks
    Andre

Leave a Reply

Leave this field empty:

Powered by Mango Blog. Design and Icons by N.Design Studio