Dynamics Community Forum Thread Details

Sql Script needed to set department and category

Can somebody help me. I need a sql script that will assign the department to all items from a supplier. The issue is that a duplicate department and some duplicate categories were created and these were deleted and now I have certain suppliers with all of their items without departments or categories. I'm hoping that there is a way to write a sql command that would assign the department to all items for a particular supplier, the same for category.

Thanks,

Grier

use: Select * from [supplier]

and record the value in the ID column.

use: Select * from [department]

and: Select * from [category]

record the ID columns for any you might need. For the categories take not of the departmentID also. Do not cross update an item, for example the category has a departmentID of 2 and you update the Item to that category but enter a categoryID of 3. This will cause you problems.

This will work for items and their Primary Supplier. If you need secondary supplier then this gets way more complicated.

Then use the following to update.

Update [Item] set DapartmentID = {ID you recorded - replace{} also}, set CategoryID = {category id here, replace everything including {} here} where supplierID = {same as before, replace with Supplier ID number}

so it should look something like this:

Update [Item} set DepartmentID = 2, set CategoryID = 5 where SupplierID = 10

Quick Links

Leaderboard