Debtor Chasing on Microsoft 365
The ledger keeps the money. The tenant keeps the ranking, the gate and the record of who was contacted and what they said.
Used by
- AI Officer
- Business Systems Analyst
- Credit Controller
- Finance Manager
How to get this live in your business
Free. The Debtor Chasing Build Standard: the objects, the checks in order, and the pass test.
The debtor chasing standard is unusual among the ones we publish because the record it works from already exists somewhere else. Your accounting system knows who owes you. What it does not do is decide who to ring first, refuse to ring the ones you must not, or remember what the last conversation produced. That is what gets built here.
In short
What this is
A build plan for the debtor chasing standard on Microsoft 365. Four lists, four flows, six measures, and the ledger left exactly where it is.
The core rule
Balances are read, stamped and shown with their as-at time. A balance stored in SharePoint is a second ledger and it is the wrong one by tomorrow.
What it does not do
It does not decide to send a debtor to collections, apply a credit hold, or write off a debt. Those are decisions with consequences and they stay with a person.
The hard part
Not the ranking. The check that runs immediately before every contact, which is the one thing a filtered view cannot be.
Take the spec to Claude, ChatGPT or Copilot. The sentence to paste is one click away.
Want the whole thing by email?
This paper, plus the rest of the library. One click to stop them.
1. Before you start
Read the debtor chasing build standard first. It is the specification and this is one way to satisfy it. Where the two disagree, the standard wins and this page has a bug. The shared conventions for this stack are in building the standards on Microsoft 365.
One honest note about this standard in particular. It specifies behaviour, the buckets, the ranking and the gate, rather than a set of record shapes. The lists below are the minimum that satisfies its conformance checklist, not objects the standard itself names. Shape them to your ledger where that reads better; the checks are what you are being held to.
The standard also marks the compliance gate as the step not to hand-roll, and names a product for it. Everything here assumes the gate is either that product or a deliberate build you own. What this page will not do is show you how to assemble a calling system out of flows and hope the gate holds.
2. The lists, with their columns
Four lists. Note what is absent: there is no invoice table and no balance column, because the ledger has those and a second copy is the failure this build plan is most likely to cause.
For your developer. Show the fields
Site: Receivables
LIST Positions (refreshed, never hand-edited)
DebtorRef Text indexed. The ledger's own identifier.
DebtorName Text
OldestDueDate DateTime
DaysOldest Number derived on refresh, not stored by hand
AmountOutstanding Currency AS AT AsOf. Display with the stamp.
AsOf DateTime REQUIRED. Every view shows it.
Rank Number WRITTEN BY THE RANK FLOW ONLY
RankReason Text why this row is where it is
NextActionDate DateTime
Status Choice open | promised | broken | disputed | closed
LIST Contacts (append only)
DebtorRef Text indexed
Channel Choice call | email | sms | letter
AttemptedAt DateTime
Outcome Choice spoke | no_answer | wrong_number | refused
| promise | dispute
Notes Text
By Person
LIST Promises
DebtorRef Text indexed
PromisedAmount Currency
PromisedDate DateTime
MadeAt DateTime
State Choice open | kept | broken
Evidence Text what was said, in their words
LIST Suppressions (the gate reads this, every send)
DebtorRef Text indexed
Reason Choice do_not_contact | dispute | claim
| payment_plan | hardship | legal
AddedBy Person
AddedAt DateTime
ReviewBy DateTime blank means indefinite
ClearedAt DateTime blank means live
No Invoices list. No stored balance outside Positions.AsOf.Suppressions is a list rather than a flag on Positions on purpose: a debtor can be suppressed for a reason that outlives any particular position, and a flag gets cleared by the next refresh without anybody deciding to clear it.
3. Every check, and what enforces it
| The check | Enforced by | How |
|---|---|---|
| The ledger stays the source of truth for what is owed | Sync flow | Positions are refreshed from the accounting system and never edited in the tenant. A balance typed into SharePoint is a second ledger, and the wrong one within a day. |
| Ranking is not by age | Rank flow | A scheduled flow scores and writes Rank. Age is one input among several, not the sort. Nobody sorts a view. |
| Nothing is contacted without the gate passing first | Contact flow | The suppression check runs inside the contact step, immediately before the send, not as a filter on the list the operator is reading. |
| A promise to pay moves the follow-up | Promise flow | Recording a promise sets the next action date from the promised date and stops the sequence until it passes. A broken promise is its own state, not a return to the top of the list. |
| Every contact is recorded whether or not anyone answered | Contact flow | The Contacts list is append-only. No answer is an outcome and it is the one people skip recording, which is how the same debtor gets rung three times on Friday. |
| A dispute stops the chase and does not restart it | Suppression list | Dispute is a suppression reason with an owner and a review date. Clearing it is a person's decision and it is dated. |
4. The four flows
For your developer. Show the fields
FLOW 1 Refresh positions
trigger scheduled, as often as your connector allows
logic read the open ledger, upsert Positions by DebtorRef
set AsOf on every row touched
close rows that no longer appear
never write AmountOutstanding from anywhere but this flow
FLOW 2 Rank
trigger scheduled, after FLOW 1
logic score each open position on the standard's inputs,
write Rank and RankReason
note RankReason is not decoration. It is what lets a person
disagree with the order instead of ignoring it.
FLOW 3 Contact, with the gate inside it
trigger an operator action or an approved run
guard read Suppressions for this DebtorRef where ClearedAt is
blank. Any live row stops the send, and the refusal is
written to Contacts with outcome refused.
logic send, then append a Contacts row whatever happened,
including no_answer
never query the send list with the suppression already filtered
out. The gate must run per send, or it is not a gate.
FLOW 4 Promise, and the follow-up that moves with it
trigger a Promises item is created
logic Positions.Status = promised
Positions.NextActionDate = PromisedDate + your grace
suspend the sequence until that date
on the date: kept -> close, not paid -> State = broken,
Status = broken, and re-rank. Broken is its own state.Flow 3 is the whole standard. If a person can send from a view without passing through it, this build does not conform however good the ranking is.
5. The Power BI model and its measures
Positions one to many Contacts and Promises. Suppressions joins on DebtorRef. Measures in the model, not in a report filter.
For your developer. Show the fields
Ranked Not Worked =
CALCULATE (
COUNTROWS ( Positions ),
Positions[Status] = "open",
NOT ISBLANK ( Positions[Rank] ),
FILTER (
Positions,
ISBLANK (
CALCULATE (
MAX ( Contacts[AttemptedAt] ),
Contacts[AttemptedAt] >= TODAY () - 7
)
)
)
)
Contact Attempts = COUNTROWS ( Contacts )
Reach Rate =
DIVIDE (
CALCULATE ( COUNTROWS ( Contacts ), Contacts[Outcome] = "spoke" ),
[Contact Attempts]
)
Promise Kept Rate =
DIVIDE (
CALCULATE ( COUNTROWS ( Promises ), Promises[State] = "kept" ),
CALCULATE ( COUNTROWS ( Promises ), Promises[State] IN { "kept", "broken" } )
)
Suppressed Live =
CALCULATE ( COUNTROWS ( Suppressions ), ISBLANK ( Suppressions[ClearedAt] ) )
Stale Positions =
CALCULATE (
COUNTROWS ( Positions ),
DATEDIFF ( Positions[AsOf], NOW (), HOUR ) > 36
)Stale Positions is the one to alert on. Everything else on this page is worthless the moment the refresh quietly stops, and a refresh that stops looks exactly like a quiet week.
6. The order to build it in
- Create the four lists. Index DebtorRef on all of them.
- Build Flow 1 against a file export before you buy a connector licence. A nightly CSV is a legitimate first version and it proves the shape.
- Put AsOf on every view you build. If a number can be read without its stamp, somebody will read it without its stamp.
- Build Flow 3 with the gate, before Flow 2. Ranking a list you cannot safely contact is the wrong order to discover a problem in.
- Stage a suppression and confirm the send refuses. This is the pass test and it should be run before the first real contact.
- Build Flow 2, then Flow 4.
- Connect Power BI, add the measures, alert on Stale Positions.
7. Four traps specific to this build
Copying balances into SharePoint
The temptation is to import the aged list once and work from it. Within a day somebody has paid and the tenant is chasing money it already has, which is the most damaging thing this process can do. Store the reference and the position as at a stamped time, show that time on every view, and refresh on a schedule.
The accounting connector is premium
Unlike the rest of these build plans, this one usually needs a premium connector or a custom connector to reach the ledger, and that is a per-user or per-flow licence. Price it before you design around it. A nightly export to a file the tenant reads is a legitimate and much cheaper first version.
Suppression as a view filter
A filtered view is per person and per session. The moment somebody opens the unfiltered list, or a flow queries without the filter, the gate is gone. The check belongs in the step that sends, where it cannot be worked around.
Treating no answer as no contact
If only successful conversations are written back, the contact history understates itself and the ranking keeps promoting the same names. Write the attempt, then the outcome.
8. Where the automation stops
- Escalating to collections, applying a credit hold, or writing off a debt. Consequential decisions, and they stay with a person.
- Clearing a suppression. Adding one can be automatic; removing one is a decision with a name and a date on it.
- Anything said on a call that commits the business to a settlement or a payment arrangement.
- The compliance gate itself, which the standard names as the step not to hand-roll and which this plan assumes you have not.
The pass test. Stage a suppression you control. Add a Suppressions row for a test debtor with reason dispute and no ClearedAt, then try to contact that debtor through the normal path. The send must refuse, and a Contacts row must appear with outcome refused naming the suppression. Then clear the suppression and confirm the same debtor becomes contactable and that the clearing carries a person and a timestamp. Both take two minutes on your own data and neither needs a real debtor. If the send goes out, your gate is a view filter; if the refusal leaves no record, you cannot later show what the system did or did not do.