Microsoft Retail Management System (RMS)
SQL customization – overview for programmer
By Andrew Karasev
1-866-528-0577, 1-630-961-5918
Alba Spectrum Technologies
Microsoft Retail Management System
serves retail single store as well as retail chains clientele. The
customization scenarios, described in this article deal with the chains, when
Microsoft RMS has multiple stores databases and Headquarters. Microsoft RMS is
SQL Server-based application, so we will consider SQL programming – SQL queries,
views and stored procedures. Also Microsoft RMS could be integrated on the SQL
level with other applications, such as Microsoft Great Plains, Microsoft CRM and
non-Microsoft applications, such as Lotus Notes/Domino
- Microsoft RMS Architecture and data
flow. As it is POS automation application, it assumes that all the POS
transactions are originated at the store. The mechanism used is worksheets.
When store is closed – Headquarters pulls its transactions via worksheet.
There are some exceptions – global data, such as global customers are updated
on the headquarters level and updates propagated to the stores.
- Data Update. If you need custom
logic work with the POS transaction – you have to update store database data.
If you try to update Headquarters database – the chances are that it will be
rolled back by the next worksheet, because original data sits on the store
level and is different from your altered data in the HQ
- Store Level Web Services. Yes –
they will enable you to communicate with store database and have transaction
data updated over there. The other way is having all the stores as linked
servers from Headquarters MS SQL Server Enterprise Manager
- Integration with Great Plains or
other SQL Server Accounting package. RMS normally replicates transactions
from the stores to Headquarters upon the store business hours close. At this
time they are ready to be integrated with such accounting system as Microsoft
Great Plains. Here we are talking about custom integration, which you can
create as a stored procs set.
- Gift Card puzzle. There are few
funny things in MS RMS you should be aware. Imagine – you issue Gift
Certificate. It exists on the Headquarters level and each store knows about
the balance on the card. Customer uses card in store A and then walks in to
the store B. If worksheet mechanism didn’t play during this time interval –
store B gift card balance will show incorrect value. To fix the issue you
need to trigger worksheet creation and launch on the headquarter level to
replicate new balance to all the stores.
Good luck with customization and if you have issues or
concerns – we are here to help! If you want us to do the job - give us a call
1-866-528-0577! help@albaspectrum.com
Andrew Karasev is Chief Technology Officer in Alba
Spectrum Technologies – USA nationwide Microsoft RMS, Great Plains, Microsoft CRM customization
company, serving clients in Chicago, California, Texas, Florida, New York,
Georgia, Arizona, Minnesota, UK, Australia and having locations in multiple
states and internationally (
http://www.albaspectrum.com ),
he is CMA, Great Plains Certified Master, Dexterity, SQL, C#.Net, Crystal
Reports and Microsoft CRM SDK developer. You can contact Andrew:
andrewk@albaspectrum.com