Skip to main content

Notifications

Microsoft Dynamics RMS (Archived)

Vendor Properties for an Item, which table is it at?

Posted on by Microsoft Employee

Hello-

Could someone tell me which table an items primary vendor is located at?  I have imported items into RMS to the main item table but noticed that there is no field there for vendor...my guess is because vendor is a multi-fielded table. 

I am learning SQL and my guess would be that I would first run a query to insert the items then run an update query to the table that has the vendor linked to the item ILC via some joins, etc.  Again, I am new at SQL and running these queries on test DB's before i go to breaking my own.

Any help would be appreciated.

Thanks

SD

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Vendor Properties for an Item, which table is it at?

    Hi Matt and Nicole-

    Thanks for your reply.  I am half way there...now what I want to do is find a way to insert into both of these tables.  There is probably 7 fields i already know how to populate using an insert into statement to the item table, but I also want to insert into the supplierList table at the same time...is it possible to do this from one query?  Right now I have, for simplicity sake, a query written that shows the ILC along with the 3 fields i would like to insert data into the supplierID.

    Any suggestions would be appreciated.  here is my query as a select..

    select i.ItemLookupCode, s.supplierID, s.cost, s.reorderNumber

    from Item as i inner join SupplierList as s

    on i.ID = s.ItemID

    I thought I could do the following..

    1.  write a simple insert into statement to insert into the item table.

    2.  use the query above but instead of a select, do an update statement but how would you write a sql update statement if you are updating a joined table?

    thanks

    joseph

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Vendor Properties for an Item, which table is it at?

    The primary supplier field is found in the SupplierList table.

    SupplierList has 2 joins to it so you can look up the other data. None of the tables use ILC as a join. Any reference to item in a foreign table is labelled as itemid which always relates to item.id.

    item.id = supplierlist.itemid

    supplier.id = supplierlist.supplierid

  • Nicole Hellerud Profile Picture
    Nicole Hellerud 1,712 on at
    Re: Vendor Properties for an Item, which table is it at?

    SD,

    Thanks for your query about what table under items the vendor is located at. I believe that the table that you are looking for is SupplierID. Does this sound as the correct table that you are looking for?

    Thanks Nicole

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

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

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,902 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,316 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans