Return-Order Product Sync in Practice: A Single-Strategy Implementation from CRM to MySQL

What This Strategy Solves

In a real retail supply chain, the returns process usually spans two systems: the CRM (represented here by Fxiaoke) handles frontline order entry and approval, while the MySQL data warehouse drives downstream financial reconciliation and inventory write-offs. Once a return order is finalized in the CRM, its line items must flow into MySQL immediately — otherwise reconciliation drifts. In practice, the common pain points are: headers sync but lines are missing, or lines sync but gifts, specs, and unit prices don't match, leaving the two sides unreconciled three months later.

The goal of this single strategy is to take the return-order products (ReturnedGoodsInvoiceProductObj) from Fxiaoke and land them completely and accurately into the MySQL return-order line table on a scheduled cadence.

Data Flow and Field Mapping

The overall flow is: Fxiaoke (source) → Qeasy Data Integration Platform (middleware) → MySQL (target).

The source queries the return-order product object via WebAPI (/cgi/crm/v2/data/query) using POST; the target writes in batch via SQL (batchexecute). Below is the core field mapping:

Two mapping patterns are common among Qeasy customers: centralized code mapping (product/customer codes in one mapping table, changed in one place), and phased header-then-line sync (load the header first and use its ID when pulling lines). This strategy uses the latter to avoid orphan lines.

How to Configure It in Qeasy

In the Qeasy Data Integration Platform, this strategy is a typical "source WebAPI + target SQL" combo. Key configuration points:

1. Source connector: Choose the Fxiaoke adapter, set dataObjectApiName = ReturnedGoodsInvoiceProductObj, and configure currentOpenUserId as the operating user so API permissions stay consistent.
2. Target connector: Choose the MySQL adapter with batchexecute mode; enable idCheck=true on the primary key id to prevent duplicate writes.
3. Field mapping: Drag source fields onto target fields in the mapping canvas, using {{field}} placeholders, e.g. {{returned_product_price}}. Keep the original precision for float fields — do not round in the middleware.
4. Deduplication & idempotency: Use returned_goods_inv_id + product_code__c as the business unique key, add a unique index on the target table, and prefer UPDATE over INSERT on duplicates.

Implementation Steps

On customer sites we usually run a three-step rollout:

1. Full trigger (initialization): The night the strategy goes live, manually run a full sync to backfill all existing return-order lines into MySQL. This runs only once to establish the baseline.
2. Incremental starting point: Add a "last-modified-time > last successful sync time" condition to the source query, using the timestamp from the full run as the incremental start point.
3. Scheduling cadence: Set the source crontab to */10 * * * * (every 10 minutes), and the target to 3-59/10 * * * * (offset by 3 minutes) so both ends don't contend in the same window. This incremental-plus-full dual-track pattern is common among Qeasy customers — incrementals run daily, and one click falls back to a full sync on incidents.

After go-live, run in a shadow environment for 24 hours, reconcile row counts and amounts on both sides, then cut over to production.

Lessons from the Field

The most common pitfalls with this strategy:

1. Header not loaded first. Pulling lines before the header lands causes returned_goods_inv_id__r to have no foreign key in MySQL. Always run the header strategy first.
2. Float prices truncated in middleware. returned_product_price is a float on the source; JSON serialization can lose precision. Explicitly declare high-precision numeric in the mapping, or use DECIMAL in MySQL.
3. Gift / custom fields silently dropped. Custom fields like field_BMa8W__c are easy to miss during initial setup, breaking later "is gift" reporting. Always cross-check against the full source schema.
4. Mismatched operating-user permissions. A wrong or expired currentOpenUserId returns empty data without raising an error, making it painfully slow to find. Add "row count + sample verification" after each sync.
5. Primary-key conflicts on incremental writes. If only the auto-increment id is the primary key, incremental syncs collide. Always add a unique index on the business key too.

When to Use and When Not To

Use when: CRM is the entry point for returns, MySQL is the accounting/reporting base, single-table volume is under tens of millions, and minute-level latency is acceptable — typical for retail and distribution.

Don't use when: The returns process is fully closed-loop inside the ERP (no CRM involvement), or you need second-level real-time inventory write-back. In the latter case, use a message queue + CDC design instead.