Navigation List Error: “Cannot insert the value NULL into column ‘VENDNAME'”

Microsoft Dynamics GPThis came up a few days ago at a client site during an upgrade being done by a colleague and then again when I was running a training session.

When I was doing the training I was explaining what a Temporary Creditor (or Vendor for the American readers) was and why I hated them so much. As I was talking I was showing that even through the temporary creditor itself had been deleted, the history for it remained.

It was when I tried to open the Payables Transactions navigation list that I got the below error:

Cannot insert the value NULL into column 'VENDNAME'

Microsoft Dynamics GP

[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into 'VENDNAME', table 'tempdb.dbo.##181163', column does not allow nulls. INSERT fails.[Microsoft][SQL Server Native Client 11][SQL Server]Cannot insert the value

After clicking OK, the navigation lists display, but only with a very small number of transactions listed when there should have been far more:

Payables Navigation navigation list

I created another creditor with the same ID, and the navigation list loaded correctly.

If you see this error have a look for transactions which are not correctly linked to a record in PM Creditor MSTR (PM00200); you will then need to create a creditor with the same Creditor ID and set it to Inactive.

From a discussion with Microsoft, this is a known issue currently under investigation so hopefully a fix will be forthcoming.

What should we write about next?

Your Name (required) -
Your Email (required) -

8 thoughts on “Navigation List Error: “Cannot insert the value NULL into column ‘VENDNAME'”

  1. BBESS says:

    How do you “look for transactions which are not correctly linked to a record in PM Creditor MSTR (PM00200)”?

  2. Ian Grieve Ian Grieve says:

    Hi BBESS,

    I’ve just written the below script and tested in the Fabrikam sample company:

    CREATE VIEW uv_AZRCRV_GetMissingVendors AS
    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
    */
    SELECT
    	['Vendors'].*
    FROM
    	(
    	SELECT
    		['PM Key Master File'].VENDORID
    	FROM
    		PM00400 AS ['PM Key Master File'] WITH (NOLOCK)
    	UNION
    		SELECT
    			['PM Transaction WORK File'].VENDORID
    		FROM
    			PM10000 AS ['PM Transaction WORK File'] WITH (NOLOCK)
    		UNION
    			SELECT
    					['PM Transaction OPEN File'].VENDORID
    				FROM
    					PM20000 AS ['PM Transaction OPEN File'] WITH (NOLOCK)
    				UNION
    					SELECT
    							['PM Paid Transaction History File'].VENDORID
    						FROM
    							PM30200 AS ['PM Paid Transaction History File'] WITH (NOLOCK)
    	) AS ['Vendors']
    LEFT JOIN
    	PM00200 AS ['PM Vendor Master File'] WITH (NOLOCK)
    		ON
    			['Vendors'].VENDORID = ['PM Vendor Master File'].VENDORID
    		WHERE
    			['PM Vendor Master File'].VENDORID IS NULL
    GO
    GRANT SELECT ON uv_AZRCRV_GetMissingVendors TO DYNGRP
    GO
    

Leave a Reply

Your email address will not be published. Required fields are marked *