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.

On the D365 one-boxes you can get from Connect, or via LCS as Demo environment, SQL Management Studio (SSMS) is preinstalled and already set up.

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.


If we run the script, the result will be shown below the query as following.
AX7 get table name for table id

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.

AX7 get table id for table name

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.

AX7 get field name for table id and field id

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.

AX7 get field id for table id and field name

 

Advertisement

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *