Deadline Urgency Monitor

Deadlines slip because nobody looks at the list until it's late. This flow surfaces the next two weeks every morning with a traffic-light urgency scale, so the team sees red before it becomes a problem.

A daily recurrence at 08:00 runs a weekend guard first: if dayOfWeek(utcNow()) is 0 or 6, Terminate as Succeeded. Otherwise a top-level ReportHTML variable collects rows from SharePoint items with SAL below 100 in a two-week window, a nested if() over ticks() differences picks the urgency colour, and the result is posted to Teams.

1. 1

   Create a Scheduled cloud flow with a Recurrence trigger: Frequency Day, Interval 1, Time zone Rome, At these hours 8, At these minutes 0.

2. 2

   Add a Condition immediately after the trigger: or(equals(dayOfWeek(utcNow()), 0), equals(dayOfWeek(utcNow()), 6)). In the If yes branch add Control → Terminate with status Succeeded.

3. 3

   Add Variables → Initialize variable 'ReportHTML' (String) at the TOP LEVEL of the flow, never inside a condition or loop — initialize actions are not allowed in nested scopes.

4. 4

   Add SharePoint → Get items with Filter Query SAL lt 100 and DueDate le '@{addDays(utcNow(),14)}' and DueDate ge '@{utcNow()}', ordered by DueDate asc.

5. 5

   Add Apply to each over the value. Inside, add Compose 'HoursLeft' with div(sub(ticks(item()?['DueDate']), ticks(utcNow())), 36000000000).

6. 6

   Add Compose 'Urgency' with a nested expression: if(less(outputs('HoursLeft'),24),'🔴', if(less(outputs('HoursLeft'),72),'🟠', if(less(outputs('HoursLeft'),168),'🟡','🟢'))).

7. 7

   Append to ReportHTML a <tr> containing the urgency icon, the item Title, the owner and formatDateTime of DueDate.

8. 8

   After the loop add a Condition on empty(variables('ReportHTML')); if true, Terminate as Succeeded.

9. 9

   Add Microsoft Teams → Post message in a chat or channel with an HTML table built from ReportHTML and a heading 'Deadlines — next 14 days'.

10. 10

    Test on a weekday, then use Test → Manually on a Saturday value to confirm the weekend guard terminates cleanly.

Paste this into Power Automate Copilot to scaffold the flow.

Create a scheduled cloud flow that runs every day at 08:00 Rome time. First add a weekend guard: if dayOfWeek(utcNow()) equals 0 or 6, terminate as succeeded. Then initialize a top-level string variable ReportHTML, get SharePoint items where SAL is less than 100 and DueDate falls in the next 14 days, loop them computing hours remaining from ticks(), assign a colour icon with nested if expressions, append HTML rows, and post the resulting table to a Microsoft Teams channel.

Captures are added as each step is published.

• Terminate action
• Workflow expression functions
• Post a message in Teams