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.
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):
Once you have identified the record(s) causing the failure, you can use the Item Maintenance window to correct the problem:
Until next post!
MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/
 |
| Items list error |
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/
Comments
SELECT * FROM IV00101 WHERE ITEMNMBR NOT IN (SELECT ITEMNMBR FROM IV00102);
No luck with that, any suggestions would be appreciated!
Thanks
Maria
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
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
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?
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.
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