Sunday, November 15, 2026
HomePowershellAutomate Contract Renewal Reminders Earlier than They Auto-Renew

Automate Contract Renewal Reminders Earlier than They Auto-Renew


Open the register the place your vendor contracts reside and discover the final date you may cancel every one with out penalty. If that column is lacking, the date you might have as a substitute is the time period finish, and the house between the 2 is the place renewals get determined with out you.

Shut that house with a SharePoint checklist that carries the dates and one scheduled Energy Automate movement that reads it every morning. Then work by means of day arithmetic that survives time zones, a 90/30/7 ladder that fires every tier as soon as, and an escalation that reaches a second particular person when the primary ignores all three notices.

Conditions

If you wish to comply with alongside hands-on, you’ll want:

  • A SharePoint website the place you may create an inventory, with its regional time zone set to the movement’s (Web site settings → Regional settings). Contribute permission on the checklist is sufficient.

  • A Energy Automate plan overlaying the SharePoint and Workplace 365 Outlook connectors. Each are Commonplace, and the forms of Energy Automate licenses web page lists which plans embrace them.

  • The Approvals connector solely if you would like a written resolution report on the 30-day tier. It’s Commonplace too.

  • One contract proprietor and one escalation contact per vendor, every with an energetic mailbox. A reminder to a disabled account is the most typical cause a working movement produces nothing you may see.

The whole lot beneath was constructed within the Energy Automate cloud movement designer in a Microsoft 365 tenant in November 2026, utilizing the SharePoint On-line, Workplace 365 Outlook and Approvals connectors at their Commonplace tier, with no preview options, and each expression was checked towards the workflow expression reference.

Why the Discover Deadline Beats the Expiry Date

A contract carries a number of dates, and just one decides whether or not you retain the deal you suppose you might have.

Date on the contract What it decides The place it lives
Time period finish When the present time period runs out Finance, as a result of the bill lands there
Cancellation deadline Final day to serve discover that you’ll not renew The renewal or evergreen clause (the clause that rolls the time period over)
Worth enhance discover Final day to object to an uplift The pricing schedule
Renewal acknowledgement Final day to substantiate you propose to proceed Generally absent

A twelve-month time period with a 60-day discover interval places the choice in month ten. Watch the time period finish alone and the renewal surfaces in month twelve, when the one decisions are to simply accept it or argue with a vendor who already has you for one more 12 months.

Why a Sweep Beats a Ready Approval

That timing guidelines out the design most individuals attain for: a movement that watches 90 days out and waits for a human. The approvals identified points web page states: “An approval movement can look forward to 28 days. If the wait time exceeds 28 days, that movement fails.” The movement limits web page caps a single run at 30 days, pending steps included.

So the calendar turns into a each day sweep that reads the register and acts solely on what’s due at this time. The timeline beneath places one contract’s dates on a single line, so the run from worth enhance discover by means of time period finish is seen in a single look.

Contract renewal timeline

Set Up the Contract Renewal Register

One row per deadline, one column per truth the movement wants. A contract with a cancellation deadline and a worth enhance discover will get two rows, which retains each expression beneath pointed at a single date column.

Column Kind Why the movement wants it
Contract and deadline Single line of textual content (the checklist Title) Names the contract and the deadline in each e-mail
Deadline sort Alternative: Cancellation, Worth enhance, Time period finish Names the deadline in each topic line, so one movement serves all three
Deadline date Date, Embody time set to No The date the ladder counts all the way down to
Proprietor Particular person Who receives the discover and owns the choice
Backup proprietor Particular person Copied from tier two, and the primary escalation goal
Escalation contact Particular person The supervisor or finances holder who will get the overdue mail
Finance Particular person The second recipient on the overdue mail, copied beside the escalation contact
Reminder stage Quantity, default 0 The idempotency marker
Final discover despatched Date and time Drives the nudge with no Delay motion
Ack standing Alternative: Pending, Acknowledged, Escalated, Closed, default Pending The acknowledgement report
Ack by, Ack on, Escalated on Particular person, Date, Date Who answered, and when the second particular person was pulled in

Set Embody time to No on each date column, so SharePoint shops a calendar date and the comparability towards a yyyy-MM-dd string stops relying on the hour. The date and time formatting reference covers the format strings used beneath. Set Ack standing to Pending on each current row earlier than the primary run; the default covers solely new rows.

Don’t make the deadline a calculated column: the movement can’t write one, and can’t override it for the seller whose window is 45 days. Verify inside names too: SharePoint retains the identify a column was created with, so a column relabelled “Deadline” nonetheless arrives as NoticeDeadline and a filter on the label returns nothing.

Three Deadlines per Contract

One date per contract breaks the primary time a worth enhance lands earlier than the cancellation window opens. Give every deadline its personal row, so the stage and last-sent values are tracked individually and one marker can’t cowl three ladders. Previous 4 or 5 deadlines the rows get lengthy to learn, the price of one date column and one ladder.

A Log That Outlives the Run Historical past

Add a second checklist, Reminder log, with 4 columns; Create merchandise appends one row per discover, outliving the run historical past.

Column Kind Inside identify
Contract Single line of textual content (checklist Title) Title
Stage Quantity Stage
Recipient Single line of textual content Recipient
Despatched at Date and time Sent_x0020_at

Construct the Each day Sweep

Create a clean cloud movement with the Recurrence set off, then set the schedule Microsoft’s scheduled movement walkthrough describes: Frequency Day, Interval 1, Time zone set to the Home windows identify of the zone your crew works in, Begin time 6:00 a.m. on the primary morning you need the sweep to run. Six within the morning native leaves a full working day to behave. This construct makes use of Japanese Commonplace Time; put your personal zone identify in that subject and in each convertFromUtc calls beneath.

Add a Compose motion named Compose_Window_End earlier than the loop:

formatDateTime(addDays(utcNow(), 90), 'yyyy-MM-dd')

Then SharePoint: Get objects towards the checklist, with the superior parameters set:

  • Filter Question: Deadline_x0020_date le '@{outputs('Compose_Window_End')}' and Ack_x0020_status ne 'Closed'

  • Order By: Deadline_x0020_date asc

  • High Rely: 5000

Server-side filtering is the distinction between a dozen rows and the entire register. Microsoft’s steering on the Get objects motion states the default: “The default merchandise restrict is 100 and objects are paginated by default as nicely,” and going previous the ceiling means “Energy Automate fails and generates an error dialog.” Examine the merchandise rely towards the row rely of the checklist, as a result of a sweep studying 100 of 400 rows seems wholesome within the historical past.

Add an Apply to every over the worth array; the tier logic beneath runs inside it. Compose_Window_End and Compose_Today sit above the loop, and the subsequent part builds the second.

Calculate Days Left With out an Off-by-One

Energy Automate works in UTC, and a date-only SharePoint column shops the location’s native midnight as UTC, so a website east of UTC serialises yesterday. Subtract two dates naively and reminders land a day early or late, a defect no one notices till a deadline passes.

dateDifference will “Return the distinction between two dates as a timespan,” per the workflow expression reference, and a timespan just isn’t a day rely, so evaluating it to 90 doesn’t do what it seems like. Ticks are the integer route: “the variety of ticks, that are 100-nanosecond intervals, since January 1, 0001 12:00:00 midnight,” with 864,000,000,000 of them in a day.

Add Compose_Today outdoors the loop, earlier than Apply to every:

convertFromUtc(utcNow(), 'Japanese Commonplace Time', 'yyyy-MM-dd')

Then Compose_DaysLeft inside it:

div(sub(ticks(convertFromUtc(objects('Apply_to_each')?['Deadline_x0020_date'], 'Japanese Commonplace Time', 'yyyy-MM-dd')),
        ticks(outputs('Compose_Today'))),
    864000000000)

The expression converts the saved worth into the movement’s zone, so it returns the date the reader typed at any time when the location’s regional time zone matches the set off’s zone. utcNow() actually is a UTC immediate, which makes Compose_Today the one place a zone belongs. ticks turns each dates into complete numbers and div returns complete days: the deadline day reviews 0 and the day after reviews -1, so much less(DaysLeft, 0) fires the morning after.

Hearth Every Tier Precisely As soon as

Three circumstances testing DaysLeft = 90, = 30 and = 7 break the primary time the movement is off a day or a contract arrives already 12 days out. Equality towards a transferring quantity is a reminder that by no means sends; a band plus one saved stage survives each.

Goal stage Tier Days left What occurs
0 None 91 or extra Nothing
1 90-day 31 to 90 First discover to the proprietor
2 30-day 8 to 30 Second discover, backup copied, resolution requested
3 7-day 0 to 7 Remaining discover, escalation contact copied
4 Overdue Under 0 Escalation contact and finance, stage 4 saved

Compose_Target_Stage maps the day rely onto that ladder. Put this expression within the Compose motion:

if(much less(outputs('Compose_DaysLeft'), 0), 4,
  if(lessOrEquals(outputs('Compose_DaysLeft'), 7), 3,
    if(lessOrEquals(outputs('Compose_DaysLeft'), 30), 2,
      if(lessOrEquals(outputs('Compose_DaysLeft'), 90), 1, 0))))

A Situation decides whether or not something is due. It compares the goal towards the saved stage, except Ack standing reads Acknowledged. Put this expression within the Situation’s superior mode:

and(higher(outputs('Compose_Target_Stage'),
    coalesce(objects('Apply_to_each')?['Reminder_x0020_stage'], 0)),
  not(equals(objects('Apply_to_each')?['Ack_x0020_status']?['Value'], 'Acknowledged')))

A row that slipped previous its deadline sends the overdue discover on that run and shops 4. Acknowledged rows fail that take a look at and ship nothing at any day rely; an Escalated row nonetheless receives the overdue discover and the stage-4 write.

A clean quantity column arrives as null, and a logical comparability converts null to an empty string, so coalesce reads an unset stage as 0.

Why the Saved Stage Is an Ordinal

Storing the day rely seems less complicated and produces a worse bug. A contract first seen at 5 days out shops 5, and its goal stage is 3. The comparability is higher(Goal, Saved), so the saved rely sits above each goal and the situation by no means passes once more: one discover, then silence. A saved ordinal strikes up because the deadline approaches, which is the route the comparability expects.

Inside it, a Change on the goal stage units the topic, the opening line and the recipients, one Ship an e-mail motion sits outdoors, and one Replace merchandise follows. Each stage will get a case, together with stage 4, which addresses the escalation contact and finance. Set Reminder stage to the goal stage, Final discover despatched to utcNow(), and Title to the row’s title. Set the replace’s Run after to profitable solely, which Microsoft’s error dealing with steering describes as controlling “what ought to occur if an motion fails, occasions out, is skipped, or is profitable.” A failed ship leaves the stage untouched, and tomorrow repeats the discover.


Key Perception: The stage column is the one factor standing between a each day schedule and ninety equivalent emails. Run the movement twice in a single morning; the second run should ship nothing.


The diagram beneath follows one row by means of tomorrow’s run: the day rely picks a band, the saved stage decides whether or not something sends, and the replace lands solely when the goal outranks it.

Stage decision tree

Ship, Acknowledge, Escalate

The e-mail has one job: get a choice recorded. To is the proprietor, Cc is the backup from tier two onward, and the topic carries the deadline date, the deadline sort and the day rely within the movement’s zone, so it types:

Motion by @{convertFromUtc(objects('Apply_to_each')?['Deadline_x0020_date'], 'Japanese Commonplace Time', 'dd MMM yyyy')}: @{objects('Apply_to_each')?['Title']} - @{objects('Apply_to_each')?['Deadline_x0020_type']?['Value']} (@{outputs('Compose_DaysLeft')} days)

The physique carries what expires and when, what occurs with no motion, a hyperlink to the row, and the one motion you need. The hyperlink is the {Hyperlink} dynamic content material, so the reader lands on the merchandise as a substitute of a website residence web page:

Open the merchandise: @{objects('Apply_to_each')?['{Link}']}
Set Ack standing to Acknowledged after getting determined, and the ladder stops.

Acknowledgement belongs in a column. A reply studying “let me test” is invisible to a movement, can’t be counted, and leaves with the one that wrote it. The place the choice wants a written report, begin an approval on the 30-day tier with a five-day window, nicely contained in the 28-day ceiling. An approval blocks the department it sits on, so holding one open wants a parallel department to nudge; right here the sweep returns tomorrow. The nudge sits outdoors the Situation that decides what’s due and above rung three, and requires that no tier is due this morning. Its Replace merchandise units Final discover despatched to utcNow(), so it repeats 72 hours later on the earliest. The stage take a look at stays beneath stage 3, so a stage-3 or stage-4 row will get no additional nudges.

and(equals(objects('Apply_to_each')?['Ack_x0020_status']?['Value'], 'Pending'),
    much less(coalesce(objects('Apply_to_each')?['Reminder_x0020_stage'], 0), 3),
    lessOrEquals(outputs('Compose_Target_Stage'),
      coalesce(objects('Apply_to_each')?['Reminder_x0020_stage'], 0)),
    much less(objects('Apply_to_each')?['Last_x0020_notice_x0020_sent'], addDays(utcNow(), -2)))

Rung three wants its personal Situation, positioned outdoors that Situation, after its Replace merchandise, so each row reaches it. The nudge repeats the discover to the proprietor; this department strikes it to the second particular person. The gate wants the saved stage in addition to the Pending take a look at:

and(equals(objects('Apply_to_each')?['Ack_x0020_status']?['Value'], 'Pending'),
    equals(coalesce(objects('Apply_to_each')?['Reminder_x0020_stage'], 0), 3),
    lessOrEquals(outputs('Compose_DaysLeft'), 2),
    greaterOrEquals(outputs('Compose_DaysLeft'), 0))

The stage take a look at makes the rung reachable. A row that walked 90, 30 and seven days out saved 3 on the seven-day tier, and each later goal can be 3, so higher(Goal, Saved) stays false and the identical gate positioned inside that Situation by no means escalates. Given its personal Situation, a row nonetheless at stage 2 will get the tier-three discover from the Change and escalates on the primary morning it reads stage 3 at two days out. Ship the deadline restatement to the escalation contact with the proprietor copied, then set Ack standing to Escalated and Escalated on to utcNow(), leaving Reminder stage at 3. The decrease sure retains this department off the rows rung 4 handles: a row three days overdue nonetheless reads 3 from Get objects and clears the higher take a look at, so with out the sure it will get a second mail that morning. The Pending take a look at sends it as soon as; the write makes the department false tomorrow. Rung 4 wants no separate situation: the goal expression maps a unfavorable day rely to stage 4, the Change addresses the escalation contact and finance, and the Replace merchandise writes Reminder stage 4, which outranks each later goal so the row goes quiet. Add one motion inside that department to append a row to Reminder log with the contract, stage, recipient and timestamp.

Alternative columns arrive as an object with a Worth property. Examine .Worth to the textual content; evaluating the article itself is all the time false and fails silently.

The Escalation Ladder

Rung Fires when Who receives it What will get written
1 90, 30 and seven days out Proprietor, backup copied from tier two Reminder stage, final discover despatched
2 No tier due, 72 hours after the final discover, nonetheless Pending, stage beneath 3 Proprietor once more, deadline restated Final discover despatched
3 2 days out, stage 3 saved, nonetheless Pending Escalation contact, proprietor copied Ack standing Escalated, escalated on
4 First morning beneath zero days, except acknowledged Escalation contact and finance Reminder stage 4, Reminder log row

Microsoft’s notes on identified approval points cowl one wrinkle: when an approver responds by e-mail or within the approvals middle, the Groups adaptive card doesn’t replace, so learn the end result from the approval report.

Take a look at the Ladder, Then Diagnose It

Run these on a replica of the checklist and preserve the reside register out till they cross.

  1. 91 days out: no e-mail, stage 0.

  2. Similar row at 90 days: one discover, stage 1, no nudge.

  3. Second run that morning: nothing. That is the duplicate take a look at that issues.

  4. Day 29: the 30-day discover alone, stage 2, no repeat of the 90-day mail.

  5. A brand new row 6 days out: one remaining discover, stage 3, no backfill of wider tiers, no nudge.

  6. Three mornings after the 30-day discover, nonetheless Pending: one nudge to the proprietor, nothing else.

  7. Similar row at 2 days out, stage 3, nonetheless Pending: one mail to the escalation contact, proprietor copied, Ack standing Escalated, no second mail that morning.

  8. A row at 2 days out holding stage 1, Ack standing Acknowledged: no mail, and none thereafter.

  9. Ack standing Closed on any row: absent from the Get objects output.

  10. Clean Ack standing and a clean Reminder stage on a brand new row: one discover, no template error.

  11. The row from merchandise 7, sooner or later previous its deadline: one mail to the escalation contact and finance, stage 4, one Reminder log row, and nothing on the subsequent morning’s run.

When the Circulate Runs Inexperienced and Sends Nothing

Symptom Trigger Repair
Inexperienced runs, no mail Filter matches nothing: flawed inside identify or literal Rely the objects within the Get objects output
Just some rows processed High Rely left at 100 Elevate it and examine counts
Off by sooner or later Date column retains a time, or the location’s zone differs from the movement’s Embody time No, test Regional settings
Similar reminder each day Replace merchandise skipped, stage by no means superior Verify its run-after settings and output
Situation by no means true Alternative column in contrast as an object Examine .Worth

Begin within the run historical past: zero objects from Get objects means the filter is at fault. A dozen objects and no e-mail factors on the situation or the stage comparability. 100 the place you anticipated 2 hundred is High Rely.

Energy Automate additionally suspends flows that fail persistently or by no means set off, and the limits web page names the recorded causes, together with AlwaysFailingDetected and NeverTriggeringDetected.


Warning: A disabled proprietor mailbox doesn’t fail Ship an e-mail. The motion succeeds, the run goes inexperienced, and the message bounces. Verify the sender’s non-delivery report earlier than you test the movement.


The place the Calendar Stops Being Sufficient

The calendar is aware of what you typed into it. It can’t learn the contract renewal clause, can’t affirm the discover interval matches the contract, and can’t negotiate the uplift. These keep human jobs, moved from month twelve to month ten. The movement itself runs below the account that created its connections, so when that particular person leaves, the sweep stops with them. Add movement possession to the offboarding guidelines, so the sweep outlives them.

The run historical past just isn’t an audit path both. Runs are retained for 30 days, shorter than the cycle being tracked, so the proof of what was despatched disappears earlier than the subsequent contract renewal. The overdue rung appends a row to the Reminder log; add it to the three reminder branches, one motion every.

Quantity is the opposite boundary. An inventory plus a schedule covers contract expiry monitoring for a number of hundred deadlines, and the 5,000-item ceiling on a single Get objects name is the place that method ends. Departments that want isolation need separate lists reasonably than item-level permissions, and clause extraction has moved previous what an inventory and a schedule ought to carry.

A calendar like that is one occasion of a sample overlaying most recurring back-office paperwork, and Adam builds and maintains these enterprise course of automations for groups that may reasonably personal the end result than the movement.

The Three Numbers That Present It Works

Tomorrow morning, test three numbers: the merchandise rely from Get objects towards the row rely of the checklist, the days-left worth on a deadline you already know, and the final discover despatched timestamp on a contract that ought to have acquired a tier. These three reply whether or not the calendar works, lengthy earlier than a renewal depends upon it.

Previous articleSubject #640
RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments