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.
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)