The Dispatch Board on Microsoft 365
One slot per job, a licence read on the day, and each party told exactly once. Two of those three are a column setting, and the third is the order you write a flow in.
Used by
- Business Systems Analyst
- Operations Manager
- Scheduler
How to get this live in your business
The interesting part of a dispatch board is not fitting jobs into a day. It is the four things that have to be impossible: a slot on an address nobody resolved, a second slot for one job, a gas job on a lapsed licence, and a customer told twice because a flow was rerun. SharePoint can make two of those unrepresentable with a column setting. The other two are the order the flow does things in, and Power Automate will happily let you get that order wrong.
In short
What this is
A build plan for the dispatch board standard on Microsoft 365. Seven lists, four flows, seven measures, and a job system beside it where one exists.
The core rule
Told once survives a rerun only if the message row is created before the send and the key is unique. A send action followed by a log is told once until the first retry.
What it does not do
Nothing in this stack geocodes an address or measures the drive between two of them. Both are inputs the build reads, and where they come from is a decision you make before you start.
The hard part
The change. The slot has to move, the version has to move with it, and each party gets exactly one fresh message, which is a different count from zero and a different count from two.
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 dispatch board build standard first. It defines its objects, and the lists below are those objects with SharePoint types on them. Shared conventions are in building the standards on Microsoft 365.
Decide one thing first: whether your job system can take the slot write and hand back its own id. The standard is explicit that ServiceM8, Simpro and Jobber can take a booked time against a job and that Tradify has no public API, so it cannot. That answer decides what the ExternalId column below means. Where the write exists, ExternalId is the job system’s own id and a blank one is a failed write. Where it does not, the tenant is the board, a person copies the slot across, and the standard says to keep that honest rather than pretend the id came from somewhere.
Two inputs this stack does not produce. A resolved address is not something SharePoint or Power Automate can derive: the resolution comes from a person confirming it or from a service outside the tenant, and the column below records which. Travel between two resolved addresses is the same. The standard says travel is read, never guessed, and a table of minutes somebody typed in March is a guess with a column name on it. Know where both come from before you build anything, because a board that fills up on unresolved addresses is worse than an empty one.
2. The lists, with their columns
For your developer. Show the fields
Site: Dispatch
LIST Jobs (path B: mirrored in, never hand-edited)
JobRef Text indexed, UNIQUE
CustomerRef Text indexed
RawAddress Text as the caller gave it
ResolvedAddress Text blank until it resolves. No slot without it.
ResolvedBy Person who or what confirmed it. See section 1.
WindowFrom DateTime
WindowTo DateTime
DurationMin Number
Requires Choice gas | electrical | height | none
multi-select, fill-in OFF
Urgency Choice routine | emergency
CustomerSaid Text multi-line. Their words, for the tech.
JobVersion Number the change flow increments this
Status Choice new | slotted | done
LIST Techs
TechRef Text indexed
Name Person
HomeBase Text
LIST Licences (one row per tech per code)
TechRef Text indexed
Code Choice gas | electrical | height
ExpiresOn DateTime read on the day of the slot, not at signup
LIST Slots (one per job, and only one)
JobRef Text indexed, UNIQUE
TechRef Text indexed
Start DateTime
End DateTime
ExternalId Text blank = a failed write, not a slot
SetBy Person blank when a rule set it
SetByRule Text which rule, when no person did
DisplacedBy Text the JobRef of the emergency that took it
AcceptedBy Person the dispatcher. Blank on a routine slot.
JobVersion Number the version this slot was made for
LIST Consent
CustomerRef Text indexed
Channel Choice email | sms
GivenAt DateTime
LIST Messages (append only. The proof of "once".)
MessageKey Text indexed, UNIQUE. on JobRef|JobVersion|Party
JobRef Text indexed
Party Choice customer | tech
Channel Choice email | sms | teams
SentAt DateTime blank until the send returns
BodyRef Hyperlink into Sent
LIST Escalations
JobRef Text indexed
Reason Choice no_licensed_tech | outside_area
| window_impossible | address_unresolved | displaced
Owner Person REQUIRED. A queue with no owner is a list.
RaisedAt DateTime
ClosedAt DateTime
LIBRARY Sent what each message actually saidTwo column settings carry most of this page. Enforce unique values on Slots.JobRef is what makes a second slot for one job impossible rather than merely unlikely, and the same setting on Messages.MessageKey is what makes told once true when a flow is rerun or resubmitted. Both need the column indexed, which SharePoint does for you when you turn the setting on.
3. Every rule, and what enforces it
| The rule | Enforced by | How |
|---|---|---|
| No slot until the address resolves | Slot flow | The flow reads ResolvedAddress before anything else and takes the escalation branch when it is blank. The address resolves outside this stack, so what SharePoint enforces is that an unresolved job cannot become a slot, not that the resolution happened. |
| A licence requirement is a rule read from the record, never assumed | Licences list | The flow filters Licences by the job's Requires code with ExpiresOn later than the slot date, at the moment of assignment. A licence is a dated row, not a Yes/No on the tech, because a tick cannot expire. |
| One slot per job, written once, updated on a second event | Slots.JobRef, unique | The flow looks for the row and updates it. If it tries to create one anyway, SharePoint refuses the create. The rule holds even when a second flow, a person or a rerun is what tried. |
| A null external id is a failed write, not a slot | Slot flow | A blank ExternalId raises an escalation and stops before the messages. Nobody is told about a slot that did not land, which is the failure mode where the customer has a time and the job system does not. |
| An emergency takes a slot only when a named dispatcher accepts the displacement | Approval action | Start and wait for an approval, then write the responder into AcceptedBy, then write the slot. The approval is not a notification the flow carries on past, and the displaced job is re-slotted or escalated in the same run. |
| The customer is told once per slot, on a channel they consented to | Consent + Messages key | The Consent row is read before a channel is chosen, and the Messages row is created before the send. A duplicate key fails the create and the send never runs. |
| The tech's notification carries the job, the address, the window and what the customer said | Notify step | The four values are columns, so the message is built from the record rather than from whatever the flow happened to be holding. CustomerSaid is multi-line on purpose: a bare new job alert is not a notification. |
| A job that cannot be slotted is escalated with a reason and an owner | Escalations list | Reason is a closed Choice from the standard's own list and Owner is a required Person column. Unassignable is a success state, so it has a row rather than an absence. |
4. The four flows
For your developer. Show the fields
FLOW 1 Slot
trigger a Jobs item is created, or Status is set to new
logic ResolvedAddress blank -> escalate address_unresolved, STOP
read Licences for each Requires code, ExpiresOn after the day
none free in the window -> escalate no_licensed_tech, STOP
place Start and End inside the window, after the previous
slot's End plus the travel you read
look for the Slots row for this JobRef
found -> update it
absent -> create it
write into the job system; no id back -> escalate, STOP
call FLOW 2 for the customer, then for the tech
never send anything before the ExternalId comes back
FLOW 2 Tell once (a child flow. One copy, two callers.)
trigger called with JobRef, JobVersion, Party
logic MessageKey = JobRef|JobVersion|Party
CREATE the Messages row first
create failed on the unique value -> already told, STOP
read Consent for the party's channel
none -> escalate with the customer named, STOP, no send
send, then write SentAt and BodyRef back onto the row
never send and then log. The row is the lock, not the receipt.
FLOW 3 Emergency
trigger a Jobs item with Urgency = emergency
logic find the slot it would displace and who holds it
Start and wait for an approval from the dispatcher
rejected or timed out -> escalate, STOP. No slot is moved.
approved -> write AcceptedBy and DisplacedBy
re-slot the displaced job through FLOW 1
cannot be re-slotted -> escalate displaced, with an owner
then slot the emergency, then FLOW 2 for both jobs
never move a slot and ask afterwards. No licence rule has an
approval branch; a gas job goes to a gas licence or nowhere.
FLOW 4 Change
trigger a Jobs item is modified: window, address or tech
logic increment JobVersion
run FLOW 1 again, which updates the one slot in place
call FLOW 2 for each party. The new version is a new key,
so each gets exactly one fresh message and no more.
never delete and recreate the slot. The history and the
acceptance on it are the record.Flow 2 exists as a child flow rather than as a copied block because told once is a property of the whole build, not of one flow. Two copies of the same send is how a customer hears twice about the same change, and the second copy is always the one somebody added in a hurry.
5. The Power BI model and its measures
For your developer. Show the fields
Silent Jobs = -- no slot and no escalation
VAR Slotted = VALUES ( Slots[JobRef] )
VAR Raised = VALUES ( Escalations[JobRef] )
RETURN
COUNTROWS (
FILTER (
FILTER ( Jobs, Jobs[Status] <> "done" ),
NOT ( Jobs[JobRef] IN Slotted ) && NOT ( Jobs[JobRef] IN Raised )
)
)
Slots Without Id = -- failed writes wearing a slot's clothes
CALCULATE ( COUNTROWS ( Slots ), ISBLANK ( Slots[ExternalId] ) )
Slots Not Told = -- a slot the customer never heard about
VAR Told = VALUES ( Messages[MessageKey] )
RETURN
COUNTROWS (
FILTER (
Slots,
NOT (
Slots[JobRef] & "|" & Slots[JobVersion] & "|customer" IN Told
)
)
)
Unassignable Open =
CALCULATE (
COUNTROWS ( Escalations ),
ISBLANK ( Escalations[ClosedAt] )
)
Displaced Awaiting =
CALCULATE (
COUNTROWS ( Escalations ),
Escalations[Reason] = "displaced",
ISBLANK ( Escalations[ClosedAt] )
)
Licence Blocked =
CALCULATE (
COUNTROWS ( Escalations ),
Escalations[Reason] = "no_licensed_tech"
)
Acceptance Latency Hours =
AVERAGEX (
FILTER (
Escalations,
Escalations[Reason] = "displaced"
&& NOT ISBLANK ( Escalations[ClosedAt] )
),
DATEDIFF ( Escalations[RaisedAt], Escalations[ClosedAt], HOUR )
)Silent Jobs is the front page. Every other number here describes something visible: an escalation you can read, a slot you can see. A job with neither is the one the standard is about, because it looks exactly like a job that does not exist, and the board is full either way.
6. The order to build it in
- Settle where a resolved address and a travel figure come from, and write both down. Everything below assumes they arrive.
- Create the seven lists. Turn on enforce unique values for Slots.JobRef and Messages.MessageKey before a single row exists, because SharePoint will not turn it on afterwards over data that already breaks it.
- Load Techs and Licences, one dated row per code. Check that at least one licence in your test data has already expired.
- Build Flow 1 as far as the Slots row, with the address branch and the licence filter in place. Stop there and confirm an unresolved job escalates instead of slotting.
- Build Flow 2 on its own and call it twice by hand with the same arguments. The second call must do nothing. This is the pass test.
- Wire Flow 2 into Flow 1, add the job system write and the ExternalId check.
- Build Flow 3. The approval comes before the write, and the displaced job is handled before the emergency is slotted.
- Build Flow 4, then connect Power BI. Silent Jobs goes on the front page and on a weekly post to the dispatcher.
7. Four traps specific to this build
The slot as a calendar entry
A shared Outlook or Teams calendar looks like the obvious home for a slot, and it is the wrong one. Anybody can drag an entry, there is nowhere to hang the external id, the job version or the dispatcher's acceptance, and a deleted entry leaves no trace of what it displaced. Keep the slot as a list item. Mirror it to a calendar if the techs want to see it there, one way only, and treat the calendar as a view that can be rebuilt.
A travel matrix that nobody dates
The standard says travel is read, never guessed. There is nothing in this stack that measures the drive between two addresses, so the tempting substitute is a list of minutes between suburbs. That is fine as a stated approximation and dishonest as a column called TravelMin with no as-at date. If you build one, stamp it, show the stamp wherever a slot is placed, and put the jobs it pushed late into a measure.
Sending before the message row exists
The natural shape is send, then log. It works until the first rerun, resubmit or retry, and then the customer gets the same confirmation twice and stops reading the next one. Create the Messages row first and let the unique key refuse the duplicate. The row is the lock. The send is what happens after the lock is held.
A licence as a Yes/No on the tech
Gas: Yes is the column everyone builds first, and it is right for about a year. A licence has an expiry and the rule has to read it on the day of the slot, not on the day somebody typed the tick. One row per tech per code with an ExpiresOn date, filtered at assignment. The tick cannot tell you it lapsed in March.
8. Where the automation stops
- Accepting an emergency displacement. The approval writes a name onto the slot it moved, and that name is the point of the step.
- Deciding a job is outside the area, or that a window cannot be met. The board raises the escalation; the judgement is a person's.
- Anything said to a customer who is upset that the time moved. The build sends one factual message per version and stops there.
- Keeping the licence record true. The flow reads Licences on the day; a person keeps the rows current, and no amount of enforcement in SharePoint fixes a row that was never updated.
- Resolving an address the service or the person could not. That job sits in the queue with a reason until somebody rings the customer back.
The pass test. Stage one job in your own tenant. Put a gas requirement on it and make the only tech free in the window one whose Licences row expired yesterday. Run the slot flow: there must be no Slots row at all, and one Escalations row reading no_licensed_tech with a named Owner. Now extend that licence and run the flow twice, an hour apart, without changing anything else. You must end with exactly one Slots row carrying an ExternalId, and exactly one Messages row for the customer. Finally move WindowFrom by two hours and let the change flow run: the same Slots row moves, JobVersion goes up, and the customer and the tech each gain exactly one more message. Ten minutes on test data. If a second Slots row appeared, enforce unique values is off and one job will get two vans. If the customer has two messages for one version, the flow sent before it wrote the row, and every retry from here on is another text. If the gas job got slotted at all, the licence is being read from something other than a dated record.