Cannot insert the value NULL into column ‘CONTACT’ error when clicking on Items List in Navigation Pane

August 11, 2011

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/

Advertisements

Configuring Email for Sales and Purchasing Documents in Microsoft Dynamics GP 2010

September 15, 2010

Recently, I have been receiving a number of questions on the new Sales documents email functionality in Microsoft Dynamics GP 2010 and how to get it configured and working adequately. If you know me by now, I love to translate those questions into useful articles for the Microsoft Dynamics GP community at large.

Fortunately enough, the configuration process is not that difficult. Microsoft Dynamics GP 2010 allows this feature to be configured in a top-down approach which in turn ensures that all related areas of the application are email-aware.

As a first step, there is a setup needed to be done at the company level – MSDGP > Setup > Company > E-mail Settings, or if you prefer, click on Administration on the Navigation Bar, then locate the E-mail Settings link under the Setup web-part:


The first settings you will be able to configure is whether you want the actual document embedded as part of the email body or as an attachment to the email itself. My preference is the latter as it allows for a cleaner look when the email is received by the recipient. Microsoft Dynamics GP supports 4 different attachment formats, DOCX, HTML, PDF and XPS.

Since Microsoft Word and Adobe Acrobat are widely available and have been around for a while, I strongly suggest selecting at least these 2 options. XPS has only been around as a document standard since 2009 (released in 2006) and has native support on Windows Vista and Windows 7, hence recipients running Windows XP may experience some difficulty opening attachments in this format, especially if they are unaware of the XPS viewer’s download location.

NOTE: The IE-hosted XPS viewer and the XPS Document Writer are also available to Windows XP users with the download and installation of Microsoft .NET Framework 3.0.

Once you have defined the initial settings, you may then proceed to enable the email documents that can be submitted from your Microsoft Dynamics GP application. You will be able to define settings for both the Sales and the Purchasing series, as shown below:

Sales E-mail Setup

Purchasing E-mail Setup

The Sales E-mail Setup and Purchasing E-mail Setup windows can also be accessed under the respective series setup menus.

Of special importance is to setup the different labels for each document that will be enabled for submission. Also, if you are expecting the recipient to reply to your emails, select the mail boxes to which replies will be delivered using the Select Names button. My personal preference is to setup general mailboxes on your Microsoft Exchange Server or any other email hosting application. For example, your sales orders can have a general mailbox such as orders@fabrikam.com, or accounts_receivable@fabrikam.com if you are expecting replies to things like AR credit memos or debit memos from the recipients Accounts Payable team. You may do the same for purchasing. This overall approach ensures that emails are delivered to one location or an individual in charge of monitoring these replies.

Finally, you may further tailor specific settings for customer and vendor records, which will allow you to define at a granular level wheter the recipient will receive multiple attachments per email and the specific format for each document that will be attached to the email. You may also choose to enable or disable specific documents that may be submitted to the recipient.

Customer E-mail Options

Vendor E-mail Options

A feature I find particularly useful is, if you have customers or vendors that have size limits to their mailboxes, you can tailor Microsoft Dynamics GP to not allow file sizes above those limits.

I hope this review of the email capabilties of sales and purchasing documents has been useful. Please drop your comments and let me know whether you are using these features today in your business and if it has aliviated any of the past pains you’ve experienced by not having the functionality or in turn has created new problems.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


Encumbrance vs PO Commitments

June 8, 2010

Two modules often confused are the Encumbrance and PO Commitments modules. They do have some similarities, but also have clear differences.

The basic differences are:

  1. Encumbrance tracks history in Great Plains while PO Commitments does not.
  2. Encumbrance doesn’t integrate with Requisition Management only PO Commitments. You can still use Encumbrance instead of Commitments, but you can’t track against a budget within Business Portal Requisition Management until you create a PO in Great Plains.
  3. Encumbrance requires all budgets to be entered for all open fiscal years but PO Commitment’s budgets can be entered for any years you want to.

Their similarities:

  1. They are initiated in the Purchase Order Entry window.
  2. Budgets are for Purchase Orders only.
  3. Can do multiple purchases simultaneously (mass).
  4. Inquiries on current view.
  5. No MC functionality.
  6. Maintenance available.

Hope this helps shed some light.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


Moving Items out of Inventory and into Fixed Assets

March 13, 2010

I love challenging customers (and partners) to use their Microsoft Dynamics GP system in very innovative ways. I like hearing things like ‘this cannot be done‘ or ‘there isn’t a way to do that‘. If there is a way, trust me, I will find it! One of these ways came in the form a simple question: how would you take an item that is already in inventory and move it to Fixed Assets?

Right off the start gate, I can think of 3 different ways to do this, but the solution I like the most because of its elegance and simplicity is as follows:

1. Setup an inventory site called FA and one called VIRTUAL or I/O (typically you will already have one of these setup if you have been using Inventory Control for a while).

2. Use the In-Transit Trasfer Transactions to transfer from your warehouse to the FA site using VIRTUAL as via site. Click on the Ship button to initate the ‘transfer’ to a Fixed Assets. In turn, this should create and in and out to your Inventory account — so far good!

3. Open the Receiving Transaction Entry window in GP and perform an In-transit Inventory receipt type.

4. Select the In-Transit Transfer number and select the item to be transferred to a Fixed Asset.

5. Open the Receiving Item Detail Entry window then mark the Capital Item option.

6. When you click the Capital Item checkbox, click on the expansion button (blue arrow) to open the FA PO Additional Information window to add the asset information.

7. Post the receipt.

Since the Purchase Order Processing module uses a Fixed Assets clearing account to capitalize an item, you should be fine (accounting wise) once the asset is created in FA and you run your FA GL posting routine. There you go! No need to create additional vendors or customers or perform extra gimmicks.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


From the Newsgroups: Multicurrency POP Receipts

February 9, 2010

Welcome to another weekly edition of the new From the Newsgroups blog entry. This week’s topic revolves around Multicurrency POP Receipts and a feature available in GP. As usual, no names will be given out, just the question and the answer.

Q: Functional currency is CAN [Ed: Canadian Dollars]. Item has a receipt that was for qty of 6000 each at .62 each USD currency. Vendor is setup as a US vendor. In the fifo layers it shows a receipt for 5999 at .62 each then another line for a qty of 1 at $12.57. Why is the system showing two lines for this receipt and breaking out the exchange all in one line. The exchange should spread over the qty of 6000 not qty 1.

We know there is an issue with split receipts when the item has more than 2 decimal points or there is landed cost involved. That is not the case here. This appears to be because of the exchange rate.

The response is as follows:

A: Good Day.

Thank you for using Forums.

With your exchange rate, it would appear that you have 6000 qty at an extended cost of $3731.95. Is this correct?

If so, the way GP handles a situation where the qty is not evenly divided into the extended cost is to produce a split receipt. The ‘logic’ behind it takes as many qty at the ‘unit cost’ as possible, and then puts the left over amount on the last single qty. This is the way this has worked as long as we’ve been using split receipts.

This is not so much a matter of putting the ‘exchange amount’ on one qty, but rather that we can’t spread the extended cost over the 6000 qty evenly. $3731.95 divided by 6000 is .6219916666666666666 repeating. We have to use some mechanism to have 6000 qty in our system at total cost of $3731.95. The method we use is a split reciept of 5999 @ .62 and 1 @ 12.57. This is standard GP functionality and has been for many versions.

If you have some suggestions on doing this in a different manner, please feel free to enter a product suggestion so that it can be reviewed for possible future releases.

Thanks!

Some of the features in GP are simply hidden to the naked eye unless you are dealing with a number of situations day in and day out. Multicurrency is one such situation.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


Why my PO Number seems to skip randomly?

December 11, 2009

The opening goes something like this:

I upgraded from version X.XX [typically 7.x, 8.x] to Microsoft Dynamics GP 10.0 and now we are having a problem where the next PO number will sometimes jump back in sequence, often many numbers. What’s happening to our PO Numbers?

In my previous article, Why does my next document number change randomly? I gave some insight into why Microsoft Dynamics GP 10.0 seems to randomly skip document numbers, sometimes backwards. Here is more information on PO Numbers and another method of preventing this behavior.

In Microsoft Dynamics GP 10.0 Purchase Order Processing module, it is possible that a user may have a purchase order number that is completely different from the next number found in the Purchase Order Processing Setup window. Although the next number for purchase orders is set up in the Purchase Order Processing Setup window, Microsoft Dynamics GP will look for unused PO numbers that precede that number. The system will be able to re-use a purchase order number in the following circumstances:

1. If you are not keeping purchase order history and void a purchase order.
2. If you are not keeping purchase order history and delete a purchase order.
3. If you are keeping purchase order history and remove historical purchase orders.

If PO Numbers lower than the one specified are still available, Microsoft Dynamics GP will use this PO Number instead of the one you have set up.

To resolve this, you can use the Minimum PO/Receipt Number utility from the Professional Services Tools Library (PSTL). This tool will allow you to prevent Purchase Order Processing from defaulting the PO Number or Receipt Number to a number that is previous to the currently set up next PO.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com


SQL – Retrieving the most recent receipt info for an item

November 27, 2009

It’s been quite a while since I have posted a SQL script, and it’s funny, because this is what I had in mind when I started out my blog. Shaun Childers posted a question on the Microsoft Dynamics GP public newsgroup, as follows:

“We are trying to create a script that will pull together our most recent
purchasing information only. The results should give all inventory items
with a qty on hand > 0, the current cost (we are average perpetual), the most
recent receipt number, the unit cost for that receipted item, the receipt date,
and the vendor name. I have tried to put this together, but have been
unsuccessful.”

At first, this query may not seem to complex, but when you start to analyze the information being requested, it becomes apparent that using a standard set based query is not going to be as simple. Luckily enough, we can take advatage of the latest T-SQL enhancements to use ranks and partitions on sets to deliver the requested query, as follows:


-- Created by: Mariano Gomez, MVP
SELECT A.ITEMNMBR
, B.ITEMDESC
, B.CURRCOST
, ISNULL(R.receiptdate, '01/01/1900') AS LastReceiptDate
, ISNULL(R.UNITCOST, 0.00) AS UNITCOST
, ISNULL(R.VENDNAME, '') AS VENDNAME
FROM IV00102 A
LEFT OUTER JOIN IV00101 B ON (A.ITEMNMBR = B.ITEMNMBR)
LEFT OUTER JOIN (
SELECT ITEMNMBR, UNITCOST, receiptdate, VENDNAME FROM (
SELECT C.ITEMNMBR, C.UNITCOST, D.receiptdate, D.VENDNAME, RANK() OVER
(PARTITION BY C.ITEMNMBR ORDER BY D.receiptdate DESC) AS RECEIPT_RANK
FROM POP30310 C
LEFT OUTER JOIN POP30300 D ON (C.POPRCTNM = D.POPRCTNM)
) Receipts WHERE RECEIPT_RANK = 1
) R ON (A.ITEMNMBR = R.ITEMNMBR)
WHERE (A.RCRDTYPE = 1) AND (A.QTYONHND > 0)

In Microsoft Dynamics GP v10, the POP Receipt history tables also hold In-Transit Transfer transactions. If you are looking to retrieve strictly vendor receipts, you may change the query as follows:


-- Created by: Mariano Gomez, MVP
SELECT A.ITEMNMBR
, B.ITEMDESC
, B.CURRCOST
, ISNULL(R.receiptdate, '01/01/1900') AS LastReceiptDate
, ISNULL(R.UNITCOST, 0.00) AS UNITCOST
, ISNULL(R.VENDNAME, '') AS VENDNAME
FROM IV00102 A
LEFT OUTER JOIN IV00101 B ON (A.ITEMNMBR = B.ITEMNMBR)
LEFT OUTER JOIN (
SELECT ITEMNMBR, UNITCOST, receiptdate, VENDNAME FROM (
SELECT C.ITEMNMBR, C.UNITCOST, D.receiptdate, D.VENDNAME, RANK() OVER
(PARTITION BY C.ITEMNMBR ORDER BY D.receiptdate DESC) AS RECEIPT_RANK
FROM POP30310 C
LEFT OUTER JOIN POP30300 D ON (C.POPRCTNM = D.POPRCTNM)
WHERE D.POPTYPE 8
) Receipts WHERE RECEIPT_RANK = 1
) R ON (A.ITEMNMBR = R.ITEMNMBR)
WHERE (A.RCRDTYPE = 1) AND (A.QTYONHND > 0)

Hope you find this query useful.

Until next post,

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/