AX7 / D365: Quick and Easy – get Table / Field Name from Id and vice versa
In the new developing experience that came along with D365, the parts that I personally miss most, compared with older AX version, are Jobs. Yes, now there are Runnable Classes, but the time it takes to write and execute a Runnable Class makes them very inconvenient in comparison to the “old” Jobs, for example in AX 2012.
So in every occasion where I need a quick info from the database, I would rather invoke a SQL statement, than writing a Runnable Class. Often it happens to me when trying to retrieve table- or field names from given table / field ids.
After opening SMMS look for the Database AxDB and run a query against it. The table we’re interested in is the SQLDICTIONARY. In this table we will find the ids of all tables and fields.
If you are interested in a table id, look for a record where the field id is 0. If you would like to find a certain field id, you have to know the table as well.
Table name from table id
The following statement returns the name for the table id 9669 in the database, as described above, we are looking for a row where the field id is 0.
|/* GET table name for table id */|
|WHERE TABLEID = 9669 AND FIELDID = 0|
If we run the script, the result will be shown below the query as following.
Table id from table name
The other way around we simply do the same as above, but instead of the id we are looking for the name of the table – here the PurchTable.
|/* GET table id for table name */|
|WHERE NAME = 'PurchTable' AND FIELDID = 0|
If you are interested in how to solve this with an AX job, here a post that I wrote about table names and ids in AX 2012.
Field name from field id
Searching for field names with a given field id works like we saw in the table examples, we just have to also add the table id to the query.
|/* GET field name for table id and field id */|
|WHERE FIELDID = 87 AND|
|TABLEID = 9669|
Field id from field name
Vice versa we select the field id and search with the field name PurchId and the table id we found earlier.
|/* GET field id for table id and field name */|
|WHERE NAME = 'PurchId' AND|
|TABLEID = 9669|