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.

All Purchasing Transactions list error - Purchasing Navigation List
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):

' 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
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/

Comments

ThongD said…
Thank you very much for your post. We've been searching and asking in the Dynamics GP Community for months and here it is. However, we didn't get the error until we upgrade from GP2010 SP1 to GP2010 SP2 aka R2.

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.
August 29, 2011 at 5:05 PM
Unknown said…
Thank you for your post, it is very helpful as usual. I am using Dynamics GP 2010 and having this same issue. After I ran your query, there are few records show up with BLANK vendor ID, every other columns are either just zero or NULL values. It seems like they are some corrupted records, so I removed them. But now, when I go to Purchasing->Navigation List-> All purchasing transactions, it gave me a different error message "Violation of PRIMARY KEY constraint 'PK##1133951'. Can not insert duplicate key in object 'dbo.##1133951'".

It seems some duplicate records are still existing when it is trying to insert into the temp table.

Any help would be appreciated!
March 29, 2013 at 1:59 PM
Mariano Gomez said…
Haitao,

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.-
March 29, 2013 at 2:30 PM
locuranet2 said…
Excellent! works for me, great job! thanks!
January 28, 2014 at 1:20 PM
Post a Comment

Popular posts from this blog

#MSDYNGP: Named Printers and Redirected Printers in RDP environments

Image
A lot of the guiding principles for deploying Named Printers in a Terminal Server or Citrix environment comes from two of my favorite articles, written by my good friend and fellow Microsoft Business Applications MVP, David Musgrave  (twitter: @winthropdc ). David happens to be the creator of Named Printers and probably understands the product better than anyone I know. You can read his articles here: Using Named Printers with Terminal Server Using Named Printers with Terminal Server Revisited These articles continue to be very relevant if you are in an environment where a Print (or Printer) server is the norm and published printers are standard. Print servers are used to interface printers with devices in a network, but mostly to standardize administrative policies, and balance the document load that printers can manage. Part of the standardization is to ensure printers are uniquely identified across the networks, regardless of whether you are accessing the network remotely or ph
Read more

Why my transactions don't post through GL even when I have the flag selected?

Image
Are you tired of asking why is it that some transactions do post through GL and why some don't? Tired of changing the posting settings to no avail? This issue outta be one of the most confusing issues for users and consultants, new or seasoned alike. If you ever struggle with one issue in GP, please tell me it's this one, because even today, I cannot claim victory over this subject. So let's walk the walk together. What the theory says? According to Part 3, Chapter 13, page 87 of the Microsoft Dynamics GP's System Setup guide, " If you mark the Post Through General Ledger Files option, batches posted in other modules automatically will update the module in which the transactions originated and the appropriate posting accounts in General Ledger. Your transactions will post to General Ledger and not through General Ledger when posting transaction individually (without a batch). " Now lets break it down... It's important to really pay close attention
Read more

Adding more comment lines to POP Purchase Orders

Image
Just recently, I was asked by a customer to address an issue with their line item comments truncating at 4 lines. In essence, the customer wanted the ability to print more than 4 lines of comments at the line item level on their purchase orders. My customer happens to be in the Nuclear Waste Management industry, and as it turns out, they have strict requirements to provide contract information, start/end dates, and a number of regulatory and compliance information on purchase orders. Some of this information is very critical to the transportation of certain products on United States highways. So as you can imagine, the request for additional comment lines was critical. The following shows an example of what the customer was facing: Purchasing Comment Entry window and POP Purchase Order Blank form As you can see, the comments are printing only 4 lines and truncating the rest of the text. Before going into the details, they are a few articles that guided me thro
Read more