Practical Tutorial: Incremental BOM Sync from Kingdee Cloud to MySQL

What This Strategy Solves

Pushing the bill of materials (BOM) from Kingdee Cloud down to a MySQL database looks like a simple "pull + write" job, but BOMs are tree-structured and every entry represents a parent–child material relationship. If the code mapping is sloppy and the incremental boundary is loose, the two systems drift apart within a few months and production gets hurt. This strategy uses an incremental + organization filter to reliably pull ENG_BOM rows for one specific organization into MySQL, becoming the data foundation for downstream MES and BI.

Data Flow and Field Mapping

The overall chain is Kingdee Cloud → Qeasy (the easily-cloud integration platform) middleware → MySQL. The source uses FormId ENG_BOM and the executeBillQuery API. Key field mappings (target table tp_jd_entry):

Source fields FDocumentStatus and FForbidStatus are not mapped to the target. They can be added via source-side pre-extraction or a follow-up strategy when needed.

How to Configure It in Qeasy

In the Qeasy integration platform, the strategy boils down to a "source read + target write" pair.

• Source (read): A Kingdee Cloud connector, API executeBillQuery, FormId ENG_BOM. In the request body, flatten nested fields with the {{FBILLTYPE.FNumber}} / {{FMATERIALID.FNumber}} syntax so the target table receives a flat row.
• Filter: FModifyDate>='{{LAST_SYNC_TIME|dateTime}}' and FUseOrgId.fnumber='TP000', which is both incremental and org-isolated.
• Pagination: Limit=2000, StartRow={{PAGINATION_START_ROW}}, to avoid hammering the source.
• Target (write): A MySQL connector, SQL execution, idCheck=true with FENTRYID as the uniqueness check, REPLACE INTO tp_jd_entry, batch size 200.
• Code mapping: This strategy does not use cross-strategy _findCollection. All code values are pre-extracted at the source via the {{object.property}} syntax and managed in one place, which makes auditing and field changes much easier — a pattern many Qeasy customers adopt to keep code mapping centralized.

Implementation Steps

Three phases: get it flowing, get it stable, then tune the cadence.

1. Prepare the target table and primary key Create tp_jd_entry in MySQL with FENTRYID as the primary key and proper indexes on BOM code, parent material code, and child material code. Truncate the table for a cold start and verify field types against the actual BOM values.

2. Configure source and target, then run a full sync Temporarily drop the incremental filter, run a full backfill, and verify that field mapping, org filtering, and pagination are all correct. Once the full sync is green, re-enable the FModifyDate filter and switch to steady state.

3. Scheduling (incremental + sequencing)

   • Source read: */7 * * * *, every 7 minutes
   • Target write: 3-59/7 * * * *, offset by 3 minutes so writes always follow reads
   • Monitor pulled rows, written rows, and failures; alert on anomalies

A "full sync trigger" switch is also recommended so you can re-run end-to-end after a major business change or organization switch — a typical dual-track pattern (incremental + on-demand full) that Qeasy customers commonly use.

Pitfalls From the Field

1. BOM is a tree — don't use the header FID as the key A classic mistake is upserting on the header FID, which keeps only the first row of any multi-level BOM. The correct key is the entry ID FTreeEntity_FENTRYID so each parent–child relationship is its own row — which is exactly why the target table uses FENTRYID as the primary key.

2. If you don't flatten nested objects at the source, the target breaks Kingdee returns FBILLTYPE, FGroup, etc. as {FNumber, FName} objects. If you forget to flatten them in the source request with {{FBILLTYPE.FNumber}}, MySQL will end up storing raw JSON strings and any downstream join or aggregation is dead. Flatten every *_FNumber and *_FName during source configuration.

3. Putting the org filter in the wrong place FUseOrgId.fnumber='TP000' belongs in FilterString, not in a post-processing script. If it sits downstream, you've already pulled data for other organizations, wasting API quota and risking a bug in the script that lets some rows through. The right move is to push the filter all the way to the source.

4. Identical cron expressions cause reads to race writes When the source and target share */7 * * * *, a new read can start before the previous write finishes, producing racy state. The safe pattern is source at */7 and target at 3-59/7, so writes lag reads by three minutes and the sequence is always stable.

5. No uniqueness on the target table → silent duplicates idCheck=true plus REPLACE INTO is a double safeguard. If someone accidentally turns idCheck off and the table has no unique index, duplicates accumulate quietly and become very hard to clean up. Always add a unique index at the DB level on top of the platform-level check.

When to Use It and When Not To

Use it when Kingdee Cloud is the ERP master for BOM data, downstream MES/BI/homegrown systems need a stable BOM master, and there is a clear multi-organization isolation requirement whose volume fits inside a 7-minute window.

Don't use it when you need to aggregate BOMs across multiple accounting books (then go with _findCollection for centralized mapping), when the source BOM is heavily customized and needs extensive _function transforms, or when you need sub-second latency — in which case a change-notification model is a better fit than polling.