Tuesday, January 17, 2012

Dynamics AX 2012 Inventory transactions/on-hand Import using Excel Add-in

Dynamics AX 2012 Excel Add-in – Inventory transactions/on-hand Import



Purpose: The purpose of this document is to illustrate how to use Microsoft Dynamics AX 2012 Excel Add-in for import of inventory transactions (on-hand).

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 Inventory Journal was not dramatically changed and import principle remains the same – populate the journal and then post the journal. However surrogate key link was introduced to some tables related to Inventory Journal transactions table.

Solution: Microsoft Dynamics AX 2012 ships with lnventory Journal AIF Web Service (InventProfitLossJournalService) which can be used in integration scenarios. However Inventory Journal AIF Web Service can’t be used in Excel at the moment for import of inventory transactions. In order to import inventory transactions using Excel the appropriate tables will be used.

Assumption: The assumption is that appropriate reference data such as released products (items), etc. was created in advance.

Data Model:

Table Name

Table Description

InventJournalTable

The InventJournalTable table contains information about inventory journals. The table holds information about how the journal is handled by the system.

InventJournalTrans

The InventJournalTrans table contains information about items and represents a line in an inventory journal. Each record has information related to a specific item and is related to a record in the InventJournalTable table, which is the journal header.

Data Model Diagram:

image

Walkthrough:

Connection

image

Add Data

image

Dynamics AX – Error

image

Add Tables

image

Field Chooser

image

InventJournalTable

Field Name

Field Description

Journal

Name

Description

image

InventJournalTrans

Field Name

Field Description

Date

Item number

Journal

Line number

Dimension No

Quantity

Cost price

Cost amount

image

InventDim

image

Excel VLookup function may be used to find appropriate InventDimId automatically based on criteria

Sequence:

1. InventJournalTable – Publish Selected

2. InventJournalTrans – Publish Selected

Result:

Dynamics AX – Inventory Journal

image

Dynamics AX – Inventory Journal lines

image

Dynamics AX – On-hand

image

Dynamics AX – 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 inventory transactions into Microsoft Dynamics AX 2012. Although Inventory Journal AIF Web Service (InventProfitLossJournalService) can’t be used at the moment in Excel for import of inventory transactions, appropriate tables can be used instead. Excel template can be created and used for import of inventory 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, Inventory on-hand.

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.

14 comments:

  1. Hi Alex, Great post (great series of posts actually). However when I try to add InventJournalTrans as an Excel data source I see: “The table [InventJournalTrans] surrogate foreign key field [ReasonRefRecId] related table [ReasonTableRef] does not have a replacement key” I took a look at that table and it seems to be a financial reason code. The field isn’t visible or used on an Inventory journal, so I guess it’s used with fixed assets or some such. Anyway, the table ReasonTableRef has only got two fields (Reason and Description). I created an Alternate index using RecId – that’s the only unique field on the table – and set that index as the replacement key, and that allowed me to add InventJournalTrans as a data source.
    I can get Excel to refresh the spreadsheet and show me inventory journal lines which I have keyed into Dynamics AX, but when I try to publish inventory journal lines I get the error “Cannot have 0 columns”. Any ideas on how this can be fixed?
    Thanks
    Tim Schofield

    ReplyDelete
    Replies
    1. Hi Tim!

      Thank you for the interest to this series!

      In order to successfully import Inventory transactions/on-hand using Inventory journal please add the index to ReasonTableRef table which is based on 2 fields {Reason, RecId}. In this case your Replacement key will definitely be unique because of RecId and non-Surrogate because of non-RecId field (Reason)

      I also created additional document for your clarify with more detailed content, it's available here: https://docs.google.com/open?id=0B3rbAZy5q2ExNGEyYmMyYjQtYTBhNC00YjgxLWFiYzYtNGJmZTkwM2FhZGE5

      Please let me know if you have any questions

      Best Regards,
      /Alex

      Delete
    2. Tim,
      I also created blog post dedicated to Replacement key Options you have in general. It's available here: http://ax2012exceldataimport.blogspot.com/2012/01/dynamics-ax-2012-excel-add-in.html

      Best Regards,
      /Alex

      Delete
  2. Hi Alex, thanks for a great post! I'm trying to make this work, but when I create a new index for ReasonTable, I cannot choose that index as replacement key for ReasonTableRef. Are there some prerequisites that I need to fix first? The system already has an index on ReasonTable, and I have both tried to add recid to the existing index, and create a new as you mention in your document, but with the same result.

    ReplyDelete
    Replies
    1. Hi Johan!

      Please create new index 'Index1', add fields to it (please see options for Replacement keys in the dedicated post, the easiest is to add some String field + RecID) and then change the following properties for the index itself:
      1. Allow Duplicates = No
      2. Alternate key = Yes

      After that you will be able to select index 'Index1' as Replacement key on Table level in properties
      Please see detailed exaplanation in Replacement Keys post

      PS. For data import purposes it's better to create brand new index (rather than modifying existing one) and then delete it once you finish data import activities

      Best Regards,
      /Alex

      Delete
  3. Hi Alex, I was able to add the Replacement Key but I continue to get the same error that Tim mentions stating "Cannot have 0 columns". I can't find any work around to this. Can you elaborate further on how you got around this?

    ReplyDelete
    Replies
    1. Hi Michele!

      When you add Tables in Excel Add-in the system goes through the relations between main table(s) and other tables, and is trying to create a complete data set based on these relations. That's why you see 'Surrogate key expansion' errors for the tables linked to the main table based on RecId if there's no Replacement key specified on the related table.

      Please note that some tables in AX don't have any fields for specific functional purpose or purposes of table inheritance (just being a logical level in the hierarchy). In the case if such table with no columns will be related to the main table you will see 'Cannot have 0 columns' error even despite the fact that Replacement key may be specified on that table (as RecId which always exists).

      Example would be Purchase orders import (PurchTable, PurchLine). There are 2 tables related to PurchLine table which have 0 columns: PurchCommitmentLine_PSN table and Tax1099BoxDetail table. And even if you specify Replacement index for both of them containing RecId field, you will still see 'Cannot have 0 columns' error when you try to publish. In order to resolve 'Cannot have 0 columns' error please add fictitios (fake) column, say 'ID' to PurchCommitmentLine_PSN table and Tax1099BoxDetail table. After that you can also add 'ID' field into the index and make it Replacement index on these related tables. Most probably you will not have any data in those 2 tables so combination of {ID = '' (empty), RecId = "XYZ" (something)} will be unique. Once you do that please refresh all Caches (or restart AOS), then restart Excel and go ahead with Purchase orders data import.

      Please let me know if you have more questions

      Best Regards,
      /Alex

      Delete
    2. Hi Michele!

      Please find detailed explanation of 'Cannot have 0 columns' issue and the solution here: http://ax2012exceldataimport.blogspot.com/2012/05/dynamics-ax-2012-excel-add-in-issues.html

      Best Regards,
      /Alex

      Delete
  4. Alex, great blog!! I have learned alot from it. When following the instructions for this example I get the following:

    The number sequence Inve_70 does not allow the Journal to be defined.
    Creation has been cancele

    What is causing this error? Thanks.

    ReplyDelete
    Replies
    1. Hi Andrew!

      Please go to Inventory Management parameters, on Number Sequences tab you will find "Inve_70" Number sequence assigned to allocate Inventory Journal #'s.
      Please open "Inve_70" Number sequence and mark "Manual" checkbox being in Edit mode. By default it's Continious.
      Then you will be able to successfully import the data assigning Inventory Journal #'s explicitly.
      Once you finish with import please set it back to "Continious" (unmark "Manual" first) for the system to continue allocating Journal #'s for Inventory journals in the UI as usually.

      Best Regards,
      /Alex

      Delete
    2. Alex,

      Thanks for the reply. That solution worked great. Is there any reason why I shouldn't be able to make a document service that handles this? Much like the LedgerGeneralJournal Service. I attempted to create one using the instructions on the MS site 'Walkthrough: Creating a Service by Using the AIF Document Service', substituting InventJournalTable and InventJournalTrans where appropriate. I can't publish from Excel though. I get an error that the update was cancelled. Can you point me in the right direction? Thanks.

      Andrew

      Delete
    3. Hi Andrew!

      In fact you should be able to create a Web Service for Inventory transactions import including InventJournalTable and InventJournalTrans (and InventDim) tables and use it in Excel Add-in. InventJournalTrans is linked to InventJournalTable by JournalId (Natural Key) which is not Surrogate Key (RecId).

      If you plan to enable this Web Service for update operation please modify respective Query Data Sources "Update" Property to "Yes". In your case you will set "Update" = "Yes" for the following Data Sources in your Query used for Web Service creation:
      - InventJournalTable
      - InventJournalTrans
      If you don't do this explicitly then update operation will fail

      Best Regards,
      /Alex

      Delete
  5. Hi Alex,

    Thanks for sharing very good ideas for updating data via excel.

    But t have serious problem when trying to follow instructions above and trying to apply them in case of updating COSTING SHEET in AX2012 r2.

    Can you pls pls demostrate how to use excel dynamics excel add in and how use it for complete costing sheet setup in AX2012. Pls take account each node can be consist of severel tens rows of surcharges. Actually this is pretty painful feature at the moment in AX as it's not possible to sellect directly range of chart of accounts in costing sheet from which e.g. variable cost consist of at the same time.
    But if costing sheet setup could be doable via excel add in I'm very pls to know that.

    br Mike

    ReplyDelete
  6. Hi Alex.
    Great post!
    I have been trying to import an excel sheet on AX, containing the inventory master. I have been using excel add-in for that, but every time I do, it picks up either the wrong data format of the intracode or it doesn't pick it at all. How can I go about this?
    Thanks.

    ReplyDelete