We are planning on implementing RMS and Headquarters for our multiple-store retail sales (do not have it installed yet). We are also going to have a virtual store, which will sell items from our brick-and-mortar store inventory. Without getting into too many details at this time, we have determined that the best way for us to accomplish our business needs is to have an external SQL database which will be used to keep track of brick-and-mortar store inventory and will also be used to record virtual store sales. We would then have triggers on this database and on the Headquarters database to update each other. Can triggers be written on the HQ database, or is that database non-customizable?
Triggers are a feature of SQL Server (and pretty much every other RDBMS) and can be written to any DB. They are not dependent on the application the Database was meant to support, such as RMS Store Ops or HQ. Microsoft has not made very much information on the RMS databases available, so in most cases you are going to have to feel your way through the data model, which is always dangerous.
I hope you plan to talk to a reseller before you go too far down this path. In the RMS hierarchy, Headquarters does not carry ANY inventory - it is all at the stores, so adjusting the HQ Database directly is going to be the wrong solution right from the outset. Of course, as you stated, you haven't provided many details here, so maybe you're already way ahead of me here.
Maybe I've got the wrong impression, then. As I understood it, Headquarters is sort of a superset of the individual store databases. Either through actual tables or views, it contains all of the inventory and sales information for the stores. When a sale is made in an individual store, it is recorded in the RMS database, but also in the Headquarters database (through a batch update), and the inventory is depleted in the RMS system as well as in the Headquarters system.
What I would be wanting to accomplish is to fire a trigger in the Headquarters database which would add or update records in an external database. Are you saying that I would have to put the triggers on each of the RMS databases instead?
The HQ Database does have the inventory and sales data for each store, but it is considered a 'snapshot'. The Store Databases contain the 'data of record'. The inventory numbers at HQ are really just a sum of the numbers at the stores. You would probably only want to work against one store database, not all of them, but I don't know your requirements.
HEre's my situation: I will be setting up a web store which will be selling items from a warehouse, as well as from my brick-and-mortar store stock. We have not been able to find a web integration component which will work with multiple stores or with Headquarters. What we are planning to do is to create an database, external to RMS and HQ, which will contain the summary inventory for all of the stores and the warehouse. This database will be populated through a series of triggers on our warehouse database (Radio Beacon), and through triggers on either the Headquarters database or on the individual store's RMS database. The web store will get it's product information and available inventory levels from this trigger-driven database. When an order is placed, we will create a web order in a dedicated copy of RMS. This copy of RMS is for virtual store sales only, and will not have an inventory of it's own. This allows us to record the sale, let it flow through naturally to our back-end accounting system (at this point, probably Great Plains) just like any sale in the brick-and-mortar store, we can use Headquarters to see overall sales (brick and mortar + virtual store), and we still maintain our inventory levels. Obvioulsy, this is not a complely fleshed-out process. There are still some significant unknowns, such as how to deplete brick-and-mortar store inventory when a web sale requires an item to be pulled from the store sales floor. I'm just trying to see if my approach described above is feasable, or if it's just wishful thinking.
Got an idea for the deleting inventory from brick and mortar to web store. Try testing the transfer inventory aspect. It will remove it from the db from the brick and mortar. I believe it will work as long as you don't have to transfer out sent to HQ.
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here.
All logos and trade names are the property of their respective owners.