AX7 – Table Id and Field Id

During development and especially debugging the new Microsoft Dynamics AX you are to face the question which table Id or field Id actually stands for which table. In AX 2012 you saw the Ids in the property sheet of selected AOT elements; those were stored in the model database. With AX7 this has changed.

If you want to know the Id of table CustTable or its field AccountNum you can use X++ and do something like

info(strFmt('%1', tableNum(CustTable)));
info(strFmt('%1', fieldNum(CustTable, AccountNum)));

This hasn’t changed. Some data references those Ids – for example document references (the attachments available throughout the system). If you examine stored data you need to do the other way around and find out about what it references. Still you can do that using X++

info(tableId2Name(10347));
info(fieldId2Name(10347, 1));

But what to do when you aren’t able to execute X++ code or simply don’t want to because you don’t have Visual Studio or there’s a simpler and quicker way? 🙂
In AX 2012 I usually used the (virtual) table UtilIdElements for that and had a simple list form with that table as datasource. Right now the table still exists but it is empty at any time.
Now there is an even easier solution. The mappings now are stored in the AX database which comes with great advantages! It is one factor enabling you to move database backups between environments. If you didn’t know yet – yes, this is possible now (if you take some things into consideration, of course).

The new tables you can query for table and field names when you only know the Ids are
TABLEIDTABLE
TABLEFIELDIDTABLE

Unfortunately you can’t use the table browser to access them but SQL Server Management Studio will let you do the trick. As an example I present you the T-SQL select statements for CustTable (Id 10347 in my environment) and its field AccountNum (Id 1).

SELECT [ID]
      ,[NAME]
      ,[RECVERSION]
      ,[RECID]
  FROM [AxDBRAIN].[dbo].[TABLEIDTABLE]
  WHERE [ID] = 10347
SELECT [ROOTID]
      ,[TABLEID]
      ,[ID]
      ,[NAME]
      ,[RECVERSION]
      ,[RECID]
  FROM [AxDBRAIN].[dbo].[TABLEFIELDIDTABLE]
  WHERE [TABLEID] = 10347
  AND [ID] = 1

The ROOTID field of the field Id table is used for table inheritance. There are some more tables starting with TABLE and they might help you when you need further information in that area.

TABLEIDTABLE

4 thoughts on “AX7 – Table Id and Field Id

  1. Thanks for the writeup. I found your post after googling “TableIdTable”, which, in turn I found trying to puzzle out why some standard Ax tables got new ids. The part that really got my attention was where you said “It is one factor enabling you to move database backups between environments. If you didn’t know yet – yes, this is possible now (if you take some things into consideration, of course).”

    I was hoping to find exactly what you said. Could you elaborate some more on that? Especially the things that should be taken into consideration.

    Thanks in advance

    • In very short: You can exchange database backups between single-machine environments (typically development environments -> the .vhd images you get in LCS) if the build versions of standard AX and third-party addons (like ISV solutions) match. In 2012 you need to make sure the *_model database have the very same contents, too (by importing the modelstore first). I put that on my list for future blog posts and hope I’ll find time to continue producing new ones soon 🙂

  2. SqlDictionary is still there. And it’s also accessible from ax table browser

    select * from SqlDictionary
    WHERE [TABLEID] = 10347 and FIELDID = 0

Leave a Comment