Notifications
Announcements
No record found.
Is it possible to add an index that spans the fields belonging to base table and inherited table? Ultimately it's one table in SQL but I can't see how to add such an index. Is it a flaw?
*This post is locked for comments
It depends what version of AX you are using. In 2012 R2 and R3, inherited tables have effectively been removed and all of the data was reduced back into a single table. The AOT still gives the appearance of inheritance. CompanyInfo, for example, is not an actual table from the perspective of SQL Server. You will instead find your CompanyInfo records in DirPartyTable (the root of the long string of AOT based inheritance starting from CompanyInfo), distinguished by RelationType and such. Thus, in that case any index on CompanyInfo in the AOT can only be applied to DirPartyTable at the level of SQL Server.
Things are, of course, entirely the opposite in 2012 RTM.
Hope this helps.
Or did I read your question wrong?
Hi Brandon,
It is AX 2012 R3 CU8.
Yes, I understand the physical backend is the same table for the parent and all inherited tables. The appearance that AOT presents - multiple AX tables - is what prevents me from adding an index.
Here's the scenario.
Base table: EcoResProductMasterDimensionValue
Inherited table: EcoResProductMasterSize
I need an index that includes several fields from the base table and the size field from the inherited table. Is there a way to create it in AX?
I sure can create it directly in SQL but then I get an error during db sync. It's not critical but very annoying.
You will not be able to add the fields from the parent table into the child table index. Although in DB its a single table. AX treats the parent and child as logically separated.
If you need indexes on parent table fields. I suggest you combine the tables.
That was my suspicion all along. Seems like a flaw in AX.
Combining the tables would be a very hard path to take in this case. These tables are central to PIM and changing the structure would likely cause a lot of re-write and future upgradability issues. I guess I could remove the inherited table and create a new view instead to alleviate potential code rewrite but even that is beyond the scope for now.
I'm surprised that it's not supported, nevertheless it seems to be the case. I suggest you create a feature request on Connect.
I would suggest to log a bug with support as well.
What I expect is for it to come back as working as designed. :)
It really is as designed - it's just designed with a flaw.
Thank you all, guys - it's always good to confirm that I'm not missing something obvious and a negative outcome is still an outcome :)
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Martin Dráb 4 Most Valuable Professional
Priya_K 4
MyDynamicsNAV 2
