Dynamics AX 2012 Excel Add-in – Issues and Solutions (Cannot have 0 columns)
Purpose: The purpose of this document is to outline some issues you may encounter when using Dynamics AX 2012 Excel Add-in for import of data and provide solutions to resolve them.
Issue: 'Cannot have 0 columns'.
Reason: This issue occurs because some tables in Microsoft Dynamics AX 2012 don't have any fields for specific functional purpose or for the purposes of table inheritance just being another 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 the table.
Dynamics AX Error
Solution: Please create fictitious (fake) field(s) in the tables related to the main table which have no columns. For example, you may encounter 'Cannot have 0 columns' error when doing Purchase orders data import because Tax1099BoxDetail table and PurchCommitmentLine_PSN table have no columns and they are linked to PurchLine table by Surrogate key. In order to resolve this issue I'll add fake 'ID' field (String) to Tax1099BoxDetail table and PurchCommitmentLine_PSN table so each table will now have at least one field.
PurchLine table is linked to Tax1099BoxDetail table by Surrogate key
PurchLine table is linked to PurchCommitmentLine_PSN table by Surrogate key
You can find all tables related to PurchLine table by looking at Relations node at table level as depicted above, or at EDT (Extended data type) level for EDTs used on PurchLine table fields.
When you try to add PurchTable table and PurchLine table to Excel Add-in you will see the following error
In order to resolve this you will have to add Replacement keys to the following tables
ReasonTableRef table Replacement key
AgreementHeader table Replacement key
SourceDocumentHeader table Replacement key
SourceDocumentLine table Replacement key
When you will try to add PurchTable table and PurchLine table again you will see the following error
In order to resolve this you will have to add Replacement keys to the following tables
Tax1099BoxDetail table Replacement key
Important: Please note that Tax1099BoxDetail table has no fields
PurchCommitmentLine_PSN table Replacement key
Important: Please note that PurchCommitmentLine_PSN table has no fields
When you try to Publish selected for PurchLine table you will see the following error
This is because Tax1099DetailBox table and PurchCommitmentLine_PSN table have no columns and they are linked to PurchLine table by Surrogate key
In order to resolve this you will have to add fictitious (fake) field(s) to Tax1099DetailBox table and PurchCommitmentLine_PSN table so they have at least one field. In this case I added 'ID' field (String) to Tax1099DetailBox table and PurchCommitmentLine_PSN table. I didn't plan to introduce any data into Tax1099DetailBox table and PurchCommitmentLine_PSN table, so I also added 'ID' field to the Replacement key with the assumption that this key will be unique.
Tax1099DetailBox table new 'ID' field (String)
PurchCommitmentLine_PSN table new 'ID' field (String)
Now if you do Publish selected for PurchLine table you will have expected result
You can apply the same logic when resolving this error for different type of data import using Excel Add-in
It's recommended to remove these data model modifications once you complete data import activities
Please make sure to refresh Caches after you change the data model and restart Excel before publishing data. It's recommended to restart AOS to make sure that the latest data model changes will be taken into account in Excel Add-in
Tags: Dynamics ERP, Dynamics AX 2012, Excel, Dynamics AX 2012 Excel Add-in, Data Import, Data Conversion, Data Migration, Application Integration Framework, Issue, Problem, Solution, Resolution.
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 issues and describe the solutions.
Author: Alex Anikiev, PhD, MCP
Hi Alex. I try to upload Demand Forecast (ForecastSales table) using Excel add-in. At first I found 2 error about replacement key on ForecastSales and PurchLine. I created replacement key for PurchLine with fields InventTransID and RecID, while for ForecastSales I create replacement key with fields ExpandId and RecID. Then Excel successfully retrieve data from AX. But when I try to publish, It raise an error "cannot have 0 columns". I search ForecastSales's relation and find that DimensionAttributeValueSet has no field except RecID and DataAreaID. So I added dummy field as what you suggest in this post. But still I can't publish, error "cannot have 0 columns" still exist. can you help me what the problem is? Thanks Alex.
ReplyDeleteI am trying to import the Purchtable and am facing the same error. Could you please provide the tables which have 0 columns?
ReplyDeleteThanks,
Aamir