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:
Walkthrough:
Connection
Add Data
Dynamics AX – Error
Add Tables
Field Chooser
InventJournalTable
| Field Name | Field Description |
| | Journal |
| | Name |
| | Description |
InventJournalTrans
| Field Name | Field Description |
| | Date |
| | Item number |
| | Journal |
| | Line number |
| | Dimension No |
| | Quantity |
| | Cost price |
| | Cost amount |
InventDim
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
Dynamics AX – Inventory Journal lines
Dynamics AX – On-hand
Dynamics AX – 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 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.
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.
ReplyDeleteI 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
Hi Tim!
DeleteThank 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
Tim,
DeleteI 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
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.
ReplyDeleteHi Johan!
DeletePlease 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
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?
ReplyDeleteHi Michele!
DeleteWhen 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
Hi Michele!
DeletePlease 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
Alex, great blog!! I have learned alot from it. When following the instructions for this example I get the following:
ReplyDeleteThe number sequence Inve_70 does not allow the Journal to be defined.
Creation has been cancele
What is causing this error? Thanks.
Hi Andrew!
DeletePlease 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
Alex,
DeleteThanks 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
Hi Andrew!
DeleteIn 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
Hi Alex,
ReplyDeleteThanks 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
Hi Alex.
ReplyDeleteGreat 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.