After a slight mid-week diversion, here is another script I produced a while ago which updates the Item’s Default Purchasing Unit of Measure. It was produced when a client wanted to bulk update a large number of records which would have taken a long time manually but by script only minutes to write and run.
In Microsoft Dynamics GP, the Default Purchasing Unit of Measure is maintained in the Item Purchasing Options window () along with the Purchasing Options (IV00106) but the Default Purchasing Unit of Measure is stored on the Item Master (IV00101) table;
CREATE TABLE #UploadData
(ITEMNMBR VARCHAR(100)
,PRCHSUOM VARCHAR(100))
GO
BULK INSERT
#UploadData
FROM
'c:\temp\PRCHSUOM.csv'
WITH
(FIELDTERMINATOR = ','
,ROWTERMINATOR = '\n')
GO
UPDATE
['Item Master']
SET
PRCHSUOM = ['Upload Data'].PRCHSUOM
FROM
IV00101 AS ['Item Master']
INNER JOIN
#UploadData AS ['Upload Data']
ON ['Upload Data'].ITEMNMBR = ['Item Master'].ITEMNMBR
DROP TABLE #UploadData
As always before running a script make sure you have a backup of your company database and only run the script if you’re comfortable working in SQL Server Management Studio.nbsp;
Pingback: Interesting Findings & Knowledge Sharing » Updating Item Purchasing Unit Of Measure From A CSV