Cannot insert the value NULL into column 'CONTACT' error when clicking on Items List in Navigation Pane
Moving on from my previous article on a similar subject - see Cannot insert the value NULL into column 'BASEUOFM' error when clicking on Items List in Navigation Pane, I recently came across this error, Cannot insert the value NULL into column 'CONTACT' when clicking on the All Purchasing Transactions list under the Purchasing Navigation Pane option, after performing an upgrade from Microsoft Dynamics GP 9.0 to Microsoft Dynamics GP 2010 R2.
The name of the global temp table - in this case, tempdb.dbo.##2093338- varies in almost all cases, but the end result of the error is the same. The issue has been identified running Microsoft Dynamics GP 2010 RTM, SP1 or SP2.
Upon further review, the issue is due to bad data in the Vendor ID (VENDORID) column in the Purchasing Receipt History table (POP30300). In summary, if you have a purchasing receipt with a blank vendor ID or a vendor ID that does not exist in the Vendor Master table (PM00200), it will cause the Items list to fail with the error above.
The following query should help in identifying the offending record(s):
Once you have identified the record(s) causing the failure, you can use the Vendor Maintenance window to add the missing vendor or further study the issue to remove the offending receipts if necessary:
Patrick Roth, Escalation Engineer with Microsoft and blogger at Developing for Dynamics GP, provides a full explanation of his troubleshooting method for this error at the Partner Online Technical Community forum:
GP2010 Purchasing List Error - Partner Online Technical Community forum
Until next post!
MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/
 |
| All Purchasing Transactions list error - Purchasing Navigation List |
Upon further review, the issue is due to bad data in the Vendor ID (VENDORID) column in the Purchasing Receipt History table (POP30300). In summary, if you have a purchasing receipt with a blank vendor ID or a vendor ID that does not exist in the Vendor Master table (PM00200), it will cause the Items list to fail with the error above.
The following query should help in identifying the offending record(s):
' Created by Mariano Gomez, MVP ' This code is licensed under the Creative Commons ' Attribution-NonCommercial-ShareAlike 2.5 Generic license. SELECT * FROM POP30300 WHERE VENDORID NOT IN (SELECT VENDORID FROM PM00200);
Once you have identified the record(s) causing the failure, you can use the Vendor Maintenance window to add the missing vendor or further study the issue to remove the offending receipts if necessary:
 |
| Vendor Maintenance window |
GP2010 Purchasing List Error - Partner Online Technical Community forum
Until next post!
MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/
Comments
Based on your post we figured that the problem is the In-Transit Inventory transaction, in the Receiving Transaction entry window, does not allow VendorID to enter.
Hope Microsoft will remove that check in SP2 as they did in GP2010 RTM and SP1.
It seems some duplicate records are still existing when it is trying to insert into the temp table.
Any help would be appreciated!
Thanks for the kind words.
The only suggestion I have is to run a SQL Server Profiler trace or a DEXSQL.LOG to see which record is causing the problem.
MG.-