Qeasy Cloud
Get Started

Sales Order Approval DingTalk Notification: A Practical Guide to Approval Aggregation Push Based on MySQL + DingTalk Robot

· 冯潇· Integration Solutions· 10 views· 4 min read

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 FieldTarget FieldMapping TypeConversion RuleBusiness Description
—robotCodeCONSTANTFixed robot codeIdentifies the robot used to send the message
userid (CASE output)userIdsDIRECT{{userid}}JSON array-formatted recipient list
—msgKeyCONSTANTsampleMarkdownMarkdown message template
user_countmsgParam.textDIRECT{{user_count}}Number of pending approval orders
id (now())msgParam.textDIRECT{{id}}Current time displayed in the message
Multi-field combinationmsgParamTRANSFORM_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

  1. userid is 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.
  2. audit_user splitting misses people. If there are more than 10 approvers (the numbers table for SUBSTRING_INDEX only generates 1-100), those beyond will be lost. For multi-level approval scenarios, the numbers table needs to be expanded to 1-500.
  3. now() with seconds makes messages verbose. A typical mistake is stuffing the full timestamp into Markdown, which looks exhausting to approvers. Switching to HH:mm format significantly improves the experience.
  4. Special approver CC logic is hardcoded. Hardcoding exceptions like 0119001466201065096 in 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.
  5. 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.

Original content. Please credit the source when reposting: https://www.qeasy.cloud/insights/solutions/strat-mysql-dingtalk-4900-sz-xs-8f588ec0

Comments