Skip to content

Tips and Tricks #5 – Update Item Pricing Using Excel, Access, and SQL

March 25, 2010

I have a terrible time with pricing updates.  I do not mean price levels and margins, I mean plain ‘ole updating standard cost and list price.  Scribe is too much of a pain, Integration Manager is too slow, and I always seem to have to tweak the templates I have saved each time I do an update. 

After talking with our GP consultant today, Todd McDaniel, he gave me an idea.  Instead of messing with all the import softwares and Excel to CSV conversions, I just need to get the data straight into a SQL table and run an update statement.  Since updating cost and list price fields do not effect the GP business logic (***Unless you are using AVERAGE COSTING***), it is just as simple as updating their respective fields with and UPDATE statement.

Please make sure your Excel spreadsheet to have the following columns for updating your GP item pricing (Excel column names need to be exact):

Item_Number, Description, Cost, List

First we want to import the spreadsheet into Access.  I am using Access 2007.  Once imported as a table into Access, we are going to move the table to our SQL Server.  Once the data in the spreadsheet has moved to our SQL Server, it is all “gravy” from there.  Please follow the steps below to move your Excel spreadsheet to Access and then SQL Server:

Import spreadsheet into Access. 

1

Choose External Data –> Excel Import Wizard (follow the wizard to import your spreadsheet to Access)

2

After importing, rename table to PriceUpdates

3

Next select Database Tools –> Move Data –> SQL Server

Following the Move Data SQL Server Wizard to move the database to SQL

4

Use an existing database or create a new one (I chose to use an existing database)

5

Create a connection to your SQL server if you don’t already have one setup on your computer

6

After setting up your connection, choose the defaults on the Upsizing Wizard screen 1

 
 7

Select No application changes and then Finish

Your data will be moved to SQL Server

Once your data has been moved to SQL Server as a table, you can run the script below to update the items in your GP inventory and price list tables (IV00101 and IV00105) with the pricing from your Excel spreadsheet that we uploaded to SQL Server.

The following script will update the current and standard costs, item description, and list price for all the items that are in both your inventory table (IV00101) and your PriceUpdates table.

Execute the following script:

***Warning***

This script has code that will update data in the Dynamics GP database.  While this is not “illegal” it may or may not be supported by Microsoft.  I have not had any issues with this in our environment, but make sure you test this in your non production environment first, before deploying it to your production environment.

***End Warning***

The USE [TSP] tells the script to only execute on my database named TSP.  You will need to modify the [TSP] to be whatever your database name is.

USE [TSP]
UPDATE dbo.IV00101
SET STNDCOST=Cost, CURRCOST=Cost, ITEMDESC=SUBSTRING(description,1,101)
FROM PriceUpdates
WHERE ITEMNMBR=Item_Number

UPDATE dbo.IV00105
SET LISTPRCE=List
FROM PriceUpdates
WHERE ITEMNMBR=Item_Number

SELECT item_number, DESCRIPTION, list, cost, b.itemnmbr, itemdesc, stndcost, CURRCOST, c.LISTPRCE FROM PriceUpdates a
JOIN iv00101 b ON a.item_number=b.ITEMNMBR
JOIN iv00105 c ON a.item_number=c.ITEMNMBR
ORDER BY item_number

/*DROP TABLE PriceUpdates   ***Remove the – before the DROP TABLE to delete the PriceUpdates database.***/

Update (3/30/2010): I have updated the query to make sure that the length of the description column does not exceed the 101 characters allowed by GP using the SUBSTRING() function.

About these ads

From → Dynamics GP

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: