Microsoft Dynamics GP (Archived)

Update User Defined Category Fields on Items

(0) Share

Report

Posted on by ATLTEK

Does anyone have a SQL script or another method to update a few hundred items in GP with new user defined categories?

I have SmartConnect, but its missing the nodes for item maintenance.

I have an Excel workbook with all of the items(exported via SmartList) and their updated categories.

Thanks!

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (2)

Sort by

Verified answer

Praveen Kumar RR 1,552 on at

Like (1)

Report
Copy link

Link copied!

Hi,

You can update Inventory Master Table IV00101 with below SQL Query,

UPDATE IV00101 SET USCATVLS_1 = 'ItemCat1' WHERE ITEMNMBR = 'TEST'

However, before you try this you need to make sure that you have done Item Category Setup and the table IV40600 has appropriate value in it.

Above SQL Query will help you to update same Category to one or more items and if you want to have different Category value to each item then it is much time consuming task.

Happy to assist you.

Thanks,

Praveen

Was this reply helpful? Yes No
ATLTEK on at

Like (0)

Report
Copy link

Link copied!

[quote user="Praveen Kumar Ramamoorthi"] Hi, You can update Inventory Master Table IV00101 with below SQL Query, UPDATE IV00101 SET USCATVLS_1 = 'ItemCat1' WHERE ITEMNMBR = 'TEST' However, before you try this you need to make sure that you have done Item Category Setup and the table IV40600 has appropriate value in it. Above SQL Query will help you to update same Category to one or more items and if you want to have different Category value to each item then it is much time consuming task. Happy to assist you. Thanks, Praveen [/quote]

Thanks. To do multiple items at a time, do I simply separate them with commas?

ITEMNMBR = 'ITEM1, ITEM2, ITEM3'

Was this reply helpful? Yes No
Verified answer

Praveen Kumar RR 1,552 on at

Like (0)

Report
Copy link

Link copied!

You can use below query to update same Item Category for all items,

UPDATE IV00101 SET USCATVLS_1 = 'ItemCat1' WHERE ITEMNMBR IN ('ITEM1','ITEM2','ITEM3')

Was this reply helpful? Yes No
Suggested answer

Bill Campbell 12 on at

Like (0)

Report
Copy link

Link copied!

Are you looking for the same Category value for each item or will the category value be different from item to item?

If you have any common items that have the same category then this works, but if you have a list of 100 items with 100 different answers for the USCATVLS_1 then you have to write 100 lines of SQL

The last post from Praveen will allow you to assign the same value in Category 1 to all the items found in the ( ) list.

Be careful when writing scripts, be sure to have a good backup and if possible work out the issues in a TEST company database, not PRODUCTION

Good luck.

Was this reply helpful? Yes No
ATLTEK on at

Like (0)

Report
Copy link

Link copied!

Thanks fellas. I'll try it out now. I will be running this on a TEST database for the TEST company in GP. Then let finance team hammer GP and find anything buggy. Then move to Production DB and GP Company.

Was this reply helpful? Yes No