After creating everything in a training session, we hit the an error 'Cannot load [AXTable]. Tables must have a valid replacement key or a primary index other than RecID for use in the Office Add-ins' when trying to add the table datasource in Excel.
Figure 1 - The error in Excel
It sucks that all the stuff from previous versions of AX were followed but the error above was hit. We had a unique key and everything. it just wasn't assigned on the table properties as primary. The primary key wasn't showing up in the primary key drop down like it did in previous versions. Just typing the name of the id in there if the idx doesn't show up in the drop down does not mean it will work. The name will show up in the properties but then, when clicked on, the name disappears. This is because it doesn't actually exist there. Don't be fooled. The Surrogate index won't work for this either btw. The primary key I planned to use contained a unique combination of three fields
I'll cut to the chase. There are new features for AX 2012 leveraging features in SQL that AX used to not be able to use (primarily alternate keys). Here are the criteria that need to be done to get the index to show up in the properties of the AX table so this error can go away:
- The index needs to be unique ('AllowDuplicates' property set to No) (Figure 2)
- The index needs to have 'AlternateKey' property set to Yes (Figure 2)
- The primary index of interest can only have one field in it
- NOTE: This sucks in the scenario I have where we have three fields which would usually be the primary index. A primary field should technically only be one field anyways. This is just forcing the scenario. So create two unique indexes if you need to export to Excel using the add-in: one that is the primary and one that contains the unique combinations of the other fields. Both Alternate Keys values should be set to true.
- The field on the table used in the primary key needs to be mandatory (Figure 3)
- The field on the table used in the primary key cannot be editable (Figure 3)
- Setup a number sequence or value creation logic for this value (complexity just went up)
Figure 2 - The index properties on the table (note: do not edit in the USR layer like in this picture...)
Figure 3 - The field properties in AX for the field used in the primary key
I try to follow your guide to do with DimensionAttributeValue table to import Fanancial dimension value using excel. but still get the same Error massage ( my problem still not fix). Could your please me help what did i do wrong ?
Thank your very much.
Can you send me an XPO of the object to my email address on this blog? I can help you out with this. Should be something really simple I'm hoping. I'll send the fixed XPO back. I'll post what I did to fix the issue, too.
I have a new table. I still get same error. Could you please help me.Delete
Dang, can you send me an XPO file with your table in it? It's awfully hard to assist this one without more information.Delete
I'm not sure if all of those settings (Mandatory, Allow Edit, etc) are needed. I think you just need to have the Index to say "AllowDuplicates == No" and "AlternateKey == Yes" and only have one field.ReplyDelete