How to Deal with eConnect Exceptions for Bank Transactions Integration in Dynamics GP
In this blog post, we are going to discuss how to deal with eConnect primary key exceptions. Let’s say there is a company called Fabrikam Inc. and they provided the data for Dynamics GP. We are going to leverage eConnect to integrate Bank Transactions into Dynamics GP.
The eConnect Primary Key Exception
When I try to integrate Bank Transactions into Dynamics GP, the eConnect throws an exception and the following message shows up "Violation of PRIMARY KEY constraint 'PKCM20100'. Cannot insert duplicate key in object 'dbo.CM20100'."
This Primary Key error is thrown by the SQL Server whenever I tried to insert duplicate key values into the table. The primary key for the table dbo.CM20100 is a combination of two fields CMDNUMWK and VOIDED. Take a look at the screenshot below to view the schema of dbo.CM20100 table and highlighted the primary key.
The Data Issue
I analyzed the eConnect business logic which creates Bank Transactions into Dynamics GP. Then, I was able to identifiy that the issue was with the data that comes from the sample company, Fabrikam Inc. The taBRBankTransactionHeader stored procedure gets the next value for CMDNUMWK field by calling a SQL stored procedure named cmGetLastJournalNumber. The cmGetLastJournalNumber generates the value by populating a record into dbo.CM40102 table and returns the DEX_ROW_ID as the next number.
I needed to make sure that the value returned by sproc cmGetLastJournalNumber for field CMDNUMWK doesn't exist in the dbo.CM40102 table. The above issue can be fixed by running the query attached below.
DECLARE @loopVariable INT
DECLARE @maxValueLoopVaribale INT
SET @maxValueLoopVaribale = (SELECT Max(CMDNUMWK)
FROM CM20100 WITH (NOLOCK))
SELECT @maxValueLoopVaribale = ISNULL(@maxValueLoopVaribale, 0)
SELECT @loopVariable = 1
WHILE ( @loopVariable <= @maxValueLoopVaribale
AND @maxValueLoopVaribale > 0 )
INSERT INTO CM40102
SELECT @loopVariable = @loopVariable + 1
DELETE FROM CM40102
Voilà! Now the issue has been resolved. Happy Troubleshooting!
*Important: please make sure to back up the Company and System databases before making any changes.
As a 4-Star NetSuite Partner and Microsoft Gold Partner, we understand that your business is unique and won’t fit a pre-determined mold. We can help you select and implement the right ERP solution for your business, so you can get the maximum value out of your system. For more information or assistance with Dynamics GP, please do not hesitate to contact FMT Consultants here or give us a call at 833-827-4275.