HubSpot + Sage 100 integration

Sage 100 still carries its MAS 90 name in places: the ODBC driver is the MAS 90 4.0 driver and the program folder is still called MAS90. Since the 2023 version Sage has sold it as a single product with three installation approaches, the ones formerly called Standard, Advanced and Premium: stand-alone or LAN on ProvideX, client and server on ProvideX, and client and server on Microsoft SQL Server. All three install on Windows, on a server the business runs itself or has a partner run.

What these businesses ask for is specific. Put order history and open balances on the HubSpot company, and stop sellers creating customers that the bookkeeper then keys into Sage 100 a second time, under a slightly different name.

The complication is access. Sage 100’s documentation offers no REST API. What Sage documents is three ways in: the ODBC driver, which reads the data files as tables; Visual Integrator, which imports and exports through jobs; and the Business Object Interface, a COM interface to Sage 100’s own programs. All three need something running beside Sage 100 on Windows, so every design starts with where that agent lives, who is allowed to install it and who keeps it running after go-live.

Five tables carry it. AR_Customer becomes the HubSpot company, AR_CustomerContact the contacts, SO_SalesOrderHeader the deals, AR_InvoiceHistoryHeader a custom object for invoices, and CI_Item the products. The first build copies in one direction only: Sage 100 is the ledger, and a won deal that should become an order is a second phase.

Keys are composite, and the obvious one is wrong twice. A customer’s primary key is the AR division number plus the customer number, so the customer number alone can repeat across divisions. An invoice in history is keyed on the invoice number plus a header sequence number, so the invoice number alone is not a safe key either. We store the full key in a custom HubSpot property.

Change detection follows the same file layouts. Master and header tables such as AR_Customer, SO_SalesOrderHeader and CI_Item carry a last update date and time, while detail tables such as SO_SalesOrderDetail do not, so order lines are re-read with their header whenever it changes.

A small agent installed beside Sage 100 does the reading, table by table, on a watermark for each. On the ProvideX editions the agent reads through the ODBC driver; on the SQL Server edition it reads the database with a read-only login. Each run selects rows whose update date and time are past the table’s watermark and posts them over outbound HTTPS to our sync service, so nothing opens an inbound port on the Sage 100 server.

The service upserts into HubSpot in batches keyed on the composite keys, then links deals and invoices to their companies in a second pass. The service, not the agent, keeps each table’s watermark, so a reinstalled agent starts where the last run ended, and a replay re-reads one table over a date range.

A write back, when it comes, goes through the Business Object Interface on the same server. The agent creates a session, sets the company and logs on as a Sage 100 user with a narrow role, then writes the order through SO_SalesOrder_bus, the business object Sage lists for its sales order table.

On Sage 100 the edition and the server decide more than the object list. Every route puts software inside your network to reach the ODBC driver or the database, and the real question is whose software that is.

A custom integration puts our agent there, reads the composite keys correctly, and is the only route here that can write back through the Business Object Interface. Managed sync on Skyvia puts Skyvia’s agent there instead: it connects to on-premises SQL Server databases and to ODBC sources, so it can copy customers, orders and invoices one way on a schedule, as the Skyvia page describes. Commercient’s Sage 100 app is the packaged third route, run and supported by Commercient.

Sage X3 shares the name and little else: it has a web API and a different design, set out on the Sage X3 page.

1. Scope. We confirm the edition and version, who looks after the server, which divisions exist and whether sales order history and deleted orders are retained.
2. Architect. We fix the tables, the composite keys, the watermark per table and where the agent runs, and decide whether a write back belongs in phase two.
3. Build. The agent and service are built against a copied company, which carries real customer data and is secured like production, with a HubSpot sandbox and a portal allowlist.
4. Validate. We compare customer counts and the open order book with a Sage 100 report and settle every difference with the bookkeeper.
5. Operate. We watch each table’s run history, keep replay available, and repoint and retest the agent at every yearly upgrade.