Update Inventory Item Accounts From CSV

Microsoft Dynamics GPThis script was produced for a client who wanted to bulk update the accounts defined against selected Inventory Items in Microsoft Dynamics GP 2010 R2. This particular client did not have Integration Manager so I needed an alternative approach to doing the update.

I could have used a GP Macro to do the job (doing one while recording the macro to create a template to be populated from a CSV using Mail Merge) but it was easier to create a SQL script to do the job directly from the CSV (this is the same view I took for updating the Account Segment Master).

This script runs from a CSV with the Item Number in the first column and then the Inventory, Inventory Offset, COGS, Sales, Sales Returns, Variance and Inventory Returns in the following columns (there are more Accounts which could be added but these were the ones the client wanted to update).

If you use this script then, as always when running a script, please make sure you have a good backup of your company database (I define a good backup as one you have restored to ensure it backed up correctly) and know what you are doing in Management Studio;

Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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). */
CREATE TABLE #StockCodes    (    ITEMNMBR VARCHAR(100)    ,IVIVINDX VARCHAR(100)    ,IVIVOFIX VARCHAR(100)    ,IVCOGSIX VARCHAR(100)    ,IVSLSIDX VARCHAR(100)    ,IVSLRNIX VARCHAR(100)    ,IVVARIDX VARCHAR(100)    ,IVRETIDX VARCHAR(100)    ) GO BULK INSERT #StockCodes FROM 'D:\Imports\StockCodes.csv' WITH    (    FIELDTERMINATOR = ',',    ROWTERMINATOR = '\n'    ) GO UPDATE    IV SET    IVIVINDX = ['Account Index Master - Inventory'].ACTINDX    ,IVIVOFIX = ['Account Index Master - Inventory Offset'].ACTINDX    ,IVCOGSIX = ['Account Index Master - Cost Of Goods Sold'].ACTINDX    ,IVSLSIDX = ['Account Index Master - Sales'].ACTINDX    ,IVSLRNIX = ['Account Index Master - Sales Returns'].ACTINDX    ,IVVARIDX = ['Account Index Master - Variance'].ACTINDX    ,IVRETIDX = ['Account Index Master - Inventory Returns'].ACTINDX FROM    IV00101 IV INNER JOIN    #StockCodes AS Stock        ON Stock.ITEMNMBR = IV.ITEMNMBR INNER JOIN    GL00105 AS ['Account Index Master - Inventory']        ON ['Account Index Master - Inventory'].ACTNUMST = Stock.IVIVINDX INNER JOIN    GL00105 AS ['Account Index Master - Inventory Offset']        ON ['Account Index Master - Inventory Offset'].ACTNUMST = Stock.IVIVOFIX INNER JOIN    GL00105 AS ['Account Index Master - Cost Of Goods Sold']        ON ['Account Index Master - Cost Of Goods Sold'].ACTNUMST = Stock.IVCOGSIX INNER JOIN    GL00105 AS ['Account Index Master - Sales']        ON ['Account Index Master - Sales'].ACTNUMST = Stock.IVSLSIDX INNER JOIN    GL00105 AS ['Account Index Master - Sales Returns']        ON ['Account Index Master - Sales Returns'].ACTNUMST = Stock.IVSLRNIX INNER JOIN    GL00105 AS ['Account Index Master - Variance']        ON ['Account Index Master - Variance'].ACTNUMST = Stock.IVVARIDX INNER JOIN    GL00105 AS ['Account Index Master - Inventory Returns']        ON ['Account Index Master - Inventory Returns'].ACTNUMST = Stock.IVRETIDX GO

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

3 thoughts on “Update Inventory Item Accounts From CSV

Leave a Reply

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