HubSpot + Sage 50 integration

Sage 50 is one name on two North American products. The Canadian edition, formerly Simply Accounting, keeps each company in a MySQL database made of an SAI file and an SAJ folder, and Sage’s Connection Manager, installed on the Windows computer that stores the data, controls every connection to it. The US edition, rebranded from Peachtree in 2013, runs on the Actian Zen database engine, which replaced Pervasive in its 2020 version. Both are sold as Pro, Premium and Quantum.

Sage describes the US product as accounting software for small businesses with in-house bookkeepers, and the request that follows is a modest one. Sales wants the balance, the last invoice and what is on order on the HubSpot company. The bookkeeper wants sellers to stop creating customers that then have to be keyed into Sage 50 a second time, under a slightly different name.

The edition decides the build. The two editions expose different databases, grant third-party access in different ways and ship different SDKs, so the same question gets two answers. In the US there is a third case as well: Sage 50 Cloud, the version Sage hosts on its own servers, where an integration is possible only once Sage has approved it. Scoping starts by finding out which of the three a business actually runs, and on which machine.

Five records carry a first build, moved one way into HubSpot. Customers become companies, their contacts become contacts, sales orders become deals, sales invoices land on a custom invoice object and inventory items become products. On the US edition they come from CUSTOMER.DAT, CONTACTS.DAT, JRNLHDR.DAT and LINEITEM.DAT, the tables Sage documents for reporting over ODBC. The Canadian edition describes its tables in a data dictionary that ships with the product, which we read in discovery.

The obvious key is wrong. On the US edition the customer ID a bookkeeper sees can be changed with Sage 50’s Change ID feature, while CustomerRecordNumber is unique, never changes and is what every other table links on. Items carry ItemRecordNumber for the same reason. On the Canadian edition we key on the table’s own id from the data dictionary, never on a customer name.

Transactions need a filter as well. On the US edition every journal shares one header table, told apart by a journal number: 3 for sales, 11 for sales orders and 12 for quotes. The invoice number is a reference Sage does not document as unique, so the key is PostOrder, which Sage makes unique to each transaction. The US customer table also holds prospects, flagged by IsProspect, which belong in HubSpot as leads rather than as customers.

An agent on the Windows machine that holds the company data reads the edition’s ODBC source: the MySQL Connector source Sage 50 creates for each Canadian company, or the Pervasive ODBC interface in the US. Sage’s US guidance is that its ODBC connection does not work from 64-bit programs, and its Canadian guidance says the same of Microsoft Access, so the agent is a 32-bit program.

In Canada it signs in as a Sage 50 user granted read rights for third-party products. In the US it uses the Peachtree ID and the password set for access from outside Sage 50, and that access is read-only. Each run selects rows past the table’s watermark where a stamp exists, such as the US customer table’s LastSavedAt and the header table’s LastPostedAt, and compares the rest by row hash.

The agent posts rows over outbound HTTPS to our sync service, which holds the watermarks, upserts into HubSpot on record numbers and runs the association pass. A later write back runs through the agent and Sage’s SDK, never straight into the database. The agent pattern is the one on the Sage 100 page; the drivers, rights and keys are Sage 50’s own.

On Sage 50 the first question is who runs the software. If the company data sits on your own Windows machine, either edition can be reached, and a custom integration is the route we recommend: our agent beside the data, ODBC for reads, record numbers for keys and Sage’s SDK for anything written back.

Commercient’s apps are the packaged alternative, one per edition, run and supported by Commercient. They suit a standard customer and invoice copy with nobody of yours on call.

On Sage 50 Cloud in the US, neither route applies unless Sage agrees. Sage lists the third-party integrations its hosted version accepts, and a request for any other app only asks Sage’s product team to consider it. What remains is Sage 50’s CSV export, which a person runs inside the hosted desktop and copies out, so it suits a one-time load better than a sync.

1. Scope. We confirm the edition, the version and the tier, whether Sage hosts it, which machine holds the company data, and whether anything should be written back.
2. Architect. We fix the tables, the record-number keys, the journal filters, the watermark per table and where the agent runs.
3. Build. The agent and service are built against Sage’s sample company and a copy of the live data, signed in as a third-party user of their own, with a portal allowlist from the first commit.
4. Validate. We compare customer counts and open invoice totals with a Sage 50 report, and settle each difference with the bookkeeper.
5. Operate. We watch each table’s runs, keep replay available, and retest the agent after each Sage 50 update, since the US subscription requires the current version.