Table Sync

Table sync keeps a datastore in ActiveManage aligned with an external table of record — typically a master data system or a partner's database. It runs scheduled or on-demand jobs that pull (and/or push) changes, applying conflict resolution rules and preserving an audit trail.

Direction Modes

• Pull-only: ActiveManage receives changes from an external source; doesn't push back. Best for master-data systems.
• Push-only: ActiveManage acts as master; pushes changes outward. Best for distributing canonical data.
• Bidirectional: Changes flow both ways. Requires careful conflict resolution.

What Gets Synced

• Inserts — new rows on either side.
• Updates — modifications to existing rows.
• Deletes — soft (mark as deleted) or hard (remove the row).
• Optionally: relationships, attachments, custom fields.

Identifying Rows

Each row needs a stable key that's consistent across both systems. Typical patterns:

• Source system's primary ID stored in a sync_external_id column.
• A natural key (email address, SKU code).
• A purpose-built UUID generated once and copied to both sides.

Worked Examples

• Customer master sync: Salesforce → ActiveManage, pull-only, hourly. Every Salesforce Contact becomes an ActiveManage user.
• Product catalogue distribution: ActiveManage → 3 partner sites, push-only, real-time on every save. Each partner site mirrors the catalogue.
• Order bidirectional: Orders flow between ActiveManage and warehouse-management system. Status updates go in both directions; conflicts resolved by timestamp.

This article walks through configuring a new table sync job, from picking the source to running the first batch and confirming the results.

Step-by-Step

1. Open Architect Panel → Table Sync and click New Sync.
2. Pick the source type:
   • SQL endpoint — JDBC/ODBC connection to a remote database.
   • REST API — call into an external API.
   • CSV / SFTP — fetch a file periodically.
   • Webhook receiver — accept pushes from the source.
3. Provide credentials / endpoint URLs. Test the connection.
4. Map columns: each source column to a target column on the ActiveManage datastore. Optional transforms (string trim, date format conversion, lookup table).
5. Set sync rules:
   • Direction (pull / push / bidirectional).
   • Identifier column.
   • Delete policy (mirror deletes, ignore deletes).
   • Conflict policy (newer wins, source wins, manual review).
6. Schedule:
   • On-demand only (manual triggers).
   • Hourly / daily / weekly cron.
   • Real-time via webhooks.
7. Run a test sync of N rows in dry-run mode. Review the proposed changes.
8. Enable.

Worked Examples

• Salesforce contacts → ActiveManage users: REST source, hourly, identifier = Salesforce 18-char ID, conflict = newer wins.
• Master product CSV from supplier: SFTP source, daily 06:00 UTC, identifier = SKU, conflict = source wins (we're a mirror, not master).
• Webhook-driven inventory: Webhook receiver, real-time, identifier = warehouse SKU, conflict = source wins.
• Bidirectional orders: SQL source, real-time via change-data-capture stream, identifier = order ID, conflict = manual review on any clash.

Bidirectional syncs inevitably hit conflicts: the same row was changed on both sides between sync runs. How you resolve those changes determines whether you keep a consistent picture or whether bad data creeps in.

Available Policies

• Newer wins (timestamp): The side with the most recent update_at timestamp wins. Simple, but assumes clocks are roughly aligned and only one side is authoritative.
• Source wins: External source always takes precedence. Best when external is the master of record.
• Target wins: ActiveManage always wins. Best when ActiveManage is the master and the external is a stale mirror.
• Field-level merge: Each field merged independently — e.g. customer's address taken from external, phone from ActiveManage. Specify per field.
• Manual review: Conflicts queued for a human to decide. Best for high-stakes data.

Detecting Conflicts

The platform stores a hash of the row at the time of the last successful sync. On the next run it compares hashes — if both sides differ from the stored hash, it's a conflict.

Worked Examples

• CRM ↔ ActiveManage: Field-level merge — “last contacted” field comes from CRM; “preferred contact method” from ActiveManage; everything else newer-wins.
• Inventory sync: Source wins — warehouse system is the source of truth on stock counts. ActiveManage shows the value but never modifies it.
• Partner catalogue: Target wins — ActiveManage is the master; partner side gets clobbered if it edits a product.
• Order status: Manual review for any conflicts on payment_status — too consequential to auto-resolve.

The Manual Review Queue

When the policy is “manual review”, conflicting changes land in a queue. Reviewers see both versions side-by-side and pick the winner per field. Decisions are logged.

Tip: Start with “manual review” for the first week of any new sync. Once you see the conflict patterns you'll have evidence for which automated policy fits.