Fifteen years of customizations and a consultant for every change

Zoho is capable software, and it can be customized a long way. That was the trouble. Each time the wholesaler needed to track something new, the consultant added a field, a module or a script. Each change solved the problem in front of it. After 15 years the system held the company's history and every workaround made along the way, and only the consultant fully understood how they fit together.

Much of the data also lived outside Zoho: in XLS spreadsheets and CSV exports, in database backups, in PDFs, in Dropbox folders and on web pages. Answering a question about a customer or a product could mean checking several of them.

Illustration titled Fifteen years of patches. A Zoho screen is covered in taped-on notes labeled custom field, custom module and script patch, dated from 2010 to 2024, and a consultant stands beside it. Loose files sit around it: XLS and CSV files, PDFs, a database backup, a Dropbox folder and a web page. A bar at the bottom reads: Only the consultant knew how it all fit together.
Figure 1. After 15 years the business depended on Zoho customizations, a consultant, and files kept outside Zoho.

The usual way out is to match each old field to a field in the new system and copy the data across. That moves the workarounds too. The new system starts with the same 15 years of patches, and the business pays to migrate its problems. We explain the alternative in Don't copy your old database: design the data model first.

We build and change CommerceServer with Buffaly, our own software engineering system. Here is what we did for the wholesaler, in the order we did it.

Step 1: Describe the business before moving any data

The first step took a couple of days. We built an ontology of the wholesaler's data: a description of the things the business deals with and how they relate, written in the form the data should take. It names customers, products, vendors, orders, purchase orders and invoices, and states the links between them, such as which purchase order filled which sales order. What an ontology is, explained for business owners covers the idea in more detail.

Flooring shows why this matters. A product is a style and color in a vendor's line. An inventory piece is a specific roll or cut of that product in a warehouse. If an old system blurs the two, a field-to-field copy blurs them in the new system as well. In the ontology they are separate things with a defined link.

Every source is then mapped to that structure, whatever its format. Each column in an XLS spreadsheet or CSV export, each table in a database backup, each field on a PDF and each item on a web page is assigned to the part of the model it fills. Each source gets its own mapping, so once the mappings exist, everything lands in the same model and the original format stops mattering.

Illustration in three parts. On the left, messy sources: Zoho records, XLS, CSV and PDF files, a database backup, a Dropbox folder and a web page. In the middle, all of them feed one ontology, the ideal data model, which links customer, order, invoice, product, inventory piece and vendor. On the right, CommerceServer, labeled: schema changed to fit the model.
Figure 2. Every source, whatever its format, was mapped into one ontology, and the CommerceServer schema was changed to fit that ontology.

We have used this method at much larger scale. Intelligence Factory's published case study on integrating dozens of EHR systems describes data from nearly three dozen EHR and EMR systems mapped to one internal ontology. People reviewed the initial mapping, and the mappings for each source were kept separate from the rules that use the data for eligibility, billing and reporting.

Step 2: Change the CommerceServer schema to fit the model

Next we mapped the ontology to the CommerceServer schema, the tables and fields the system stores data in. Where the schema did not match how the wholesaler works, we changed the schema.

This is the step where 15 years of patches ended. A custom field that existed because Zoho had nowhere better to put something becomes a proper field or relationship with one meaning. A script that enforced a business rule becomes a rule stated in the model. The workarounds are left behind because the new structure no longer needs them.

Step 3: Import 15 years of records in minutes, then rerun until nothing differs

An automated pipeline then loaded 15 years of data, hundreds of thousands of records, in minutes. The speed matters for a practical reason. When an import takes minutes, it can be run again after every correction: run it, compare the result with the source, fix the mapping and run it again from the start. Nobody edits records by hand in the new system, so every fix lives in the pipeline and survives the next run. Why your data import should be rerunnable explains the approach.

On this project every field brought across was compared with a backup of the old data. That was 293,986 fields, and the comparison finished with zero differences. Getting to zero meant tracing 43 exceptions back to source documents.

What differedCountHow it was resolved
Order dates35Recovered from the time the original order email was received
Products5Traced through vendor part numbers
Other3Traced to their source documents one by one

The dates are a good example of how this works. For 35 orders the correct date was missing from the old records but present in the original order emails, so the date was taken from the time each email was received. Invoices that could not be proven against a source document were kept and labeled as historical source invoices. Every record was kept, and only records proven against a source are presented as verified.

Illustration titled Import, compare, fix the mapping, run again. Fifteen years of records pass through an automated import that runs in minutes. The result shows 0 differences across 293,986 fields compared with the old data. A loop arrow labeled fix the mapping, rerun from scratch leads back to the start. A panel lists 43 exceptions traced to source: 35 order dates, 5 products and 3 others.
Figure 3. A full import takes minutes, so every mapping fix was followed by a complete rerun and a field-by-field comparison with the old data.

Step 4: Build every feature and report the business asked for

Every feature and report the wholesaler requested was built. The business now runs its own custom workflow, custom business rules and custom reporting, all supported in CommerceServer. It never had that in 15 years on Zoho.

The largest new piece was a B2B web platform. The wholesaler's customers log in, check inventory and submit purchase orders themselves. Because the platform runs on the same CommerceServer data the staff use, the stock a customer sees is the stock in the system. A B2B ordering portal for distributors describes what such a portal needs from the data underneath it, and how a negotiated quote becomes an order in CommerceServer walks through one order from quote to supplier purchase orders.

Illustration titled One set of data for staff and customers. On the left, staff use the staff system for orders and reports. On the right, customers use a B2B customer portal to check inventory and submit a purchase order. Both connect to the same CommerceServer data in the middle, under which a line reads: The stock a customer sees is the stock in the system.
Figure 4. Staff and B2B customers work from the same CommerceServer data, so the portal shows real stock.

Step 5: Run both systems for a month without entering anything twice

Parallel runs usually fail because of double entry. Staff are asked to enter every order in both systems, they fall behind, and the new system's data stops being trustworthy before cutover.

This one asked staff for one extra step. They kept working in Zoho and emailed the files Zoho produced. Buffaly agents read each file into the ontology, checked it and wrote it to CommerceServer.

When a file arrived in a layout the agents had not seen, or with a case the existing tools did not handle, Buffaly wrote a new tool for it and used it in the same session. That is ordinary behavior for Buffaly. In Buffaly Builds Its Own Tools, an analysis of 1.2 million messages and over 380,000 tool calls found that 70% of newly created tools were used in the same session they were written.

The month doubled as constant testing. Every emailed file was a real transaction from the business, run through the new system. Problems surfaced while Zoho was still in use and were fixed before cutover. By the end of the month the team could not stand going back to the old Zoho system. The parallel run: staff email files from Zoho and agents enter them describes the setup in detail.

Illustration titled One month, two systems, no double entry, in four steps. 1, staff work in Zoho and email the file. 2, Buffaly agents read it into the ontology, with a note that a new layout gets a new tool written by Buffaly. 3, the agents check the records, with a note that problems found are fixed before cutover. 4, the records are written to CommerceServer. A bar at the bottom reads: Every emailed file was a real test of the new system.
Figure 5. The parallel run. Staff kept working in Zoho, and Buffaly agents did the entry in CommerceServer.

What happened next

A sister company started its own migration right away, and it was so easy that it was nearly forgotten. What we learned in the first migration had become the tools for the second: the import method and the checks before and after each load. The second migration was the easy one tells that story, and the case facts list this migration's numbers in one place.

What to check before you replace your own system

You can apply the same checks to your business before you talk to any vendor:

  • List every place your business data lives: the main system, XLS and CSV files, database backups, PDFs, shared folders and any web pages the business relies on. Each one is a source that has to be mapped.
  • Find the customizations. For each custom field, module or script, write down the business need it serves and who can change it. Those needs belong in the new data model, and the custom fields that served them can usually be retired.
  • Look at the migration plan you have been given. If it is mainly a spreadsheet of old fields matched to new fields, it is a copy job, and it will move the patches.
  • Ask how long one full import takes and whether it can be rerun from scratch. If the answer is days, corrections will end up being made by hand.
  • Ask how the new system will be tested with real documents before cutover, and who does the entry while both systems run.

Moving off Zoho or another heavily customized system

If your business runs on years of customizations and a consultant you call for every change, we can do the same work with your data: describe the business, change CommerceServer to fit it, move your full history and run both systems until your team is ready to switch. Tell us about your current system, or read how full service works.