Here's a scenario that plays out in organizations every day: your team has a SharePoint list tracking vendor contracts. Contracts get added manually, sometimes with missing fields. Renewals get missed because nobody thought to flag items expiring in 30 days. Old contracts that expired two years ago are still cluttering the list, making search results unreliable. And when someone finally does archive a contract, they copy-paste it into a second list by hand — sometimes incorrectly, always slowly.
This is exactly the kind of work Power Automate was built for. Not glamorous automation that replaces entire departments, but precise, rule-based orchestration of routine data operations — the kind that happens hundreds of times a month and costs your team real hours when done manually. When you wire up SharePoint list item lifecycle management properly, your lists stay clean, your data stays consistent, and your team spends time on decisions rather than data hygiene.
By the end of this lesson, you'll be able to build complete, production-quality flows that manage the full lifecycle of a SharePoint list item: from conditional creation, through status-driven updates, to rule-based archiving and safe deletion. You'll handle edge cases, avoid the mistakes that cause silent failures, and understand the structural decisions that make a lifecycle management system maintainable over time.
What you'll learn:
You should be comfortable with:
formatDateTime(), utcNow(), string concatenation)You do not need to be a Power Platform developer. This is practitioner-level work — you're building flows that real organizations rely on.
Before you write a single flow action, you need a list structure that supports lifecycle operations. Most lifecycle failures aren't flow failures — they're design failures. Lists that weren't built with automation in mind make your flows fragile, verbose, and hard to troubleshoot.
For this lesson, we'll use a Vendor Contract Tracker as our working example. This is realistic enough to cover complex scenarios without becoming a distraction.
Create a SharePoint list called VendorContracts with these columns:
| Column Name | Type | Notes |
|---|---|---|
| Title | Single line of text | Vendor name (required) |
| ContractNumber | Single line of text | Unique identifier |
| ContractValue | Currency | Annual contract value |
| StartDate | Date only | Contract start |
| ExpirationDate | Date only | When contract expires |
| RenewalDeadline | Date only | 30 days before expiration — calculated or set by flow |
| Status | Choice | Draft, Active, Expiring Soon, Expired, Archived |
| ContractOwner | Person or Group | Internal owner |
| AutoRenewal | Yes/No | Whether contract auto-renews |
| LastModifiedByFlow | Single line of text | Tracks which flow last touched this item |
| ArchiveReason | Single line of text | Populated before deletion |
The Status field is your lifecycle control column. Every automated operation you build will either read this field to decide what to do, or write to it as the result of a business rule. Keep its choices clean and exhaustive — every valid state a contract can be in should appear here.
The LastModifiedByFlow column deserves special attention. This is a metadata field your flows write to when they modify an item. When something goes wrong at 2am on a Saturday, this column tells you which flow touched this record last. Don't skip it.
Create a second list called VendorContracts_Archive with identical columns, plus two additional ones:
| Column Name | Type | Notes |
|---|---|---|
| OriginalItemID | Number | The SharePoint ID from VendorContracts |
| ArchivedDate | Date and time | When the item was archived |
Why keep a separate archive list instead of just filtering by Status = "Archived"? Because archive lists accumulate forever, and eventually your primary list becomes slow and cluttered. More importantly, it gives you a clean place to apply different retention policies — you might keep active contracts indefinitely but purge archives after 7 years. Separating them makes that possible.
The first lifecycle event is creation. This sounds simple — someone fills out a form and you create a list item. But in practice, creation logic needs to handle duplicates, set calculated fields, and apply initial business rules.
We'll trigger this from a Power Apps form (or a Microsoft Form), but the pattern applies to any creation source. The trigger is "When a new response is submitted" (for Forms) or "When an HTTP request is received" (for Power Apps).
For this example, we'll use Microsoft Forms feeding into our VendorContracts list.
The most common mistake in creation flows is skipping duplicate detection. Here's how to do it properly.
After your trigger fires, add a "Get items" action targeting VendorContracts. In the Filter Query field, use an OData expression to check for existing contracts with the same contract number:
ContractNumber eq '@{triggerBody()?['responderEmail']}'
Wait — that's the wrong field. This is a common mistake: people build the filter with placeholder logic and forget to update it. Your actual filter should look like this, using the form response field that contains the contract number:
ContractNumber eq '@{outputs('Get_response_details')?['body/r3a4b5c6d']}'
That cryptic field reference (r3a4b5c6d) is Microsoft Forms' internal field ID. To get the actual value cleanly, first store the form response in a Compose action and reference it by name. This makes your flow far more readable:
Add a Compose action after getting the form response, name it ContractNumberInput, and set its input to the contract number field from your form. Now everywhere else you can reference outputs('ContractNumberInput') instead of the full dynamic content path.
After the Get items action, add a Condition that checks:
length(body('Get_Items_-_Check_Duplicate')?['value'])
Is equal to 0.
If this is true (no duplicates found), proceed with creation. If false, send an email notification to the submitter explaining the contract number already exists.
Inside the true branch, add a "Create item" action. Here's where most flows get sloppy — people only fill in the required fields and leave everything else empty. Let your creation flow do the work:
For RenewalDeadline, use an expression to calculate 30 days before expiration:
addDays(outputs('ExpirationDateInput'), -30)
For Status, set it to Draft unconditionally. Don't try to determine Active vs. Draft at creation time — you have another flow for that.
For LastModifiedByFlow, enter a literal string like Flow: Contract Creation v1.2. When you update the flow later, update this string. That's your audit trail.
After the Create item action, add an "Update item" action to write the newly created item's SharePoint ID back into a field if you need cross-referencing — but more importantly, use this as your place to send a confirmation email to the ContractOwner with the new item's link.
Tip: Always use "Get item" after "Create item" if you need to reference the created item's auto-generated fields (like
IDor system-managed metadata). The Create item response gives you the ID directly inbody('Create_item')?['ID']— use that rather than doing an extra Get.
This is the heart of lifecycle management. Once per day (or more frequently for time-sensitive data), a scheduled flow evaluates every item in your list and updates its Status based on business rules.
Use the "Recurrence" trigger. Set it to run daily at 7:00 AM UTC. If your organization is US-based, 7:00 AM UTC is overnight in most US time zones — your flow runs before anyone shows up to work, and they see accurate statuses first thing in the morning.
Don't run this flow every hour unless you genuinely need that freshness. Scheduled flows that run frequently generate a lot of flow run history, make troubleshooting harder, and can hit SharePoint API throttling limits if your lists are large.
This is the most important performance decision in a lifecycle flow: never get all items if you can filter.
Instead of using a "Get items" action with no filter and then applying conditions inside the flow, use OData filter queries to only retrieve items that actually need evaluation.
For updating items that might be transitioning from Active to Expiring Soon, you want items where:
Your OData filter:
Status eq 'Active' and ExpirationDate le '@{addDays(utcNow(), 30)}' and ExpirationDate ge '@{utcNow()}'
For transitioning from Expiring Soon to Expired:
Status eq 'Expiring Soon' and ExpirationDate lt '@{utcNow()}'
Run these as separate "Get items" actions, each followed by their own "Apply to each" loop. This is more efficient than one giant loop with nested conditions, and far easier to troubleshoot — if your "Active to Expiring" logic breaks, it doesn't affect the "Expired" logic.
Warning: The "Get items" action has a default top limit of 100 items. If your list has more than 100 contracts that might need status evaluation, turn on pagination in the action settings. Set the threshold to 5000 (SharePoint's maximum for a single query). Do not skip this step — silent truncation at 100 records is one of the most common causes of unexplained data inconsistency.
Inside the Apply to each for "Active → Expiring Soon" transitions:
Add an "Update item" action. You do not need a condition here — every item returned by your OData filter meets the criteria for this update. Set:
items('Apply_to_each')?['ID']Avoid the temptation to add a condition inside the loop that re-checks the same criteria you already filtered on. Trust your OData filter. Re-checking creates redundancy and makes the flow harder to read.
For the "Expiring Soon → Expired" loop, set Status to Expired and also check the AutoRenewal column. If AutoRenewal is Yes, instead of marking it Expired, you might want to:
That branching logic lives inside the Apply to each loop using a Condition action:
This is where the power of the lifecycle pattern really shows — your business rules aren't documented in a wiki that nobody reads. They're enforced by the flow itself.
Don't send a notification every time your scheduled flow runs. That creates notification fatigue. Instead, send notifications only on state transitions.
After updating an item to "Expiring Soon," send an email to ContractOwner using the "Send an email (V2)" action:
Subject: Contract Expiring in 30 Days - @{items('Apply_to_each')?['Title']}
Body:
The contract with @{items('Apply_to_each')?['Title']} (Contract #@{items('Apply_to_each')?['ContractNumber']})
is expiring on @{items('Apply_to_each')?['ExpirationDate']}.
Please review and take action before the renewal deadline:
@{items('Apply_to_each')?['RenewalDeadline']}.
View the contract: @{concat('https://yourorg.sharepoint.com/sites/YourSite/Lists/VendorContracts/DispForm.aspx?ID=', items('Apply_to_each')?['ID'])}
Hard-coding the SharePoint site URL is fine here. Just document it in your flow description so whoever maintains this flow later knows what to update if the site moves.
Deleting a SharePoint list item is permanent. Even with recycle bins, restored items lose their version history. The archive pattern solves this by copying the full item to an archive list before any deletion occurs. This is the safest and most auditable approach to record removal.
Many organizations only realize they need an archive after something goes wrong — a compliance audit, a dispute over contract terms, someone asking "what did that record say before we deleted it?" By building archiving into your lifecycle from the start, you're making a decision that costs you 10 minutes now and potentially saves hours of scrambling later.
Archiving can be triggered two ways:
We'll build the scheduled version here, since it's more complex and more powerful.
Trigger: Recurrence — daily at 7:30 AM UTC (30 minutes after your status update flow, so the statuses are already current).
Step 1: Get items to archive
Use OData filter to find expired contracts that have been sitting in Expired status long enough:
Status eq 'Expired' and ExpirationDate lt '@{addDays(utcNow(), -90)}'
This finds contracts that expired more than 90 days ago. Enable pagination with threshold 5000.
Step 2: Apply to each — Create the archive record
For each item returned, create a record in VendorContracts_Archive. Map every field from the source item:
items('Apply_to_each')?['Title']items('Apply_to_each')?['ContractNumber']items('Apply_to_each')?['ContractValue']items('Apply_to_each')?['StartDate']items('Apply_to_each')?['ExpirationDate']Archiveditems('Apply_to_each')?['ID']utcNow()Expired > 90 days - Auto-archived by scheduled flowFlow: Archive and Delete v1.0Tip: Map every column explicitly, even if some values are null. This prevents schema drift from causing silent data loss. If you add a column to VendorContracts six months from now, your archive flow will need to be updated too — that's expected maintenance, not a design flaw.
Step 3: Verify the archive was created
This is the step most flow builders skip, and it's the most dangerous thing to skip. Before deleting the original item, confirm the archive record was actually created successfully.
After the "Create item in Archive" action, add a "Get item" action targeting VendorContracts_Archive using the ID returned by the create action: body('Create_item_in_Archive')?['ID'].
Then add a Condition:
body('Get_item_-_Verify_Archive')?['ID'] is not equal to (leave blank / null)If this condition is true (archive item exists and has an ID), proceed to deletion.
If false, skip deletion and add an action to send yourself a notification that the archive verification failed for a specific item. Include the original item ID so you can investigate manually.
This extra step is the difference between a flow you can trust and a flow you have to babysit.
Step 4: Delete the original item
In the true branch of your verification condition, add a "Delete item" action targeting VendorContracts with the ID items('Apply_to_each')?['ID'].
That's it. The record now lives in your archive list permanently, and the primary list stays clean.
Configure your Apply to each to not stop on errors. In the settings of the Apply to each control, enable "Continue on error." This way, if one item fails to archive (perhaps due to a field validation issue in the archive list), the flow continues processing the remaining items rather than stopping entirely.
Add a "Compose" action after the Apply to each to capture the results, then send yourself a summary email. Use the expression body('Apply_to_each_-_Archive') to get the loop outputs. If any iterations failed, the email tells you which ones.
Not every lifecycle event is scheduled. Sometimes a contract owner needs to manually trigger a lifecycle transition — canceling a contract, putting it on hold, or immediately archiving it after a vendor relationship ends.
Use the SharePoint trigger "When an item is created or modified" on the VendorContracts list.
The challenge with this trigger is that every modification fires it — including modifications made by your other flows. If you're not careful, you'll create trigger loops where Flow A modifies an item, which triggers Flow B, which modifies the item again, which triggers Flow A again.
Break the loop with a condition at the top of the flow.
Add a condition as the very first action:
items('Apply_to_each')?['LastModifiedByFlow']
Does not contain Flow: (the prefix all your automated flows use).
If this evaluates to true (meaning a human edited the item, not a flow), continue. If false, terminate the flow immediately using a "Terminate" action set to "Succeeded." This prevents your flows from chasing their own tails.
Warning: The "When an item is modified" trigger fires even when Power Automate modifies the item. Always include loop-breaking logic at the start of any flow triggered by item modification. Failing to do this is the single most common cause of infinite loop incidents in SharePoint-connected flows.
Once you've confirmed a human made the change, evaluate what kind of change it was. Check the Status field value:
If Status = "Archived": Immediately trigger the archive-and-delete logic. A human has decided this item should be archived right now, not in 90 days.
If Status = "Draft": Send the ContractOwner a reminder that the contract hasn't been activated yet and ask them to review the remaining fields.
If Status = "Active": Check whether StartDate is today or in the past. If the StartDate is in the future, send a warning: "You've marked this contract Active, but it doesn't start until [date]. Is this intentional?"
These small validation checks are the kind of thing that prevents dirty data from accumulating. They're not hard to build once the core lifecycle structure is in place.
Build the complete VendorContracts lifecycle management system described in this lesson. Here's the specific build sequence:
Phase 1: List Setup (20 minutes)
VendorContracts list with all columns described in the design sectionVendorContracts_Archive listPhase 2: Creation Flow (30 minutes)
Phase 3: Scheduled Status Update Flow (45 minutes)
Phase 4: Archive Flow (45 minutes)
Phase 5: Manual Trigger Flow (20 minutes)
SharePoint stores dates in UTC. When you write an OData filter with a date expression, you need to make sure you're comparing UTC to UTC.
Broken:
ExpirationDate lt '@{utcNow()}'
This looks correct but can fail for date-only columns if SharePoint interprets the time component unexpectedly.
More reliable:
ExpirationDate lt '@{formatDateTime(utcNow(), 'yyyy-MM-dd')}'
For date-only columns, formatting to yyyy-MM-dd strips the time component and produces cleaner comparisons.
Your flow works perfectly in testing with 8 items. It fails silently in production with 340 items. You see updates applied to only 100 records and spend two hours wondering why.
Fix: Always enable pagination on every "Get items" action in lifecycle flows. Go to the action's three-dot menu, select Settings, enable Pagination, and set the threshold to 5000.
When you reference a field in an expression and that field is null, your expression fails and the action errors out. This is especially common with optional fields like ArchiveReason or ContractValue.
Use the coalesce() or if() expression to provide defaults:
coalesce(items('Apply_to_each')?['ArchiveReason'], 'No reason specified')
As described above — your flow modifies an item, which retriggers the flow. The symptom is a flow run history showing thousands of runs in a short period and possibly throttling errors.
Fix: Always check LastModifiedByFlow at the start of any item-modified flow and terminate if the value indicates a flow made the last change.
Skipping the archive verification step and deleting immediately after creating the archive item. The Create action succeeds even if the item is malformed — you need to "Get item" from the archive to confirm it's actually there and valid.
Doing a "Get items" SharePoint query inside an "Apply to each" loop. Every iteration of the loop makes a separate API call to SharePoint, which is slow and hits throttling limits fast.
Fix: Get all items you need before the loop, then work with what you have inside the loop.
items('Apply_to_each')?['ID'] and not a hardcoded valueThis is usually the verification condition evaluating to false when it should be true. Open the failed run in Power Automate's run history, expand the "Get item - Verify Archive" action, and check what body it returned. Compare the condition inputs to what the action actually returned.
You've built a complete, production-quality SharePoint list item lifecycle management system. Let's recap what you've put in place:
The patterns here extend well beyond vendor contracts. The same structure applies to project tracking, IT asset management, employee onboarding workflows, incident management, and anywhere else you have records that move through defined states over time.
Where to go next:
The real value of lifecycle automation isn't any single flow — it's the compounding reliability of a system where your data is always in a known, valid state without anyone having to remember to maintain it.
Learning Path: Flow Automation Basics