web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Supplier List
Microsoft Dynamics RMS (Archived)

Supplier List

(0) ShareShare
ReportReport
Posted on by Krista Bezanson 819

We have approximately 12,000 items in our database that do not have a supplier assigned to them. Is there a way to assign the supplier info to these without doing them one at a time?  We have 300 suppliers!

*This post is locked for comments

I have the same question (0)
  • Clint Skog Profile Picture
    Clint Skog on at

    Hello Krista,

    It would be possible, but you would have to write SQL scripts. As you have so many items and so many suppliers it would be pretty time consuming. You could open a support request, but i would imagine it would end up being an Advisory service.

  • Community Member Profile Picture
    Community Member on at

    Assigning suppliers to a bulk of items involves an update statement to the Item table and an Insert statement to the SupplierList table, the 2 statements would be similar to this: -

    ***************

    update item

    set supplierid=supplier.id

    from supplier

    where supplier.code='xxxx'

    and ItemLookupCode in ('','','')

    and SupplierID=0

    INSERT INTO SupplierList

    SELECT DISTINCT  '1' as MinimumOrder,Item.ID as ItemID, SupplierID, Cost,'0' as ReorderNumber

    ,'1' as MasterPackQuantity,NULL as DBTimeStamp,'0' as TaxRate

    FROM Item

    WHERE ItemLookupCode in ('','','')

    **************

    The above statements assume that your criteria is based on the ItemLookupCode, while is definitely not practical to execute the query 300 times specifying the items for each supplier

    So my suggestion is that you prepare an excel sheet with 2 columns only (ItemLookupcode & supplier code) then import this excel sheet into a temp table in the database, then I can change the above query to use the temp table you've just imported so that you execute the query only once

    If the above suggestion is OK with you, just let me know and I will post the edited query. My only concern is that you have to be sure that all of the items in the excel sheet don't have any suppliers already  assigned to them, although we can check this anyway before executing the query

    A Hassan

    RMS Leaders

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics RMS (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans