Sunday, November 19, 2017

Cannot create a record in Workflow tracking status table (WorkflowTrackingStatusTable). Correlation ID: {70B17AE2-AF1A-47A9-937F-39F206AF91DE}, Pending. The record already exists.

Encountered this issue once the system recovered from the RecId max out in the workflow tables. There were certain transactions that were submitted and updated as In-review however the workflow was not triggered for them. The workflow bar was missing and on trying to Re-Submit the workflows the error was received.

Solution :

  1. Shutdown all the AOS instance
  2. Bring up any one AOS, synchronize the database.
  3. Re-Submit the workflow for the failed records.

    TSTimesheetTable    timesheets;
    WorkflowTrackingStatusTable  trackingstatus;

    while select timesheets
    where 1==1
        && timesheets.ApprovalStatus == TSAppStatus::Pending
        //&& timesheets.TimesheetNbr == 'CCM-175040'
    notexists join trackingstatus
    where trackingstatus.ContextTableID == 4627
    && trackingstatus.ContextRecId == timesheets.RecId
        timesheets.ApprovalStatus = TSAppStatus::Create;
                                       "Resubmitted due to error",
                                       timesheets.createdBy );

    info( timesheets.TimesheetNbr );

AX 2012-Cannot create a record in Workflow tracking status table

Got the message in the workflow. The messages can be seen in the EventViewer and can also be seen on screen if the workflow is triggered using the tutorial form.

The reason for this error is when the RECID of the tables involved and systemSequences is out of sync. Which means the RecId that the system tries to generate for a new record is something which is duplicate and has been generated in the past.

The reason why this mismatch occurs could be anything and is not known to me; however what is important at this point, is to get out of this situation. With the little R&D that i did, i realized the following facts.

  1. The RecIDs are not generated real time from the systemSequences table. In reality each AOS caches a certain amount of RecID and tries to use them. 
  2. Due to the above stated fact the max RecId that is visible directly from SQL database, is different from the one that actually exists in the AOS. Hence it is important that when we are correcting the NextVal in the systemSequences table, the value is fetched using the AOS table and not from the database directly (using SQL scripts).

Solution : 
  1. Check of the Maximum value of the RECID column for the table resulting in the error, in this case it is WorkflowTrackingTable. 
  2. Make sure you check for the value of RECID from AOS and not using the SQL table as there might be records that are not committed to the database. 
  3. Sort the table by RECID in descending order and copy the maximum RecID as reported by AOS. 
  4. Update the systemSequences table by adding 250 to this value to be on the safer side and to avoid cached values. 
  5. Compile the table giving the error on each of the AOS so that the cache's is cleared.