Material Orders on Microsoft 365
Stock is read before anything is bought, the price comes from the supplier's own catalogue, and the docket is matched back rather than signed and filed. Six queues, and the list itself refuses the second order.
Used by
- Business Systems Analyst
- Operations Manager
- Project Manager
How to get this live in your business
Ordering looks like the easiest of these to automate, which is why the picking list is the half every tool ships. The half that costs money is the other one: the shelf nobody checked, the second order on Wednesday, the substitute nobody agreed to, and the docket that was signed on site and never compared to anything. All four are shapes in a list before they are flows, and the order the flows run in is the whole standard.
In short
What this is
A build plan for the material order standard on Microsoft 365. Twelve lists, a library, four flows, eight measures.
The core rule
The stock read comes before the draft and the catalogue read before the write. A build that orders first and checks afterwards has already spent the money.
What it does not do
It never accepts a supplier's substitution, never releases over the threshold, and never adds a supplier to the approved list. A named person does each of those three.
The hard part
Not the picking. The job ordered on Monday and again on Wednesday, and the delivery that was received as a photo instead of as lines.
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 material order 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: which of your suppliers has a catalogue this tenant can actually read. A wholesaler who will send a price file you can land in a list is one kind of supplier. A bloke with a phone number is another, and the standard is blunt about him: orders to that supplier park on every line until a person prices them. Sort your suppliers into those two piles before you build anything, because the second pile decides how much of this is a queue rather than a flow.
Then be honest about stock. The whole standard turns on reading what is already on the shelf or in the van, and a Stock list nobody maintains will quietly reserve things that are not there. If stock is not real, say so and start with on-hand at zero for everything: the build still works, it just never saves you the line it should have.
2. The lists, with their columns
For your developer. Show the fields
Site: MaterialOrders
LIST Jobs
JobRef Text indexed
StartDate DateTime
JobOwner Person
LIST JobMaterialLines
JobRef Text indexed
Code Text indexed
Qty Number
Description Text
LIST Stock
Code Text indexed
OnHand Number
Reserved Number
Location Choice store | van
LIST Reservations (written BEFORE any order exists)
JobRef Text indexed
Code Text indexed
Qty Number
ReservedAt DateTime
LIST Suppliers
SupplierRef Text indexed
Name Text
Approved Boolean a person set this
ApprovedBy Person
LeadDays Number
LIST Catalogue (theirs. Read at order time)
SupplierRef Text indexed
Code Text indexed
Price Currency
LeadDays Number
Edition Text
ReadAt DateTime
LIST Orders (one per job and supplier)
OrderKey Text indexed, UNIQUE. JobRef + SupplierRef
JobRef Text indexed
SupplierRef Text indexed
ExternalId Text the ordering system's id. Blank is a failure
CatalogueEdition Text
Total Currency
Status Choice draft | parked | waiting_release | sent
| delivered
ParkReason Choice not_in_catalogue | write_failed
ParkOwner Person
DraftedAt DateTime
ReleasedBy Person or the rule below, never both
ReleasedByRule Text
ReleasedAt DateTime
SentAt DateTime
ExpectedAt DateTime SentAt plus LeadDays
LIST OrderLines
OrderKey Text indexed
JobLineId Number the JobMaterialLines item this came from
Code Text
Qty Number
Price Currency written by the flow. Off every form
LIST ReleaseRules (a person writes this down once)
Name Text indexed. What ReleasedByRule names, so the release flow filters on it.
Threshold Currency
Owner Person
SetAt DateTime
LIST Deliveries
OrderKey Text indexed. No OrderKey, no delivery
DocketRef Text
DeliveredAt DateTime
DocketFile Hyperlink into Dockets
LIST DeliveryLines
OrderKey Text indexed
Code Text
QtyOrdered Number copied from the order, so the two sit beside each other in one row
QtyReceived Number
Reason Choice short | damaged | substituted
SubstituteCode Text
AcceptedBy Person blank on a substituted line is a variance
LIST Variances
OrderKey Text indexed. Blank only on no_order
DocketRef Text
Kind Choice short | over | wrong_item | damaged
| price_changed | substitution | no_order
Owner Person REQUIRED
RaisedAt DateTime
ClosedBy Person
ClosedAt DateTime
LIBRARY Dockets the signed docket, as received
no_order is the docket that matches nothing: escalated to a person,
and deliberately never recorded as a delivery.QtyOrdered is copied onto the delivery line rather than looked up, so one row carries both numbers and the short is visible without a join. That row is what the invoice check build reads when the supplier's invoice turns up, which is the entire reason the docket is matched here instead of filed.
3. Every rule, and what enforces it
| The rule | Enforced by | How |
|---|---|---|
| Every ordered line traces to a material line on the job | OrderLines.JobLineId | Each order line carries the JobMaterialLines item id it came from. A line with no id is a line somebody added, and nothing on the job is dropped without a parked order to explain it. |
| Stock is read before the order, and a covered line is reserved rather than ordered | Reservations, Flow 1 step order | The reservation row is written in its own step before any order row exists, and the draft reads Reservations rather than trusting what it decided a moment ago. |
| Every line carries the supplier's catalogue price, read at order time | Catalogue lookup, CatalogueEdition | Price is written by the flow and removed from every form. The edition is stamped on the order, so the price that moved between order and invoice is a fact rather than an argument. |
| A code no catalogue holds parks the order with reason not_in_catalogue | ParkReason choice | Two choices, fill-in off. A park always names one, and grouping the parked queue by code gives you the list of things your suppliers do not actually stock under that number. |
| One order per job and supplier, written once | Unique values on OrderKey | OrderKey is the job and the supplier joined and stored, not derived in a view. SharePoint refuses the second row, so a re-run updates or does nothing. |
| Over the threshold, or an unapproved supplier, needs a named person | Approval, Suppliers.Approved, ReleaseRules | The rule may release only when Total is at or under its threshold and Approved is yes. Either condition failing routes an approval to the rule's owner and the order waits. |
| Lead time is data, and a late order tells the job owner | LeadDays, ExpectedAt, Flow 3 | ExpectedAt is computed from the supplier's or the catalogue line's lead days. Where it lands after the job's start, the same run posts to JobOwner naming the order and the supplier. |
| A docket that matches no order is not a delivery | Deliveries.OrderKey, Flow 4 | Flow 4 will not create a Deliveries row without an existing OrderKey. It writes a Variance of kind no_order with the docket reference and an owner instead. |
4. The four flows
For your developer. Show the fields
FLOW 1 Draft the orders
trigger an operator action on a Jobs item, or a schedule over
jobs whose material lines changed
logic read Stock for every code on the job
write Reservations for the lines stock covers, IN THEIR
OWN STEP, before anything else
for the rest, read each supplier's Catalogue
a code no catalogue holds -> park not_in_catalogue
group the remaining lines by supplier
for each supplier: build OrderKey, and stop if that key
already exists
write OrderLines at catalogue prices, stamp
CatalogueEdition, create the Order as draft
never order a line that has a reservation, and never write a
Price that did not come from a Catalogue row.
FLOW 2 Release
trigger an Orders item reaches Status = draft
logic if Total is at or under a ReleaseRules threshold AND the
supplier is Approved: write ReleasedByRule, ReleasedAt
otherwise Status = waiting_release and start an approval
to the rule's Owner
write ReleasedBy and ReleasedAt from the outcome
never let the rule release an unapproved supplier, whatever the
total. Two conditions, both of them.
FLOW 3 Send, and warn on lead time
trigger ReleasedBy or ReleasedByRule is written
logic hand the order to whatever puts orders in front of the
supplier, and store what comes back in ExternalId
nothing back -> Status = parked, reason write_failed
ExpectedAt = SentAt plus LeadDays
if ExpectedAt is after the job's StartDate, post to
JobOwner naming the order, the supplier and the date
never set SentAt on a parked order, and never leave a blank
ExternalId sitting in draft as though it had gone.
FLOW 4 Receive the docket
trigger a file lands in Dockets, or an operator action
logic find the Order by OrderKey. No match -> a Variance of
kind no_order, an owner, and NO delivery row
write one DeliveryLine per ordered line, carrying
QtyOrdered beside QtyReceived
each difference -> a Variance with its kind and an owner
a substituted line with a blank AcceptedBy is a Variance
of kind substitution and is not counted as received
never absorb a difference into the job's cost. Every gap is a
row somebody owns.The reservation being its own step in Flow 1, before the catalogue read, is the part that looks fussy and is not. Flows retry. A reservation written in the same branch as the order write means a retry can order the line the van already covers, and the cheapest material in the business is the one already on the shelf.
5. The Power BI model and its measures
For your developer. Show the fields
Sent Without Release = -- must be zero. Front page.
CALCULATE (
COUNTROWS ( Orders ),
NOT ISBLANK ( Orders[SentAt] ),
ISBLANK ( Orders[ReleasedBy] ),
ISBLANK ( Orders[ReleasedByRule] )
)
Write Not Landed =
CALCULATE ( COUNTROWS ( Orders ), ISBLANK ( Orders[ExternalId] ) )
Lines Reserved Share = -- what the shelf saved you
DIVIDE ( COUNTROWS ( Reservations ), COUNTROWS ( JobMaterialLines ) )
Catalogue Gaps = -- group this by code
CALCULATE ( COUNTROWS ( Orders ), Orders[ParkReason] = "not_in_catalogue" )
Waiting Release Ageing Days =
AVERAGEX (
FILTER ( Orders, Orders[Status] = "waiting_release" ),
DATEDIFF ( Orders[DraftedAt], TODAY (), DAY )
)
Late Against Start =
COUNTROWS (
FILTER (
Orders,
NOT ISBLANK ( Orders[ExpectedAt] )
&& Orders[ExpectedAt] > RELATED ( Jobs[StartDate] )
)
)
Variances Open = -- put Kind on the axis
CALCULATE ( COUNTROWS ( Variances ), ISBLANK ( Variances[ClosedAt] ) )
Unaccepted Substitutions =
CALCULATE (
COUNTROWS ( DeliveryLines ),
DeliveryLines[Reason] = "substituted",
ISBLANK ( DeliveryLines[AcceptedBy] )
)Waiting Release Ageing Days is the one that tells you whether the threshold is set anywhere near right. A queue that sits for four days is not a control, it is a job that started without its materials, and the answer is usually to move the number rather than to remove the gate.
6. The order to build it in
- Load Suppliers with Approved set by a person, and load one supplier's Catalogue. One real catalogue proves more than four half-built ones, and the pile of suppliers with no catalogue can wait.
- Create Jobs and JobMaterialLines, and get one real job's material list into them from the job system.
- Create Orders with OrderKey indexed and unique values switched on, before any flow writes to it. This is the line that makes the second order impossible rather than unlikely.
- Build Flow 1 in two halves. First the stock read and the reservation, with no ordering at all: prove a covered line gets a Reservations row. Then the catalogue read and the park, before any order is written.
- Add the grouping and the order write to Flow 1, and take Price off every form on OrderLines.
- Build Flow 2 with the approval path only. Add the threshold rule afterwards, with both conditions, and test the unapproved supplier under the threshold: that is the case a one-condition rule gets wrong.
- Build Flow 3, prove the blank ExternalId parks, then add the lead time warning to the same run.
- Build Flow 4 last, starting with the docket that matches nothing, then connect Power BI with Sent Without Release and Variances Open by kind on the front page.
7. Four traps specific to this build
Deriving the order key in a view instead of storing it
One order per job and supplier only exists if OrderKey is a stored, indexed, unique column. A grouped view is a way of looking at lines, not a thing SharePoint will refuse to duplicate, and two runs will give you two sets of lines with neither of them being the order the supplier received.
Writing the reservation after the order
Power Automate retries, and a retry that re-enters the branch after the order write will buy the line the van already covers. The stock read and the reservation belong in their own step at the top of Flow 1, and the draft should read Reservations back rather than carry the decision in a variable.
The docket as an attachment on the order row
A photo attached to an order is a picture of an agreement, not a match. It cannot be counted, cannot be filtered, and answers nothing three weeks later when the supplier invoices for what the docket says. Write DeliveryLines with QtyOrdered beside QtyReceived, and keep the file in the library with a link on the row.
A tick box for accepting a substitution
A Yes/No column records that somebody ticked something. The standard asks for a named person who was on site and could see whether the substitute would do. Make AcceptedBy a Person column, let a blank one hold the line as a variance, and resist the version where the flow ticks it because the quantity matched.
8. Where the automation stops
- The release of any order over the threshold, or to a supplier not on the approved list. The rule covers the small and the familiar; everything else is somebody's decision with their name on it.
- Approving a supplier in the first place. Approved is a Yes/No a person sets, and no flow should ever write to it.
- Accepting a substitution, on site, with their name on the line. The build can hold the line as a variance forever; it cannot look at the fitting and say whether it will do.
- Every variance: short, over, wrong item, damaged, or a price that moved between the catalogue and the docket. The queue is the ask, and closing one is a person deciding what happens to the money.
- Matching and paying the supplier's invoice. This build produces the order and the matched docket; the third record and the decision belong to the invoice check standard, not here.
The pass test. Stage one job with four material lines: one the store covers in full, one code no catalogue holds, and two from an approved supplier that together sit over the threshold. Run the draft flow twice, an hour apart. You must get a Reservations row for the covered line and that line on no order at all, a parked order with reason not_in_catalogue and no price on it, exactly one order per supplier, and the over-threshold order at waiting_release with nobody having released it. Then record a docket one line short against a sent order, and a second docket against an order number that does not exist: the first gives you delivery lines with both quantities and a variance of kind short, the second gives you no delivery at all. Twenty minutes on test data. If the covered line was ordered, the stock read is running after the draft and you are buying what is already on the shelf. If a second order appeared for the same pair, OrderKey is not unique and the supplier is sending two of everything.