Skip to main content

Notifications

Microsoft Dynamics RMS forum

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

Posted on by Microsoft Employee
Hi all, I was just wondering does anyone know of a way to unassign a supplier to a group of items in headquarters Many thanks for any ideas Margaret

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Headquarters Unassign a supplier to a group of items in a department

    Hi A,

    Thank you for this very detailed answer. I will be using your query

    Margaret

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Headquarters Unassign a supplier to a group of items in a department

    Thanks very much for your suggestion

    Kind regards

    Margaret

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: 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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Headquarters Unassign a supplier to a group of items in a department

    You'd need to run a DELETE query, but you have to hit both the supplierlist and item tables; if you can, bite the bullet and do it through the gooey (delete the supplier in the SUPPLIER tab of the item; add the correct supplier there as needed, too)

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,802 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,133 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,154

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans