Identifying Duplicate Transactions

By David Musgrave

Because Microsoft Dynamics GP is designed to have separate tables for WORK, OPEN and HISTORY transactions, there are times where it is possible for a transaction record to exist in more than one table. This is usually the result of an error or interruption.

These duplicate records become an issue when it comes time to move a transaction from one table to another. For example: when it is posted, fully applied, or paid transaction removal is used. At this time, the duplicate will generate a duplicate key error and the process will be aborted.

Another time when duplicate records can cause problems is when using an Inquiry window which can show data from more than one of the WORK, OPEN and HISTORY tables at the same time. To achieve this, the Inquiry windows transfer data into a single temporary table and use this table for their display. If duplicate records exist, you will get an error when the data is being copied into the temporary table.

An error message that may be generated by several inquiry windows, when there are duplicates, refers to a createSQLTmpTable stored procedure. This stored procedure does not actually exist, but is in fact referring to pass through SQL script called from the Dexterity code. For example: The error message from the Payables Transaction Inquiry window is The stored procedure createSQLTmpTable returned the following results, DMBS: 2627, Microsoft Dynamics GP: 0. Error 2627 is a SQL Cannot insert duplicate key error.

To make it easier to find duplicates, I am providing the following SQL queries to look for duplicate headers in the core Distribution and Financial modules. While these will not find every possible duplicate in every table, they are a great basis for checking for duplicate transactions.

SQL Script to look for duplicate headers in SOP, IVC, POP, RM, PM, IV, & GL modules


Mohammad R. Daoud
Software Development Manager
+962 – 79 – 999 65 85
Great Package For Business Solutions


About Mohammad R. Daoud
Mohammad R. Daoud has been working as a Microsoft Dynamics GP consultant since 2004. His career path started with version 7.5; he studied every single tip of the application technicalities and did allot of successful implementations that includes functional consultations, analysis, and custom development projects. He holds a graduate degree in Computer Science and currently he is pursuing MBA degree in Accounting. In January 2007 he was nominated to the Microsoft Most Valuable Professional (MVP) certificate and was certified in April 2008 due to his online contributions in the Dynamics Community (Dynamics GP Newsgroups, Forums, User Groups and his blog: Worth to mention that he was also listed as one of the Microsoft Dynamics Top 100 Most Influential People in 2009 by DynamicsWorld. And more... • Demonstrated Passion for MS Products through participating in beta testing for some product versions, an effective member of Microsoft Connect. • Talented in securing strong high quality relationships with premier customers, partners and sales teams passionately and profitably. • Dedicated to meeting the expectations and requirements of internal and/or external customers • Problem Solver – Uses systematic approach to uncover true problem(s) and practical solution and can marshal resources to solve problem! • Proven record of effective account management, including Account Planning, Opportunity Management, and Business Management Excellence and working as part of a larger virtual team • Successful in delivering high quality technical engagements in the enterprise Dynamics GP implementations area according to customer requirements. • Dedicated, Committed, Self Motivated, Creative and highly focused with excellent communication, Negotiation, presentation, Active Listening and Objection Handling skills. • Experienced in Profitable researches and studies including white papers and technical presentations.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: