Primary key index not showing up in AX 2012 table

There was an issue where I was training a couple resources for the client on how to create table and a form in AX so they can import data into AX, view/modify the data in a simple form, then export the data to Excel for reporting. Simple enough. A 30 minute mod that turns into a 4 hour AX training exercise because its a 'teaching tool' exercise.

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

Hope the above helps some people. It's not a huge deal but was a source of frustration when it should not have been an issue...  Please make sure to chose your primary key carefully as it can be extremely tricky to change this in a production environment.