The Run-Based Collection Loop: Stop chasing responses by hand

Recurring collection work breaks down in the follow-up. You send a request, responses trickle in, and you lose track of who is outstanding. The fix is not more emails. The fix is state.

We call this pattern the Run-Based Collection Loop: a reusable system that creates one status row per recipient per run, then drives completion with automated reminders and a clean close. It works for quarterly reporting, policy attestations, document collection, and any workflow where silence is not acceptable.

We have implemented this pattern for a client running 30+ recipients per quarterly cycle, but the structure is industry-agnostic and portable.

Tools used: Power Automate + SharePoint Lists + Microsoft Forms.

Follow-up is a state problem, not an email problem. If you do not store status per person per run, you will always chase by hand.

What this system does

One run = one collection cycle (for example, 2026-Q1). The system:

• Sends a personalized request to each recipient
• Tracks response status per person, per run
• Reminds only non-responders on a cadence you control
• Logs every reminder and submission timestamp
• Gives you a single completion view at any time

Mental model:

Kickoff -> Sync -> Remind -> Close

Kickoff creates rows, sync updates rows, reminders chase the gaps.

What this replaces (outcome delta)

The time savings show up where it hurts most: follow-ups.

Why three flows instead of one

We split this into three flows for a reason:

• Reliability: each flow has a single responsibility
• Testability: you can run and debug flows independently
• Guardrails: you can pause reminders without breaking intake

Where it fits (and where it does not)

This pattern is ideal when silence is not acceptable and you have a known list of people:

• Quarterly or monthly status updates
• Grantee or portfolio reporting
• Policy attestations and training confirmations
• Client intake or document collection
• Budget requests and approvals

It is overkill if you only chase 3-5 people once a year. The setup cost is real: you are building a small system, not a single flow.

Minimum data model (SharePoint Lists)

The list names and column names are flexible. The structure is not.

1) Recipients list (projects, grantees, accounts)

Conceptual fields (examples in parentheses):

• Recipient identifier (ProjectKey, RecipientID)
• Primary contact email (PrimaryContactEmail)
• Internal owner (ProjectOwnerInternal)
• Recipient status (Active/Inactive)

2) Runs list (reporting periods)

Conceptual fields:

• Run identifier (PeriodKey, RunID)
• Open date
• Due date
• Kickoff sent flag (KickOffEmailSent)
• Run status (Open/Closed)

Example run from our build: RunID 2026-Q1, OpenDate 2026-01-18, DueDate 2026-01-20, Status Open.

3) Submissions list (one row per recipient per run)

Conceptual fields:

• Recipient identifier (links to recipient)
• Run identifier (links to run)
• Response status (ResponseSubmitted Yes/No)
• Submitted timestamp
• Reminder count
• Last reminder timestamp
• Response fields (whatever you collect)

Flow 1: Kickoff (manual trigger)

Purpose: Start the run, create submission rows, send initial emails.

Trigger: Manual (operator starts the run)

Steps:

1. Get the open run
2. Guardrail: if no open run, stop
3. Guardrail: if kickoff already sent, stop
4. Get all active recipients
5. For each recipient, create a submission row, build a prefilled form URL, and send the kickoff email
6. Mark the run as kicked off

Flow 2: Sync responses (Forms trigger)

Purpose: Update submission rows when responses arrive.

Trigger: Microsoft Forms response submitted

Steps:

1. Get response details
2. Find the submission row by RecipientID + RunID
3. If the row exists, parse response fields and update the submission row
4. If the row does not exist, terminate and notify

Flow 3: Reminders (scheduled)

Purpose: Chase non-responders without spamming everyone.

Trigger: Recurrence (e.g., daily at 9:00 AM)

Steps:

1. Get open runs
2. Guardrail: proceed only if exactly one open run
3. Compute DaysToDue
4. Decide if today is a reminder day

If yes, do the following:

1. Get pending submissions
2. For each pending row, look up recipient contact
3. Build prefilled URL
4. Send reminder email
5. Update ReminderCount and LastReminderAt

Failure modes and guardrails

These are the common breakpoints and how the system handles them:

Copy/paste corner

Reminder cadence (pseudo-logic):

days_to_due = dateDiff(DueDate, utcNow())
send_today = days_to_due in [7, 3, 1]

Prefilled URL (example shape):

https://forms.office.com/...&RecipientID={RecipientID}&RunID={RunID}

Pending submissions filter (SharePoint example):

RunID eq '2026-Q1' and ResponseSubmitted eq false

Operator defaults (what we do every run)

• Every run gets a RunID. No exceptions.
• Kickoff creates submission rows before emails go out.
• We only remind when there is exactly one open run.
• Reminders are logged with count and timestamp.
• Form responses always update existing rows, never create new ones.
• Prefilled URLs are mandatory for reliable matching.

Quick start (build this in an afternoon)

1. Create the three lists with the conceptual fields above
2. Build the kickoff flow and send a test request
3. Build the sync flow and confirm the row updates
4. Build the reminder flow with a safe cadence
5. Run a small pilot (5-10 recipients)
6. Tune cadence and email copy, then scale

Who this is for

This pattern is best for teams running recurring collection cycles (quarterly or monthly) where:

• the recipient list is known
• silence is not acceptable
• compliance and audit trails matter

If you only chase a handful of people once a year, a lighter process may be enough.

The punchline

Once you stop thinking of this as "send a few emails" and start treating it as a run with state, the follow-up problem disappears.

If you want this pattern adapted to your workflows, book a Workflow Teardown. We will map your current process, then design the smallest system that makes the follow-up automatic.