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
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.
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?
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
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.
I would love this as well +1
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (