Record IDs in AX 2012

Record IDs (or Recid's in short) have always provided a means to identify data in AX tables. With the introduction of AX 2012, its importance has increased manifold. AX2012 has come up with the new features of surrogate keys and best practice of using Recid's as foreign key instead of table's natural keys. Recid can also be marked to be created as an index (or even primary index) of the table. Lot of developers may debate on this trend (especially who have to debug lengthy code with Recid joined tables :) ) but the fact remains that Recid has become very important in AX. Let's see how the Recid is generated and managed in AX.

 In AX 2012 R2, the single database is split into two databases - business database and model database. The later stores the details about AX metadata i.e. what elements exist in the application, what are their IDs, in what layer, which model etc. A table named 'ModelElement' contain the most needed information about it.



Model element have AOT metadata

 

The same data can be accessed in AOT by System documentation > Tables > SysModelElement. Which is essentially a view of the 'ModelElement' table.

 SysModelElement in AOT table browser

There is another table 'SystemSequences' which stores the next Recid for each AX table. The field 'NextVal' contains the next value of Recid for the table record and 'TabId' is the AX table id of the table in question. Writing a simple join on ModelElement and SystemSequences table can easily tell you what is the next Recid for a table.



System sequences data

 

                

Note: Some Recids are stored in system cache as well so the query may not give correct result always.

 There is very simple logic how system assign Recid. It looks if there is a record for the table in 'SystemSequences' table. If yes, then assign the 'NextVal' as the Recid and increase 'NextVal' and 'RecVersion' fields by one. Otherwise, create a new record in 'SystemSequences' table with some prefixed initial values.

 Please see the 'sp_GetNextRecId' stored procedure in AX business database to find the exact code.

 Now when we know how AX is storing and managing the Recid, let see what can be done further in the scenarios when Recid goes corrupt. It is rare scenario but may happen. You will not able to insert record in a table and will get weird error saying 'Cannot insert record in table <Table name> (<Table AOT name>). <TitleField 1> <Its value>, <Title field 2> <Its value>. The record already exists.'

 If this happens and you are sure that record is not creating any unique index violation, then you can check what is the 'NextVal' value for the table in 'SystemSequences' table. If the record with this value as Recid already exists in your table, then the sequence is corrupted. In such situation, you should use 'SystemSequence' API class.

This class has methods to suspend, remove suspension of Recid and TransId for tables and also to flush these values for specific table.

 

One probable sequence of method calls to remove the RecId corruption is

1.      Suspend the RecId

2.      Suspend the TransId

3.      Flush the values

4.      Remove RecId suspension

5.      Remove TransId suspension

 

Although the above method has worked for me always, I can't vouch for it as the code of these methods is hidden in AX kernel, which I can only guess. It is not advisable to update 'NextVal' in 'SystemSequences' table directly by SQL. But use your own judgement when you are stuck is such problem.

I hope the above article will help unravel one of the best kept secret of AX2012 & gain you some insight of Recid working.