Monday, April 13, 2015

How to assign a new Dynamics GP Item Class to existing inventory items and roll down class settings

By Steve Endow

UPDATE: A reader contacted me to let me know that the process outlined below may not work properly if you are using Dynamics GP Manufacturing.  Some manufacturing tables contain the item class ID, so the process below does not account for those.  Also, updating the item class ID via SQL may not properly trigger certain updates in manufacturing.  The reader suggested that the macro approach, while more tedious to setup, is probably a safer method.

I was asked if there was a way to create a new Inventory Item Class, apply that new class to a lot of existing inventory items, and roll down the new item class settings to those inventory items.

Obviously you can open an individual inventory item, change the class ID, and when prompted, roll down the class settings to that single item.  But how do you do this in bulk for 50 or 100 or more items?

I searched around to see if anyone had a good solution, and I found a few potential workarounds, such as using a macro, but none of the options sounded very appealing.

After thinking about it for a few minutes, I came up with this solution.  It seems to work, but there may be a particular scenario that it doesn't handle, or some fields that it doesn't update.

Here's my approach to assigning a new item class to a bunch of existing inventory items and rolling down the new class settings to those items.

First, create the new inventory item class and populate as few fields as possible.  I'll explain why below.

Second, use a SQL statement to update the class ID for the relevant inventory items.  Something like this:
Your items will now be assigned to the new classID.

Next, open your new inventory class and edit all of the fields that you need to set.

When you click on Save, you will be prompted if you want to roll down the changes.

Click on Yes to roll down the changes.

After rolling down the changes, open several of the inventory items to confirm that all of the settings rolled down properly.

In my testing, if I only changed one or two fields on the new item class, even though I clicked Yes to roll down the changes, the field changes to the class did not roll down to a test item.  I didn't perform additional testing to see why the small change didn't roll down, or which fields were, or were not, included in the roll down.  But I did test changing several fields, and after I rolled those changes down, they did update the items properly.

It seems like this should work, but as I mentioned earlier, there may be some caveats.  So give it a try and let me know if you find any issues or ways to improve the process.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

No comments: