Dynamics Community Forum Thread Details

Troubleshooting D365 FO RecId numbering in Production environement - Cannot create a record in Table (Table) the record already exists.

In D365FO for each table the number RecId is a mandatory column added by the system for which it's a primary key. This field is linked with a sequence.

But same times RecId is not getting the correct sequence (does not increment).

In Sandbox environment we have access on the database from LCS but not for production environement.

To resolve this problem we must increment the minvalue in the sequence :

1- Get the table Id

select * from SQLDICTIONARY where NAME like 'custtable' and FIELDID = 0

2- Get max RecId from table

select max(RecId) from CUSTTABLE

3- Get next value from sequences

select next value for dbo.SEQ_9136

4- update the number value

ALTER SEQUENCE SEQ_22096 MINVALUE 5637219000 RESTART WITH 5637219000

this solution is work fine in sandbox environement because we ave access on database.

Solution for Production environement

Here we must create a runable class to update the sequences values

class RecIdSequenceCustTable
{
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {
        CustTable custTable;
        Connection connection ;
        Statement statement ;
        RecId nextRecID;
        str sql ;
        int cnt ;
        
        connection = new Connection();
        statement = connection.createStatement();
        select firstonly custTable order by RecId desc;
        nextRecID = custTable.RecId + 1 ;
        connection.ttsbegin();
        sql = strfmt("ALTER SEQUENCE SEQ_%1 MINVALUE %2 RESTART WITH %2",custTable.TableId,nextRecID);

        cnt = statement.executeUpdate(sql);
        statement.close();

        connection.ttscommit();
    }
}