Way to mass change supplier for multiple items at once

[Skyler Lister Aley]

Hi all,

Is there a way to mass change the supplier for multiple items at once. When
we have a change in who we order a product line from we have to go into each
item individually to update the supplier info. Is there a easier way to
update multiple items?

Thanks, Skyler

[Ryan @ RITE]

Skyler,
Not out of the box. If you understand the database structure, this can
certainly be don via SQL commands, but if you do not know 100% what you
are doing, you can certainly break things fast.

There may be 3rd party tools out there pre-built to do this, I don't
know of any off the top of my head. If you cannot find a tool to do
this, and it is something that you will need on a regular basis, please
feel free to drop me an email and we could discuss what it would take to
build such a tool.

--
Thank you,
Ryan Sakry
Program Manager
rsa...@rite.us
http://www.rite.us
320-230-2282 ext. 4002
(Office) 320-230-1796 (Fax)

[Matt Hurst]

There are two places where this problem applies to the data:
Item.SupplierID and SupplierList (MinimumOrder, ItemID, SupplierID, Cost,
ReorderNumber, MasterPackQuantity, TaxRate)
There is a one to many relationship between the two, as you can order items
from different suppliers, but there is only one default supplier designated
by the Item.SupplierID.

To just change the supplier and not leave the old entry, you can blanket
overwrite with something like:
update s set s.SupplierID=(select id from supplier where
suppliername='NewCO')
from SupplierList s join item i on itemid=i.id and i.supplierid=s.supplierid
where i.SupplierID=(select id from supplier where suppliername='OldCO')
and i.CategoryID=(select id from Category where name='Widgets')

Then change the Item records to set the new supplier as default

update item set SupplierID=(select id from supplier where
suppliername='NewCO')
where SupplierID=(select id from supplier where suppliername='OldCO')
and CategoryID=(select id from Category where name='Widgets')

This isn't a great solution because it assumes that all the detail records
stay the same except for the supplier. Normally when you change vendors
it's because they are giving a better price and all the new records need to
reflect that price, order number, MPQ etc. For that solution, I usually get
the new info in an excel spreadsheet, use MS Access to append into my
supplierlist table, then repoint the item default supplier to the new
records.

"Skyler Lister Aley" <SkylerLi...@discussions.microsoft.com> wrote in
message news:A4FBA7D2-E2C5-4643...@microsoft.com...