Skip to main content

Notifications

Microsoft Dynamics RMS (Archived)

RMS Database relationships

Posted on by 290

Hi,

I'm pretty new to using RMS and wondered if anyone has put together a diagram of how the tables relate to each other?

We are looking into writing our own custom reports and it would make life so much easier if I could see how the tables link.  I've managed to get a copy of the schema but although the tables seems self-explanatory, it doesn't seem obvious (to me) which tables are linked.

 

*This post is locked for comments

  • KursatOzer Profile Picture
    KursatOzer 15 on at
    RE: RMS Database relationships

    The easiest way to see relations on RMS tables is JOIN statements on QRP (Reports) files.

    For example;  

    FROM TenderEntry

    LEFT JOIN [Transaction] ON TenderEntry.TransactionNumber = [Transaction].TransactionNumber

    LEFT JOIN Batch ON [Transaction].BatchNumber = Batch.BatchNumber

    LEFT JOIN Register ON Batch.RegisterID = Register.ID

    LEFT JOIN Cashier ON [Transaction].CashierID = Cashier.ID

    or

    FROM TransactionEntry WITH(NOLOCK)  

    INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber

    LEFT JOIN Item WITH (NOLOCK) On TransactionEntry.ItemID = Item.ID

    etc.

  • Rob Gresswell Profile Picture
    Rob Gresswell 290 on at
    Re: RMS Database relationships

    I've been working through the schema comparing which Primary ID relates to which Foriegn IF in the tables but it appears that there are some ID fields that do not relate to any table.  I'm assuming there are some ID fields that are there purely for reference?

  • Clint Skog Profile Picture
    Clint Skog on at
    Re: RMS Database relationships

    We don't have any diagrams drawn up, nut you can access the Database Schemas at the following location:

    How to obtain the database schemas for Microsoft Dynamics Retail Management System

    mbs2.microsoft.com/.../KbDisplay.aspx  

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    Re: RMS Database relationships

    I'm not aware of any diagram, but I can say most basic references are via the ID column in each table. For example, there is a value in the itemdynamic table called itemid which corresponds with the id column in the item table. Likewise, the categoryid column in the item table corresponds to the id column in the category table.

    The only obvious exception I can think of off the top of my head is that the transactionentry table uses the transaction number and storeid to reference the transaction table.

    I've found that  "JOIN Table2 ON Table1.ID = Table2.Table1ID" or vice-versa will be the correct format in probably 90% of situations.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: RMS Database relationships

    I would love this as well +1

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,336 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans