The Service Pack on Microsoft 365
The unbound queue is the whole build. Every list below exists so that one value can be trusted, which means the close has to be a flow that refuses rather than a status somebody sets.
Used by
- Business Systems Analyst
- Compliance Officer
- Operations Manager
How to get this live in your business
The standard says that if you implement one thing from it, implement the unbound queue and put its age on a chart. That is a harder ask on this stack than it sounds, because SharePoint will cheerfully let a person set a status column to whatever they like, and the first time the unbound number is embarrassing somebody will. So the queue is a value only a flow writes, the close is a flow that can refuse, and what it refuses on is a table of required evidence keyed by asset class rather than a set of tick boxes that fit one class and lie about three.
In short
What this is
A build plan for the service pack standard on Microsoft 365. A library, nine lists, four flows, eight measures, and one queue that matters more than the rest of it put together.
The core rule
The close flow counts the evidence this asset's class requires before the record can be anything but unbound. Chasing evidence afterwards is the failure, not the fix.
What it does not do
It assembles a complete record and puts it in front of the person who signs. It does not certify, and it does not decide a pass.
The hard part
One set of tick boxes across the whole register is wrong for three of the four classes, which is why the close is a flow reading a table rather than a validation formula on a column.
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 service pack 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.
Find out where the tags are already recorded, and read from there rather than migrating. The standard’s provider block is explicit: connect what exists, do not migrate, and the asset register is wherever the tags already are. If that is a job system, Assets below is a mirror keyed on the printed tag and refreshed on a schedule, and nobody edits it in SharePoint. If it turns out to be a spreadsheet on somebody’s laptop, that is the real first task and no flow on this page fixes it.
Then agree the defect codes, per class. The standard says codes that mean something for a portable item mean nothing for a passive one and that reusing the list is how a defect register becomes unreadable, so this is several closed lists rather than one. Creating a Choice column takes a minute. Getting the technicians and the coordinator to agree its values is the work, and doing it after the build is live means recoding everything already in the register.
2. The lists, with their columns
For your developer. Show the fields
Site: ServicePack
LIBRARY EvidenceFiles (the photographs and the certificates)
RecordId Text indexed, REQUIRED. A file with no record is not evidence, it is a file.
Kind Choice identity | location | tag | certificate
no reading. Readings are numbers.
CapturedBy Person
CapturedAt DateTime
LIST Assets (mirrored from wherever tags live)
AssetId Text indexed. Printed on the tag.
SiteId Text indexed
Class Choice portable | system_with_readings | electrical
| passive
LocationText Text what the location shot should show
Status Choice live | isolated | retired
LIST Obligations
ObligationId Text indexed
AssetId Text indexed
Interval Text the servicing cycle this asset is on
Jurisdiction Choice QLD | NSW | VIC
add every other state the business works in
NextDue DateTime
Status Choice live | discharged | breached
LIST ServiceRecords (one visit, and whether it counts)
RecordId Text indexed
AssetId Text indexed, REQUIRED. No asset, no record.
ObligationId Text REQUIRED. A visit that discharges nothing cannot be counted as coverage.
Result Choice pass | defect | isolated
set by the attending person. No flow writes this column.
PerformedBy Person
Competency Text the qualification relied on
BaselineSeen Choice seen | absent_nonconformance
no default. Blank refuses the close.
PerformedAt DateTime
Queue Choice pass | defect | blocked | unbound
written by the close flow ONLY
QueueSince DateTime stamped on entry, never reset. The age.
NextActionOwner Person
LIST RequiredEvidence (per class. Not one list for the register.)
Class Choice indexed. portable | system_with_readings
| electrical | passive
Kind Choice identity | location | tag | certificate
| reading
Required Boolean
LIST Readings (numbers in fields, so they trend)
RecordId Text indexed
Metric Text
Value Number never a photograph of a gauge
Unit Text
TakenAt DateTime
LIST Defects
RecordId Text indexed
Code Choice the closed list agreed for this class
the closed list agreed for THIS class
Owner Person REQUIRED
RaisedAt DateTime
RectifiedAt DateTime blank means live. Closing the visit does not close the defect.
LIST Isolations
RecordId Text indexed
AuthorisedBy Person REQUIRED
Reason Text REQUIRED
RevisitBy DateTime REQUIRED. Without it this is a defect wearing a different label.
ClearedAt DateTime blank means still isolated
LIST Findings (the tag that resolved to none, or two)
SiteId Text indexed
TagAsRead Text what the technician read
Matched Choice none | many
RaisedAt DateTime
Owner Person
LIST JurisdictionRules (dated, cited, per state)
Jurisdiction Choice indexed. QLD | NSW | VIC
add every other state the business works in
RetentionYears Number
WhoMayAttest Text
Citation Text REQUIRED
CheckedAt DateTime REQUIRED. A value with no checked date is a guess with a number on it.
Queue and Result are two columns because they answer two questions: what
the person found, and whether the record is complete enough to count.BaselineSeen is a Choice with no default rather than a Yes/No, and that is the most deliberate column here. A Yes/No defaults to No and cannot express having not looked, so blank, No and a recorded non-conformance collapse into one value. The standard says an empty field and a missing baseline are different facts and only one of them is reportable, and the column type is where that survives.
3. Every rule, and what enforces it
| The rule | Enforced by | How |
|---|---|---|
| A record cannot be created without resolving to exactly one asset | Required column | AssetId is required on ServiceRecords and looked up against Assets, so an unresolvable tag produces a Findings row and no record at all. That is the standard's finding rather than a note on the job. |
| Every record names the obligation it satisfies | Required column | ObligationId cannot be blank, so a visit that discharges nothing cannot be saved. Coverage is measured over Obligations rather than over visits, which is why this matters. |
| Result is recorded by the competent person, never computed | List design | Result is set by the attending person and no flow writes it. Queue is the exact opposite and only a flow writes it. Those are the only two columns split that way and the split is the standard. |
| Required evidence is a property of the asset class | RequiredEvidence list | The close flow reads the rows for this asset's class and counts EvidenceFiles and Readings against them. A single list validation formula cannot express this, which is why it is a table and a flow. |
| A missing certificate stops the job closing, and never raises a reminder instead | Close flow | Class electrical with no certificate file refuses. There is no else branch that posts a reminder and lets the record through, because a reminder that does not refuse is this check failing quietly. |
| Work marked done with required evidence missing sits in its own queue with its age | Choice column and a stamp | unbound is a value on the same Choice as pass, so a record cannot be both, and the pass measure reads Queue rather than Result. QueueSince stamps on entry and never resets, so the chart is an age and not a count. |
| Defects come from a closed list with an owner, so they can be counted and chased | Choice and required Person | There is no free-text column on Defects to write a paragraph into, and Owner is required, so a coded defect with nobody against it does not exist. |
| Retention and who may attest are dated, cited values per state | JurisdictionRules list | Read at pack time from the row for that site's jurisdiction, with the citation and the date it was checked beside it. A literal number of years in a flow is wrong in at least one state and will not say so. |
4. The four flows
For your developer. Show the fields
FLOW 1 Bind at capture
trigger a submission from the capture app
logic resolve the tag to exactly one Assets row
none, or more than one -> Findings row, stop. No
ServiceRecords row is created.
create the ServiceRecords row with AssetId and ObligationId
write each file into EvidenceFiles with RecordId on it
write each reading into Readings as a Number
never accept a reading as an image. A photograph of a gauge is a
location shot with a number in it, and nothing can trend it.
FLOW 2 Refuse the close
trigger a ServiceRecords item is modified, or the app asks to close
logic read RequiredEvidence for this asset's Class
count EvidenceFiles by Kind and Readings by Metric against it
Class = electrical and no certificate file -> unbound
BaselineSeen is blank -> unbound
Result = isolated and no Isolations row with
AuthorisedBy, Reason and RevisitBy -> unbound
Result = defect and no coded Defects row
carrying an Owner -> unbound
everything present -> Queue = pass | defect | blocked,
following Result
anything missing -> Queue = unbound, stamp QueueSince
ONLY if it was not already unbound
never post a reminder and let the record through.
FLOW 3 The isolation clock
trigger scheduled, daily
logic Isolations where ClearedAt is blank and RevisitBy is past
post to the channel with the asset, who authorised it and why
set the Obligations row back to live: an isolation
discharges nothing
never clear an isolation because a later visit passed. It clears
when somebody records that it cleared, and the escalation
stands until they do.
FLOW 4 Produce the pack
trigger an operator request naming an asset and a date range
logic read Jurisdiction from the Obligations row for that site
read JurisdictionRules NOW for retention, who may attest,
the citation and CheckedAt
assemble from ServiceRecords, EvidenceFiles, Readings,
Defects and Isolations as they stand at this moment
hand it to the person the rule says may attest
never save the assembled pack back as the copy of record. The next
correction makes it wrong and nothing on it will say so.Flow 2 stamps QueueSince only on entry into unbound, and that condition is easy to leave out. Without it the on-modified trigger refreshes the stamp every time anybody touches the row, the age chart the standard asks for reads near zero forever, and the oldest problem in the register becomes the one that looks newest.
5. The Power BI model and its measures
For your developer. Show the fields
Unbound = -- build this one first
CALCULATE ( COUNTROWS ( ServiceRecords ), ServiceRecords[Queue] = "unbound" )
Unbound Age Days = -- the chart the standard asks for
AVERAGEX (
FILTER ( ServiceRecords, ServiceRecords[Queue] = "unbound" ),
DATEDIFF ( ServiceRecords[QueueSince], NOW (), DAY )
)
Oldest Unbound Days =
MAXX (
FILTER ( ServiceRecords, ServiceRecords[Queue] = "unbound" ),
DATEDIFF ( ServiceRecords[QueueSince], NOW (), DAY )
)
Passes = -- Queue, never Result
CALCULATE ( COUNTROWS ( ServiceRecords ), ServiceRecords[Queue] = "pass" )
Coverage =
DIVIDE (
CALCULATE ( COUNTROWS ( Obligations ), Obligations[Status] = "discharged" ),
COUNTROWS ( Obligations )
)
Defects Open =
CALCULATE ( COUNTROWS ( Defects ), ISBLANK ( Defects[RectifiedAt] ) )
Isolations Lapsed = -- must be zero
CALCULATE (
COUNTROWS ( Isolations ),
ISBLANK ( Isolations[ClearedAt] ),
Isolations[RevisitBy] < TODAY ()
)
Baseline Non-conformances =
CALCULATE (
COUNTROWS ( ServiceRecords ),
ServiceRecords[BaselineSeen] = "absent_nonconformance"
)
Rules Unchecked This Year = -- the quiet one
CALCULATE (
COUNTROWS ( JurisdictionRules ),
DATEDIFF ( JurisdictionRules[CheckedAt], TODAY (), DAY ) > 365
)Passes reads Queue and not Result, and that one word is the standard compressed into a measure. A competent person recorded a pass, the tag photograph never arrived, so the record is unbound and it is not coverage. Any measure that counts Result as coverage will report a register in better shape than it is, which is the exact failure the unbound queue exists to make visible.
6. The order to build it in
- Mirror Assets from wherever the tags are already recorded, and load RequiredEvidence for one class only. Portable items are the right first class because they are the ones where the location shot gets dropped.
- Create ServiceRecords with AssetId and ObligationId required and nothing else enforced. Even at this point a record cannot exist against the wrong thing or discharge nothing, which is the first two checks with no flow written.
- Add the Queue column with unbound among its values, and QueueSince beside it. Set it by hand for a fortnight if you have to. The standard says implement this queue before anything else and it is useful before any flow exists.
- Connect Power BI and put Unbound Age Days on a chart. Everybody's number is near zero until it is measured, and you want that argument settled before you spend a month building flows nobody has agreed to.
- Build Flow 2, the refusal, for that one class. It is the only flow here that changes behaviour rather than describing it, and everything after this is easier once it exists.
- Build Flow 1 and the capture app. One capture surface, not two: if the job system already has an app the technicians use, that is the one.
- Load JurisdictionRules for every state you work in, with the citation and the date checked, then build Flow 4.
- Build Flow 3, then run the pass test.
7. Four traps specific to this build
One required-evidence checklist for the whole register
The obvious SharePoint move is three Yes/No columns on ServiceRecords: tag photo, location photo, certificate. It is correct for portable items, meaningless for a passive one and actively wrong for a system with readings, so technicians tick boxes that do not apply and the one that mattered is the blank nobody notices. Required evidence is a row in a table keyed by class, and the close flow reads it rather than a column rule.
BaselineSeen as a Yes/No column
Yes/No is the natural type and it cannot hold the third state. It defaults to No, so a technician who never looked and a technician who looked and found nothing on file produce the same value, and the reportable fact disappears into the one that is not. Make it a Choice with no default and let a blank refuse the close.
A reading photographed and tagged as evidence
If EvidenceFiles has a Kind of reading, a photograph of a gauge satisfies the count and leaves nothing to trend, and trending is most of the value. That is why reading is absent from the library's Kind column and present on RequiredEvidence: the close flow counts it against the Readings list, where Value is a Number, or it counts nothing.
Keeping the pack that Flow 4 produced
SharePoint will store the output forever if you let it, and the day a record is corrected every saved copy is wrong with nothing on its face to say so. If a copy has to be kept because somebody was sent it, keep it in a separate library named for what it is, an issued copy, with the record and the time it was issued on the row, and never read it back as the record.
8. Where the automation stops
- The signature. In New South Wales only an accredited practitioner may assess each measure and endorse the annual statement, in Queensland the occupier signs the yearly statement, in Victoria the owner prepares the annual report. The build assembles the pack and produces it. It never attests.
- Deciding a pass. Result is recorded by the competent person who attended, and nothing in the model derives it from the evidence however complete the evidence looks.
- Deciding competency. This is licensed work. The Competency column records what was relied on; it does not verify it, and a flow that checked a text field against a list would be pretending to.
- Interpreting the instrument. JurisdictionRules is loaded by somebody who knows, with the citation and the date they checked stored beside the value, and a wrong load looks exactly like a right one.
- Saying a building complies. The pack is a record of what was done and when. It is not a statement that the asset or the building meets anything.
The pass test. Stage one completed visit on a portable item: identity, location and tag photographs, a recorded pass, a competent person on it, and BaselineSeen set. Close it and confirm Queue reads pass. Now delete the tag photograph from EvidenceFiles and let Flow 2 run again. The record must leave pass and land in unbound with QueueSince stamped at that moment, Passes must fall by one and Unbound must rise by one. Ten minutes in a test site. If deleting the evidence leaves the record at pass, Queue is a label somebody set rather than a value the close flow owns, and every number built on it describes a system you do not have. If the record vanishes from the pass count and nothing appears in unbound, you have built the tidy version, where the work is invisible instead of wrong, which is precisely the failure the standard exists to surface. And if QueueSince moves every time the flow runs, put the oldest record in the register on a chart and watch it read a day old: the age is the whole reason the stamp is there.