Automate Project Cost Auditing with MySQL & Outlook in n8n

Manual Project Budget Audits Causing Cost Center Chaos for Amjid

Meet Amjid, a project manager at a mid-sized company juggling multiple external projects spread across several cost centers. Every week, Amjid faces the tedious and error-prone task of auditing project budgets to identify which projects lack assigned budgeted costs. This manual process eats up hours, and worse, neglected missing budgets frequently lead to inaccurate financial forecasting and delayed decision-making. Amjid needs a reliable automated reminder system that pinpoints these missing budget details and alerts the right teams without fail.

What This Automation Does ⚙️

This n8n workflow automates the entire process of identifying active external projects missing budgeted cost data and notifying respective cost center teams via tailored emails through Microsoft Outlook. When triggered weekly, it:

• Queries the MySQL project database for open, active external projects where budgeted costs are zero.
• Groups results by company and cost center, summarizing how many projects lack budgets.
• Routes each cost center’s data through a Switch node to determine the correct email recipient and message.
• Sends customized HTML email alerts directly to the concerned cost center teams via Microsoft Outlook.
• Ensures timely budget updates for accurate financial tracking and accountability.
• Saves Amjid several hours weekly previously spent on manual data extraction and email drafting.

Prerequisites ⚙️

• n8n account to create and manage the workflow.
• Access to your MySQL database with relevant project data tables and query permissions.
• Microsoft Outlook account configured with API access for sending emails.
• Basic knowledge of SQL queries beneficial.

Step-by-Step Guide to Build This Workflow

Step 1: Set Up the Schedule Trigger to Run Weekly

In n8n, click the + Add Node button, select Schedule Trigger. Navigate to the settings and configure it to trigger once every week at 8:00 AM. This automates the audit reminder timing.

Expected outcome: Workflow initiates weekly without manual intervention.

Common mistake: Forgetting to set the time or interval correctly, causing no triggers.

Step 2: Add the MySQL Node to Query Project Data

Select the MySQL node from the n8n nodes list. Configure your database credentials, then paste this SQL query exactly to extract needed data:

SELECT 
    company,
    cost_center AS default_cost_center,
    COUNT(*) AS project_count
FROM 
    tabProject
WHERE 
    status = 'Open' 
    AND project_type = 'External'
    AND is_active = 'Yes'
    AND budgeted_project_cost = 0
GROUP BY 
    company, cost_center
ORDER BY 
    company, project_count DESC;

This query filters projects with zero budget cost, ensuring only active external projects are considered.

Expected outcome: Node outputs project counts by cost center with missing budgets.

Common mistake: Running query on wrong database or without proper read permissions.

Step 3: Add the Switch Node to Route Cost Center Data

Insert a Switch node to split incoming data based on the default_cost_center field. Configure four outputs named A, B, C, and D, each corresponding to a specific cost center:

• Output A — Cost Center A
• Output B — Cost Center B
• Output C — COST CENTER C (case sensitive)
• Output D — Cost Center D

Use exact string comparison conditions for each.

Expected outcome: Each project group is routed correctly to the assigned email node.

Common mistake: Mismatched case or extra spaces in cost center names leading to routing failures.

Step 4: Configure Microsoft Outlook Nodes to Send Emails

Add one Microsoft Outlook node per cost center (three in this example, configured for A, B, and D outputs; C output can be left empty or handled similarly). In each Outlook node:

• Set the Subject to “Projects Cost Missing” or similar.
• Customize the HTML Body Content with a clear message including dynamic variables like {{ $json.default_cost_center }} and {{ $json.project_count }} to personalize emails.
• Set the To Recipients to the cost center’s email (e.g., [email protected]).
• Set Body Content Type to html for proper formatting.

Expected outcome: Tailored email alerts are sent to each cost center team highlighting missing budget entries.

Common mistake: Forgetting to connect respective outputs or incorrect email formatting causing failed sends.

Customizations ✏️

• Add More Cost Centers: In the Switch node, add new output rules for additional cost centers, and create corresponding Outlook nodes to handle their email alerts.
• Change Email Recipients: Update the toRecipients field in the Outlook nodes to target the actual team contacts instead of static email.
• Modify Trigger Time: Adjust the Schedule Trigger node to run at a different time or frequency, such as daily or monthly, based on reporting needs.
• Enhance Query: Extend the MySQL query to include more project details or filter by additional criteria like priority or region.

Troubleshooting 🔧

Problem: No emails sent despite the workflow running

Cause: Likely the Switch node isn’t routing data correctly due to case mismatch or missing cost center strings.

Solution: Double-check the exact spelling and case of cost center names in the Switch node conditions. Test the MySQL data output to confirm values.

Problem: MySQL node timeout or query failure

Cause: Incorrect database credentials or query syntax errors.

Solution: Verify credentials, test query directly in a database client, and ensure the table and columns exist as named.

Pre-Production Checklist ✅

• Verify MySQL credentials and test the query independently for accurate data.
• Confirm all four Switch node outputs correspond correctly to your cost centers.
• Send test emails from Outlook nodes using sample data.
• Check HTML email formatting for readability.
• Ensure Schedule Trigger timing matches business needs.

Deployment Guide

Once tested thoroughly, activate the workflow in n8n by toggling the activation switch. Monitor initial runs for errors by checking execution logs inside n8n. Since this workflow sends financial notifications, ensure secure credential storage and access controls.

Optionally, set up error notifications or logging nodes to catch failures and alert admins.

FAQs

• Can I use a different email provider?
  Yes, you can switch Microsoft Outlook nodes to Gmail or SMTP nodes by adjusting credentials and settings accordingly.
• Is my data secure?
  n8n encrypts credentials and supports deployment on secure self-hosted servers for maximum data control.
• Can I include more dynamic project details in emails?
  Yes, extend the SQL query and adjust email HTML templates to include additional variables easily.

Conclusion

By automating project budget auditing with MySQL data and Microsoft Outlook email alerts in n8n, Amjid now saves hours weekly and ensures no budget gap goes unnoticed. This automation brings accountability and clarity to project cost centers, minimizing financial reporting errors.

Next steps could include integrating Slack notifications for real-time alerts or adding project cost updates directly from forms to the MySQL database for a full budgeting workflow.

Give this setup a try, and you’ll experience the relief of automating financial accuracy.