Microsoft Great Plains - Microsoft RMS Integration

Out‑of‑the‑Box RMS Integration with Great Plains

When Microsoft acquired Great Plains and Retail Management System (RMS) separately, the two platforms continued to evolve on their own paths. In practice, the integration story began only after both products were brought under the same corporate umbrella. Even today, the out‑of‑the‑box solution Microsoft offers remains a focused, narrow bridge that connects only the General Ledger (GL) and Purchase Order (PO) layers of RMS to a single Great Plains company. The design was intended to satisfy small retailers who needed to post financial data without the overhead of a full enterprise data integration. The result is a plug‑and‑play setup that ships with the RMS 1.2 and Great Plains 7.5 releases, and is slated to roll into the upcoming 8.0 version with a subcontract from Nodus Technologies.

Under the current implementation, every sales or purchase transaction generated in RMS is captured in a staging table that mirrors the GL and PO structure in Great Plains. When a batch job runs, the data is inserted into the corresponding GP tables, and the usual posting logic of Great Plains validates and commits the entries. The workflow is simple, but it comes with a number of caveats. First, the integration is limited to a single GP company, meaning that a retailer with several distinct store entities must manually map each RMS batch to the correct GP company file. Second, because the connector does not touch the bank reconciliation or cash management modules in Great Plains, any cash receipts or disbursements recorded in RMS must be reconciled manually in GP. This disconnect forces organizations to maintain duplicate cash flow records, which increases the risk of errors and slows audit readiness.

Despite these limitations, the out‑of‑the‑box tool can still serve as a solid foundation for many midsize retailers. The integration is supported by a set of stored procedures that translate RMS data into GP format, ensuring that each transaction adheres to GP’s business rules. For users who only need to post a few hundred transactions a day, the performance is adequate, and the cost of deployment is minimal. However, the real challenge arises when transaction volume climbs into the tens or hundreds of thousands. In such cases, the built‑in scheduler can become a bottleneck, and the lack of real‑time capabilities becomes more pronounced. Retailers that anticipate rapid growth or high transaction throughput must therefore look beyond the out‑of‑the‑box solution and explore more scalable integration patterns.

The design of Microsoft’s native connector also makes it difficult to customize the data flow. While the stored procedures can be edited, the integration’s configuration screens are very basic, offering only a checkbox to enable the connector and a field to specify the GP company database. There is no graphical interface for mapping fields, handling exceptions, or defining custom business rules. For organizations that require a more flexible mapping layer - such as aligning a new customer master between RMS and GP - the out‑of‑the‑box tool forces developers to resort to manual scripting or third‑party middleware. The result is that the native integration is best suited for organizations that have a stable, predictable set of transactions and that can accept a somewhat rigid data pipeline.

Because Microsoft’s support for the existing connector is already focused on version 7.5, the company’s future roadmap signals a shift toward more open, API‑driven solutions. Microsoft has announced that the next iteration of RMS will expose a RESTful interface, which will open the door to more modern integration approaches such as Azure Logic Apps or custom .NET services. For now, though, businesses that rely heavily on RMS for retail operations must weigh the trade‑offs of the current out‑of‑the‑box solution: lower upfront cost and simpler deployment versus limited scalability, a single‑company focus, and a lack of deep integration into cash management modules.

Custom Integration Paths and Third‑Party Solutions

When the constraints of the out‑of‑the‑box connector become too restrictive, retailers often turn to alternative methods that offer greater flexibility and higher throughput. One of the most common approaches is to leverage Great Plains Integration Manager, a tool that can import bulk files from RMS into GP’s staging tables. Integration Manager is user‑friendly, featuring a wizard that walks the user through selecting a source file, mapping columns, and reviewing the data before it is posted. Because it uses the same Windows interfaces that Great Plains itself relies on, it guarantees that all business logic - such as account validation and transaction sequencing - is respected. The downside is that it operates on a Windows desktop and runs as a background process, which can become sluggish when dealing with more than a few thousand records. Nevertheless, for organizations with modest transaction volumes - around 100 to 200 transactions per day - Integration Manager offers a quick, low‑cost solution without the need for custom coding.

For developers seeking a programmatic route, Microsoft’s eConnect framework provides a .NET‑based SDK that can push master data and transactional data from RMS into GP. eConnect exposes a set of COM interfaces that accept XML or structured data and translate it into GP work tables. By building a VB.NET or C# application that queries RMS, transforms the data, and calls eConnect, developers can achieve near real‑time posting. The main limitation here is that eConnect only accepts work‑table entries; it does not provide a mechanism for inserting open or historical records directly into GP’s master tables. Thus, an additional layer of logic is required if the goal is to maintain an up‑to‑date customer master that reflects every change made in RMS. Despite this, eConnect remains a popular choice for retailers that already use .NET for other integrations and that need to orchestrate data flows between multiple systems.

Another powerful avenue is the use of SQL stored procedures. By writing custom T‑SQL scripts that read from RMS staging tables and insert into GP’s target tables, organizations can bypass many of the limitations of the native connector. Stored procedures give administrators fine control over data validation, transformation, and sequencing. For example, a stored procedure can be written to read a batch of purchase orders from RMS, convert the vendor codes, and insert them into the GP PO header and line tables, all within a single transaction. The main requirement is a deep understanding of both RMS and GP database schemas. Knowledge of table naming conventions - such as RM00101 for the customer master in RMS or SOP30200 for historical sales orders in GP - is essential to avoid accidental data corruption. When used carefully, stored procedures can handle hundreds of thousands of transactions per day, matching or even exceeding RMS’s own throughput limits.

Data Transformation Services (DTS) also plays a valuable role in many integration scenarios. DTS packages can extract data from RMS, transform it according to business rules, and load it into staging tables in GP. The visual design surface of DTS makes it accessible to developers who prefer drag‑and‑drop interfaces over hand‑written code. In addition, DTS can be scheduled to run at regular intervals, providing a scheduled data movement solution that can be integrated with existing ETL workflows. Because DTS can also consume or produce XML files, it serves as an intermediary when a retailer wants to expose RMS data to external partners through web services or EDI interfaces.

Custom screens built with Dexterity offer an integrated user experience within Great Plains. By creating a new screen that allows users to set RMS store identifiers, map transaction types, and trigger the integration manually, organizations can hide the complexity behind a familiar GP UI. The key to success here is to develop a separate screen rather than modifying an existing one, which helps preserve upgradeability when GP releases a new version. Dexterity also supports VBA and ADO, enabling developers to add custom buttons or logic directly to the screen. While Microsoft is gradually phasing out Dexterity support in favor of newer development frameworks, it remains a viable option for businesses that rely heavily on GP’s internal user interface.

Beyond Microsoft’s own tools, several third‑party vendors provide comprehensive RMS‑to‑GP integration packages. One prominent example is the solution co‑developed by LightEdge Solutions and Alba Spectrum Technologies. This product is engineered to synchronize receivables and purchase order data between RMS and GP, allowing multiple RMS stores to post to one or more GP companies. The core of the integration is a set of SQL insert statements that feed data directly into GP’s staging tables, achieving throughput that matches RMS’s maximum capacity. Because the solution is designed for RMS Headquarters databases, it can handle large volumes of transactions without the need for intermediate files or manual intervention. In cases where the retailer prefers to use a store‑level database, the same logic can be adapted with minimal changes.

At Alba Spectrum Technologies, the integration product is further customized to meet each client’s specific needs. The team writes bespoke code, refines mapping logic, and fine‑tunes performance parameters to ensure that the data flow aligns with the retailer’s operating rhythm. The ability to map multiple RMS stores to a single GP company - or vice versa - provides a level of flexibility that is hard to achieve with the native connector. Clients can also request enhancements such as real‑time posting, exception handling dashboards, and audit trails that capture every step of the data journey.

In sum, the choice of integration strategy hinges on transaction volume, the need for real‑time data, and the complexity of the retailer’s data model. For low‑volume, straightforward GL and PO posting, the out‑of‑the‑box connector may suffice. As demands grow, organizations can scale upward by adopting Integration Manager, eConnect, stored procedures, DTS, or Dexterity screens. For the most demanding scenarios - especially those requiring high throughput, multi‑store mapping, and robust exception handling - investing in a third‑party solution like the LightEdge–Alba Spectrum package often delivers the best return on investment. By evaluating these options against their own operational requirements, retailers can build an integration architecture that supports growth while maintaining data integrity across both RMS and Great Plains.