Contract Expiry Alert System

Overview

Every morning at 9 AM, this flow scans a SharePoint contracts list for items with Status eq 'Active', calculates the number of days remaining until each contract's expiry date, and then routes a tiered set of alerts based on how close the contract is to expiring. Contracts hitting 90, 60, or 30 days out, the final week countdown (7 through 1 days), and the day-of expiry each trigger their own escalation message to the appropriate stakeholders. Once a contract reaches zero or fewer days remaining, its SharePoint status is updated to Expired so it no longer triggers active-contract alerts.

Use Case

Contract lifecycle management lives or dies by renewal lead time. When expiry dates are tracked manually in a SharePoint list or spreadsheet, it is easy to miss the 90-day window most legal teams want for renewal negotiations — and even easier to let weekly check-ins lapse until a contract has already expired. This flow gives the contracts team an automated, tiered reminder system that escalates from a polite 90-day heads-up to a daily countdown in the final week, ensuring owners, legal, and management always have time to act.

The flow is ideal for teams that:

• Operations and legal teams managing renewable vendor or customer contracts
• Centralizing contract expiry tracking in SharePoint
• Automating tiered escalation as expiry dates approach
• Reducing the risk of unintentional lapse or auto-renewal
• Giving management visibility on imminent expirations
• Auto-updating contract status when expiry passes

Flow Architecture

Environment Variables

Connectors & Connections

Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.

Customization Guide

Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.

Deploying to another environment

Import the solution into the target environment, then set flowlibs_ContractsSiteUrl and flowlibs_ContractsListName to point at your contracts list, and flowlibs_LegalEmail / flowlibs_ManagementEmail to the relevant distribution lists. Verify the SharePoint list has Status, ExpiryDate, Owner, and ContractName columns, then turn the flow on.

Add a Teams adaptive-card dashboard

Use flowlibs_TeamsChannelId and add a Post adaptive card action to the loop so the team gets a live visual countdown of contracts entering each tier.

Include contract value for prioritization

Pull the ContractValue column into each alert message so legal can sequence renewals by dollar impact.

Renewal workflow trigger

Add a 'Renew' button to the alert email that posts to a child flow which clones the contract record into a renewal draft and assigns a task to the owner.

Custom escalation tiers

Adjust the Switch cases (e.g. add a 14-day reminder, or replace the daily countdown with every-other-day) to match your legal team's SLAs.

Key Expressions

The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.

dateDifference(utcNow(), item()?['ExpiryDate'])

Status eq 'Active'

lessOrEquals(outputs('Compose_Days_To_Expiry'), 0)