Export Data from Source System
Someone manually runs a report or export in Sage 100, Membrain, or the PHP admin panel. Exports to CSV. Different export formats per system, per data type.
Open in Excel, Clean & Reformat
Delete unnecessary columns, fix formatting, standardize date formats, correct encoding issues, handle special characters, merge or split fields to match target system requirements.
Consolidate Multiple Sources
If the target needs data from multiple systems (e.g., the website needs products from Sage AND descriptions from SharePoint), manually merge files. Match on customer name or part number - no shared unique IDs.
Import into Target System
Upload the cleaned CSV into the target system's import tool. Each system has different import requirements, field limits, and error handling. Some imports overwrite, some append, some do nothing useful on error.
Spot-Check & Verify
Manually open a few records in the target system to make sure the import worked. Compare against source. No systematic validation - just eyeball checks on a handful of rows.
Fix Errors & Re-Import
When errors are found (wrong prices, missing customers, duplicate records), go back to step 2. Fix the CSV, re-import, re-verify. Sometimes the fix creates new errors. Cycle repeats.
Source System Triggers Event
A customer is updated in Sage, a deal closes in Membrain, an order comes through the website, or pricing changes. The integration hub detects the change automatically via API polling or webhooks.
Hub Validates & Transforms
The integration hub applies the field mapping dictionary automatically. Validates data types, required fields, format rules. Transforms between system-specific formats. All rules are documented and version-controlled.
Hub Routes to Target System(s)
Uses shared unique IDs (Sage CustomerNo, ItemNo) to match records across systems. Creates or updates in the target system via API. One change in the source can cascade to multiple targets simultaneously.
Automatic Validation & Logging
Every sync is validated automatically - record counts, field checksums, relationship integrity. Full audit trail: what changed, when, from where, by whom. Every transaction logged with before/after snapshots.
Exception Queue (Human Review)
Only records that fail validation or cannot be auto-matched land in a human review queue. Dashboard shows what needs attention, why it failed, and suggested fixes. Humans review exceptions - not every row.
| Dimension | Current Process | Integrated Process | Impact |
|---|---|---|---|
| Time per sync cycle | 1.5 - 4.5 hours | 5 - 15 minutes (exceptions only) | ~85-95% time reduction |
| Sync frequency | Weekly / on-demand (delayed by manual effort) | Real-time or scheduled (hourly/daily) | Data freshness: days to minutes |
| Error detection | After the fact - when someone notices | Immediate - validation on every record | Errors caught before they reach users |
| Error recovery | Re-do the entire export/import cycle | Fix the specific record, auto-retry | Minutes vs. hours per error |
| Audit trail | None - no record of what changed or when | Full log: before/after, timestamp, source | Compliance-ready, debug-friendly |
| Cross-system matching | Manual name matching - no shared IDs | Shared unique IDs (CustomerNo, ItemNo) | Eliminates duplicate and orphan records |
| Scalability | Linear - more data = more manual hours | Flat - 100 records or 10,000, same effort | Growth without adding headcount |
| Staff dependency | 1-2 people know the process - bus factor risk | Documented, automated, team-independent | No single point of failure |
| Pricing accuracy (website) | Stale until next manual update | Synced within minutes of Sage change | Customers always see correct prices |
| Order processing | Website order re-keyed into Sage manually | Auto-created in Sage on submission | Faster fulfillment, zero re-keying |