Way to mass change supplier for multiple items at once

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

Reply to
Skyler Lister A
Loading thread data ...

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.

Reply to
rsakry

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" wrote in message news: snipped-for-privacy@microsoft.com...

Reply to
Matt Hurst

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.