Dynamics Community Forum Thread Details

Headquarters Unassign a supplier to a group of items in a department

Use the following script, but BACKUP FIRST

--IF THE SUPPLIER TO BE UNASSUGNED IS THE PRIMARY SUPPLIER OF ANY OF THE ITEMS TO BE AFFECTED, MAKE SURE TO HAVE ANOTHER SUPPLIER ASSIGNED TO REPLACE THE PRIMARY ONCE REMOVED

--1

--This is to change the LastUpdated field of the Item table so that so u can include the intended items into 250 worksheet using "Recently Updated" option

--this will affect intended items that don't have the intended supplier as primary

update Item

set lastupdated=getdate()

where item.id in (select itemid from supplierlist inner join supplier on supplierid=supplier.id where code=@SuppCode)

and item.supplierid<>(select id from supplier where code='27')

and item.departmentid=(select id from department where code='100')

--2

--delete all records related to the supplier from SupplierList

delete supplierlist

where supplierid=(select id from supplier where code='27')

and itemid in (select item.id from item inner join department on item.departmentid=department.id where department.code='100')

--3

--to see the items still having the intended supplier as primary

select itemlookupcode,description

from item

where supplierid = (select id from supplier where code='27')

and item.departmentid=(select id from department where code='100')

--4

--change the primary supplier in the item table of the list displayed by the previous query to any existing supplier already assigned (if any) to the intended items

--items that don't have other suppliers assigned to it, will remain having

update item

set item.supplierid=supplierlist.supplierid

,lastupdated=getdate()

from supplierlist

where item.id=itemid

and item.departmentid=(select id from department where code='100')

and item.supplierid = (select id from supplier where code='27')

A Hassan

RMS Leaders

Quick Links