Most of the time during record creation, dynamics ax throws error. in ax 2012

Most of the time during record creation, dynamics ax throws error.

“Cannot create a record, the record already exists”

There could be multiple reasons for this issue. This post is targeted to resolve the issue #3

1. Unique index is getting violated
2. Number sequence used is not generating the correct number.
   • This may be the issue related to number sequence cache
     • This can be resolved setting the correct next number at the number sequence
3. RecId is not getting the correct sequence
   1. This issue happens because of table synchronization issue during production build creation.

Solution Issue#3:

The way RecId number sequence generation works is that there is stored procedure namely ‘sp_GetNextRecId’ used by AX to generate next recId. AX does not call this stored procedure for each recId generation. It caches the range and when range expire, it call the SP to get the next ID.

There is also a table namly ‘SystemSequences’ where AX stores RecId along with table Id. if there any synchronization issue related to ID conflicts or new data is brought in, some times this sequences goes out of sync.

To correct the issue, We can update the table directly but we also have to flush cache for AX to starting looking at the change.

Code to update table and flush Cache

The code below is copied from Shashi’s blog. This solves the issue

static void SetNextRecId(Args _args)
{
SystemSequences seq;
ttsBegin;
select firstonly forupdate crosscompany seq
where seq.tabId == 123456; // use the table id here or tablenum()
seq.skipTTSCheck(true);
seq.skipDatabaseLog(true);
seq.selectForUpdate(true);
seq.nextVal = 5637123456 + 1; // enter the last recId for the table
seq.update();
ttsCommit;
}

You may need to run this job and restart the AX client only if the just the above doesn’t work

static void sab_recIdSequenceFix(Args _args)
{
SystemSequence systemSequence = new systemSequence();
Tableid tableId = 123456; // use the table id or tablenum() here

systemSequence.suspendRecIds(tableId);
systemSequence.suspendTransIds(tableId);
systemSequence.flushValues(tableId);
systemSequence.removeRecIdSuspension(tableId);
systemSequence.removeTransIdSuspension(tableId);
}