Change Vendor Class in SQL Table

(0) Share

Report

Posted on by Dan Troxell

215

I'm being asked to run a SQL script to mass change the value in the VNDCLSID (Vendor Class) column in the PM00200 table. This would be a big time savings from manually changing them in the application. It's been done here in the past by other dbs but altering data directly in Dynamics GP tables makes me nervous. Does anyone have any experience or know of any concerns I should have doing this?

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

sandipdjadhav 18,306 on at

Like (0)

Report

Dan,

Changing Data directly on SQL Table is risky, I think better you use Integration Manager to Update your Vendor Master.

Thanks

Sandip

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Dan,

If you do not own Integration Manager (the perferred route), then updating the PM Vendor Master is an option. Just realize that it will not impact anywhere else. So, for example, if the classs ID is stored in some history table then it will not change that. I have done similiar in past.

Was this reply helpful? Yes No
Suggested answer

Frank Hamelly | MVP... 46,625 Super User 2025 Season 2 on at

Like (0)

Report

Dan, Sandip is correct, direct updating in SQL does carry it's risks. However, there are only 2 tables in GP that contain this column - PM00100 (Vendor Class Master) and PM00200 (Vendor Master). I think you're relatively safe in a direct update. Just make sure you backup the database before making the update and then run Checklinks on the PM Master afterward.

Hope this helps,

Was this reply helpful? Yes No
Tim Foster 8,515 on at

Like (0)

Report

Some of the answers hint at what your problems will be after doing a SQL update of the VNDCLSID. I'm hoping this will make it clearer. You will *only* be changing the VNDCLSID value in the PM00200 table. You will not experience the benefits of doing this through the front end (GP) - Vendor Cards can be updated with the defaults loaded from the Class cards. If this is what you want, why not write a macro and use MS Word to merge all of the Vendors and Classes? Otherwise, Sandip's suggestion of using IM would be best. The SQL to update all the peripheral tables will be much more complex (and risky).

Was this reply helpful? Yes No
Frank Hamelly | MVP... 46,625 Super User 2025 Season 2 on at

Like (0)

Report

Tim is 100% correct. If you're looking for the roll down of Class settings - use Integration Manager. Direct table update won't do it for you.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Or write a macro to assign the class ID's to vendors. This ulates what happens when you make the changes manually.

Make sure that every time you are actually changing the class id, so that the message appears asking if you want to roll down changes.

Was this reply helpful? Yes No
Dan Troxell 215 on at

Like (0)

Report

After extensive testing I went ahead and made the direct table update today in production. Check links did not find any problems after this process, so I felt reasonably safe. I agree this is probably not a good idea but it's what the end user’s wanted and only time will tell if it's created an issue to deal with later. Thanks for everyone's help.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Just remember that any values in the new class won't have rolled down to the vendor records. So the entire Vendor fields (except the class ID fiels!!) will remain as they were before you changed the class id.

Best regards,

Ian.

Was this reply helpful? Yes No