How to resolve data import error for Microsoft Dynamics AX 2012 using Office Add-ins. This error happens when you try to import data from excel to Dynamics AX.
Applied on:
Microsoft Dynamics AX 2012 CU2.
Symptom:
When you try to import data for LogisticsAddressCity, LogisticsAddressZipCode, LogisticsAddressState etc. you get an error “Cannot load [LogisticsAddressCity]. Tables must have a valid replacement key or a primary index other than RecID for use in Office Add-ins”.
Reason:
The main reason of this error is missing of primary index which is responsible for unique record in your table.
Solution:
Step 1: Open your AX client and go the AOT / Data Dictionary / Tables then LogisticsAddressCity table.
Step 2: Go the indexes of above mentioned table and create a new index.
Step 3: Assign fields CountryRegionId, Name and RecId to this new index. Save your work. Here CountryRegionId and Name both are mandatory fields for this table so added in primary index and additionally RecId has been added to make the record unique in the table.
Step 4: Select newly created index and go to property of the index. Set property for Allow Duplicates = No; Alternate Key = Yes
Step 5: Save your work and select table LogisticsAddresssCity. Go to property of this table and set newly created index to Replacement Key property. Save your work.
Please repeat step 2 to 5 for other tables you got error. Select mandatory fields and RecId in their index.
Step 6: Right click on these tables and synchronize them one by one.
Step 7: Select all these customized tables and generate CIL. You can use Ctrl + Shift + F7 to generate CIL for selected tables. If CIL is generated correctly you will get output messages like: Finished Pass 1 at [date & time], Finished Pass 2 at [date & time], Finished Pass 3 at [date & time].
Step 8: Once all these are done, restart your application AOS. Once the AOS is restarted, run your AX client and try to import data.
Hope this will resolve your problem.
Happy learning :)
NOTE: Use at your own risk. There is no warranty on this article.
This post was very well written,important stuff great blog to get people interested and youve done just that.
ReplyDeleteSharePoint Migration | Recruiting Software
This comment has been removed by the author.
ReplyDeleteThanks for the help.I wish you a happy new year!
ReplyDeleteGreat Post, really helpful :)
ReplyDeleteThanks a lot
Hi
ReplyDeleteI was following your instructions to import customer transaction balance and also refer to this website. http://ax2012exceldataimport.blogspot.com.au/2012/01/dynamics-ax-2012-customers.html
However, I am still having troubles on importing data. After all of the necessary settings, the ribbon of "Publish Data" just grey out. Can you help me solve it, please?
Hi Suzyn,
DeleteAfter deciding the table fields and before adding data to your your temple, click on "Field Chooser" button. Once the "Field Chooser" button is off then "Publish data" button will be enabled.
Hope will help you.
...
Rajesh Biswas
Thank Rajesh. You are brilliant. However, I am still having troubles on import customer transaction balance. First it sais that I needed to increase max Buffer size which I did. Then when I click "Publish Data", I got an error message from excel.
DeleteLedgerJournalTrans.createList Line=4, Pos=4, Xpath=/LedgerJournalTrans/LedgerJournalTrans[1] Method AxdBase.getDimensionId must be overridden.
LedgerJournalTrans.createList Line=4, Pos=4, Xpath=/LedgerJournalTrans/LedgerJournalTrans[1] Document Generic document could not be created. Error details: Method AxdBase.getDimensionId must be overridden.
What this means?
Works like a charm! By the way, there is no need to restart AOS.
ReplyDeleteThank you very much!
it works !!! thanks a lot
ReplyDeleteHi,
ReplyDeleteWhile loading the data, i am getting an error for Surrogate key in LogisticsPostalAddress table.
I would like to know how to add surrogate key for its connected tables.
Thanks,
Yash
Hi Yash,
DeleteWhen you are getting error the errors are specific to table(s). So you need to work all the tables for which you are getting error (surrogate key). To resolve this error you need to follow steps 1 to 8 mentioned above.
After doing this please let me know if it works.
Thanks,
Rajesh Biswas
Hi Yash,
DeleteJust saw your query:
Error:
The table[LogisticsPostalAddress] has surrogate foreign key expansion errors:
The table[LogisticsPostalAddress] surrogate foreign key field [CityRecId] related table [LogisticsAddressCity] does not have a replacement key.
The table[LogisticsPostalAddress] surrogate foreign key field [District] related table [LogisticsAddressDistrict] does not have a replacement key.
The table[LogisticsPostalAddress] surrogate foreign key field [ZipCodeRecId] related table [LogisticsAddressZipCode] does not have a replacement key.
Okay, 1st you need to work with "LogisticsAddressDistrict" table then "LogisticsAddressCity" and "LogisticsAddressZipCode" tables because in the reported error line no. 2,3, & 4 are talking about these three tables. Once these are fixed and you try to import data, if any table is left with error, system will let you know in the form of error.
hope this information is useful.
cheers,
Rajesh Biswas
You mentioned Reply link in this blog was not working for you. To write or reply you need to login to this blog using your gmail id.
DeleteNote: No need to be a member of this blog. Any gmail id will work.
Superb Man.... you are simply excellent....
ReplyDeleteThank you for sharing such a great and helpful post! It is much appreciated, and I am grateful for the knowledge that you have provided. Thank you once again for taking the time to create such valuable content cloud data migration!
ReplyDelete