Notifications
Announcements
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
I would love this as well +1
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.
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
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?
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.
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.
As AI tools become more common, we’re introducing a Responsible AI Use…
Thanks to all of our 2025 Community Spotlight stars!
These are the community rock stars!
Stay up to date on forum activity by subscribing.
