Data Integrity Checker
Nightly validation of required fields, orphan lookups and duplicates across the lists your flows depend on.
This flow is being documented
Architecture and use case are already public. Build instructions and screenshots follow as the flow is finalised.
Use case
Flows assume clean data. One empty required column or duplicate key and a downstream flow silently produces the wrong output. A nightly checker reports the rot before it spreads.
Who uses it
SharePoint administrators and flow maintainers.
Real scenario
At 02:00 the checker finds four items with an empty Owner, one duplicate reference and two lookups pointing at deleted rows, and emails the list owner a categorised report.
Time saved
Before
Bad data discovered by whoever the wrong report reaches
After
A categorised report before the working day starts
Time saved
Hours of downstream debugging
How it works
A nightly recurrence reads each monitored list, applies a set of rule checks (required fields, duplicate keys, orphan lookups, out-of-range dates), collects violations into a report array and emails or posts a categorised summary to the owners.
Step-by-step build
- 1
Define the validation rules per list in a configuration SharePoint list.
- 2
Recurrence at 02:00, then loop the monitored lists.
- 3
For each list, run Get items and Filter array checks per rule.
- 4
Aggregate violations into a single report grouped by rule type.
- 5
Email or Teams-post the report to each list owner, and terminate silently when there is nothing to report.
Copilot prompt
Paste this into Power Automate Copilot to scaffold the flow.
Create a scheduled flow that runs nightly, reads a configuration list of validation rules, loops the monitored SharePoint lists checking for empty required fields, duplicate keys and orphan lookup references, aggregates all violations into a grouped report and emails it to each list owner, terminating silently when no issues are found.
Screenshots
Captures are added as each step is published.