Wednesday, August 3, 2011

Cannot insert the value NULL into column 'BASEUOFM' error when clicking on Items List in Navigation Pane

Just recently a few partners began reporting getting the error Cannot insert the value NULL into column 'BASEUOFM' when clicking on the Items list under the Inventory Navigation Pane option.



Items list error
  The name of the global temp table - in this case, tempdb.dbo.##0251007 - 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 Unit of Measure Schedule (UOMSCHDL) column in the Item Master table (IV00101). In summary, if you have an item record with a blank Unit of Measure Schedule or a Unit of Measure Schedule that does not exist in the Unit of Schedule Master table (IV40201), 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 IV00101 WHERE UOMSCHDL NOT IN (SELECT UOMSCHDL FROM IV40201);

Once you have identified the record(s) causing the failure, you can use the Item Maintenance window to correct the problem:


Item Maintenance window

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

6 comments:

Maria said...

Hi Mariano, we are getting a similar error when clicking on the Item List "Cannot insert the NULL into column 'ATYALLOC'". ATYALLOC doesn't have a master pick list like BASEUOFM so instead, I checked if the IV00102 record was missing.

SELECT * FROM IV00101 WHERE ITEMNMBR NOT IN (SELECT ITEMNMBR FROM IV00102);

No luck with that, any suggestions would be appreciated!

Thanks
Maria

Maria said...

Update! I resolved the issue right after sending you the question on getting this insert error for the column ATYALLOC.

Procedure IV_ItemList is executed to populate that listing so I pulled out the query. There is a left outer join of IV00101.MSTRCDTY = IV00102.RCRDTYPE.

For one item the MSTRCDTY value was 0 so it did not match any record in IV00102. I updated it to 1 and ensured the sites were assigned properly through the front end.

No idea how the data went bad but the listing is back on and running.

Maria

Kimberley said...

Do you know which tables the Account Transactions List on the Financial Navigation Pane pulls from? I'm receiving the error that you mentioned on that list where the column referred to is 'ACTINDX'. I've tried the following queries, and none of them result in any data:

SELECT * FROM GL10001 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00100)
SELECT * FROM GL20000 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00100)
SELECT * FROM GL30000 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00100)

SELECT * FROM GL10001 WHERE ACTINDX = ''
SELECT * FROM GL20000 WHERE ACTINDX = ''
SELECT * FROM GL30000 WHERE ACTINDX = ''

SELECT * FROM GL10001 WHERE ACTINDX = '0'
SELECT * FROM GL20000 WHERE ACTINDX = '0'
SELECT * FROM GL30000 WHERE ACTINDX = '0'

SELECT * FROM GL10001 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00105)
SELECT * FROM GL20000 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00105)
SELECT * FROM GL30000 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00105)

I can't figure out where else to look!

I've also run Check Links and it doesn't result in any errors.

Thanks!

Kimberley

Jerry Mehler said...

I'm having a very similar problem with list "ALL SALES TRANSACTIONS". I'm getting "can not insert the value NULL into column 'custnmbr'.

I've tried check links on all sales and RM files and can't find any logical tables that might be linked that have a NULL customer Number

Anyone have any ideas?

Mariano Gomez said...

Jerry,

The easiest way to go about this is by capturing a DEXSQL.LOG to find the offending record. The list certainly compiles data from various tables, so it could be coming from any of the tables used in that query.

Jerry Mehler said...

Thank you Mariano

You definitely pointed me in the right direction. Though it took about 4 hrs of detective work to track done the offending records in both SOP30200 and RM10300. Had to trace from a script to 4 different views back to the originating real SQL Tables

Thanks again

Jerry