Sales Order Approval DingTalk Notification: A Practical Guide to Approval Aggregation Push Based on MySQL + DingTalk Robot
What This Strategy Solves
In the sales order approval workflow, approvers often don't check the system all morning, and pending orders pile up to dozens before they react, dragging out approval cycles. This time we want to push the pending approval counts aggregated by approver in MySQL to the approvers' DingTalk on a schedule via the DingTalk robot, achieving "reminder arrives when the person arrives." Essentially, this is not structured data synchronization but a lightweight integration of "business query result → instant messaging message."
Data Flow and Field Mapping
The source side is a flat result aggregated by user_id in MySQL, where each row represents an approver and their pending approval count; the target side is one call to the DingTalk robot batchSend API, sending one Markdown message. There are no detail rows, no nested structure—a single-layer mapping is sufficient.
| Source Field | Target Field | Mapping Type | Conversion Rule | Business Description |
|---|---|---|---|---|
| — | robotCode | CONSTANT | Fixed robot code | Identifies the robot used to send the message |
| userid (CASE output) | userIds | DIRECT | {{userid}} | JSON array-formatted recipient list |
| — | msgKey | CONSTANT | sampleMarkdown | Markdown message template |
| user_count | msgParam.text | DIRECT | {{user_count}} | Number of pending approval orders |
id (now()) | msgParam.text | DIRECT | {{id}} | Current time displayed in the message |
| Multi-field combination | msgParam | TRANSFORM | _function CONCAT(...) | Concatenated into complete Markdown text |
userid is not a single value but a JSON array string generated by the CASE WHEN ... ELSE CONCAT(...) END in the source SQL: specific approvers will have an additional CC person appended, while other approvers only have a fixed CC person, ultimately forming the "approver + CC" recipient logic. This step is the most error-prone part of the entire strategy.
How to Configure on Qeasy
We use the Qeasy data integration platform to handle this link, with configuration divided into three parts:
Source side: Choose MySQL platform, API type as WebAPI/QUERY. In main_sql, use SUBSTRING_INDEX to split comma-separated strings like audit_user into single rows, then aggregate with GROUP BY user_id. Dynamic syntax like limit :limit offset :offset should be changed to placeholder binding—this is the unified approach we recommend customer teams follow for easier future maintenance.
Target side: Choose DingTalk platform, API select /v1.0/robot/oToMessages/batchSend. robotCode uses a constant, userIds directly references source-side {{userid}}, msgParam uses _function CONCAT to concatenate the title, user_count, and id into complete JSON text. A common practice in Qeasy is to extract such "code mapping" into a centrally maintained table rather than scattering it across strategies—subsequent employee departures or job number changes only require modification in one place.
Scheduling: Configure crontab on both source and target sides. Source side: 1 8,10,12,14,16,18,20 * * *, target side delayed by 1 minute to avoid target pulling before source query completes.
Implementation Steps
Pushed forward in three phases, this is exactly how we ran it at a customer site:
Phase 1: Incremental starting point reconciliation. Run a full pull once in the test environment first. It's fine if all approvers that day receive a "0 pending approvals" message—the key is to verify that the recipient list, timestamp, and Markdown rendering are all normal.
Phase 2: Scheduled execution. Switch crontab to trigger at 8 AM for the first time, then observe whether the data at 10 AM and 12 PM matches expectations. We usually recommend customers run "incremental and full parallel" for three days first, confirming no duplicates or missing sends before switching to pure incremental.
Phase 3: Stable period. Change now() in SQL to DATE_FORMAT(NOW(), '%H:%i') to only output hours and minutes, making messages more compact; meanwhile, centralize fixed CC person codes into the mapping table for easier administrative adjustments.
Pitfall Review
useridis not a single value but a JSON array. The first time we configured it, we passed{{userid}}directly as a string, resulting in DingTalk returning a parameter error. The safe approach is to manually assemble JSON strings in the source-side CASE output and pass them through directly on the target side.audit_usersplitting misses people. If there are more than 10 approvers (the numbers table forSUBSTRING_INDEXonly generates 1-100), those beyond will be lost. For multi-level approval scenarios, the numbers table needs to be expanded to 1-500.now()with seconds makes messages verbose. A typical mistake is stuffing the full timestamp into Markdown, which looks exhausting to approvers. Switching toHH:mmformat significantly improves the experience.- Special approver CC logic is hardcoded. Hardcoding exceptions like
0119001466201065096in SQL means personnel changes require SQL modifications. We later advised customers to extract this part into a "centralized code mapping management" table—adding approvers goes through configuration without touching code. - Source and target crontab at the same time. If source runs at 8:00 and target also pulls at 8:00, the target may pull an empty set. Offsetting source and target by 1 minute is the safe approach.
Applicable and Non-applicable Scenarios
Applicable: Approvers have DingTalk accounts, the enterprise has enabled group robots, and pending approval count is a low-frequency aggregation dimension (by person by day). Not applicable: Scenarios requiring point-to-point approval result replies or carrying detail rows (each order)—those should use work notifications or approval OA rather than group robot Markdown.