Qeasy Cloud
Get Started

Purchase Order Approval DingTalk Notification: A Practical Guide from MySQL Aggregation to Markdown Messaging

· 高金凤· Integration Solutions· 7 views· 5 min read

What This Strategy Solves (Scenario & Value)

In one manufacturing client's outsourced procurement workflow, approvals were scattered across multiple owners and used to pile up silently — people relied on email cc and manual checks. In a real engagement, we turned "pending purchase-order summary → DingTalk reminder" into a scheduled job: every two hours, MySQL aggregates the pending count by approver, and a self-built DingTalk robot pushes a single Markdown message containing the count, a timestamp, and a "View Details" link. Approvers no longer have to log into the ERP to know how many orders are waiting on them. The core value is shifting from "passively discovering backlogs" to "proactively delivering reminders."

Data Flow & Field Mapping

The pipeline is flat — no nested detail lines: MySQL (source aggregate query) → Qeasy middleware (field mapping + Markdown assembly) → DingTalk robot batchSend (single message body).

The source result is a flat set: each row = one approver + their pending PO count; the target is one JSON message body. Key field mapping:

Source Field (MySQL)Target Field (DingTalk)Mapping TypeConversion RuleBusiness Meaning
—robotCodeCONSTANTFixed codeDingTalk custom robot code used to send purchase approval reminders
useriduserIdsDIRECT{{userid}}DingTalk recipient IDs, JSON array string
—msgKeyCONSTANTFixed value sampleMarkdownMarkdown template type
Multi-field combomsgParamTRANSFORM_function CONCAT(...)Concatenates title, pending count, current time, detail link
user_countmsgParam.textDIRECT{{user_count}}Pending PO count for this approver
idmsgParam.textDIRECT{{id}}Current timestamp generated by source now()

Several things worth noting in the source SQL: pur_status IN ('1','11') selects pending status, pur_type='2' limits to outsourced procurement, is_del='1' filters valid records, and company_code scopes by company. The approver list comes from act_to_form_process.audit_user (comma-separated), which is split into individual users via a numeric sequence and then aggregated — a typical pattern for "approval-flow reminder" strategies.

How to Configure in Qeasy

In the Qeasy data integration platform, this strategy is split into two integration solutions: a MySQL select source solution and a DingTalk batchSend target solution, then chained via a strategy.

Source side: Choose API type select (WebAPI, POST), paste the main SQL into main_sql, use :limit / :offset for pagination; bind placeholders via main_params. Declare response fields user_id, user_count, job_number, userid, real_name, id all as strings.

Target side: Choose API /v1.0/robot/oToMessages/batchSend, configure four request fields robotCode / userIds / msgKey / msgParam. msgParam uses _function CONCAT to assemble the three Markdown sections (title, count + time, detail link) into a valid JSON string. This is the typical Qeasy pattern of "header + body in one pass" — since the message has no detail rows, there is no need to split into multiple stages.

Implementation Steps

  1. Connect the source. Configure the MySQL data source in Qeasy and run select once to get a sample response — confirm userid and user_count are populated. This is the fastest way to surface permission, SQL syntax, and field-naming issues.
  2. Connect the target. Set up a DingTalk custom robot with robotCode, then call batchSend once directly via Postman to confirm Markdown renders correctly before moving it into Qeasy.
  3. Orchestrate the strategy. Create a new strategy, select the MySQL select source solution and the DingTalk batchSend target solution; row-level mapping — each source row triggers one target call.
  4. Schedule it. crontab: 1 8,10,12,14,16,18,20,22 * * * (source runs 1 minute before target so the query finishes before the notification fires). Start limit at 50, confirm there are no omissions before increasing.
  5. Gray verification. Watch a single approver first — verify they receive the Markdown at 8 AM, the count is correct, and the link is clickable.
  6. Full rollout. After 1–2 days of clean gray runs, open it up to all company codes and approvers.

Pitfalls We Have Hit

  • Do not hardcode the userid cc branch on the target side. For a specific approver we needed two extra cc recipients, so we did not encode it as an if-else in the mapping rule — instead, we generated the JSON array directly in the source SQL via CASE WHEN. This keeps encoding-mapping logic centralized in one place; later, changing the cc list only requires editing the SQL, not the Qeasy strategy. This is a common pattern among Qeasy customers.
  • The userid JSON array must wrap each id in double quotes. Our first version used CONCAT('[',userid,']'), and DingTalk returned 400 immediately — it expects a JSON string array, not a Python list. The safe form is CONCAT('["',userid,'"]'), with multiple ids joined by \","\".
  • The source crontab must run 1 minute before the target. Source at minute 1, target at minute 2. If reversed, the target fires before the source, and approvers receive a "you have 0" empty reminder at 8 AM.
  • Put now() on the source side, not in Qeasy. The timestamp inside the message must match the query moment. Using Qeasy's built-in time variable causes the odd effect of multiple messages in one batch sharing the same timestamp. For lightweight reminders like this, running an incremental + full-rescan dual track is critical — incremental by time window normally, full rescan to clear backlogs when something goes wrong.
  • Detail links must be clickable. In the Markdown template, [<View Details>](http://...:9000/examine/outsourcAudit) with angle-bracketed text renders as a blue clickable link in DingTalk; plain-text URLs are unclickable on the DingTalk desktop client.

When to Use & When Not

Use for: pending-approval / pending-task reminder-style lightweight push (purchase, sales, contract, expense reimbursement), non-structured notifications aggregated by recipient, and business notifications whose latency tolerance is measured in hours. Do not use for: scenarios that require detail-line display (e.g., listing 5 order numbers in one message) — for those, route to an OA todo or the business system's own todo center instead of a DingTalk group robot. It is also not suitable for minute-level real-time reminders; for that, use the DingTalk work-notification API for single-cast delivery.

Original content. Please credit the source when reposting: https://www.qeasy.cloud/insights/solutions/strat-mysql-dingtalk-4900-sz-sc-6b462b75

Comments