The Advice File on Microsoft 365
Field keys as a list, verbatim before any field, and a send that carries a person's name. The decision that makes or breaks it is whether a flow can write to the CRM at all.
Used by
- AI Officer
- Business Systems Analyst
- Compliance Officer
How to get this live in your business
The standard says the file is the product and the assistant may write into it without being the source of anything in it. On this stack that is mostly a matter of which list a value came from, and it is enforceable. The part that is not is the sentence saying the CRM record is the file. If the network runs on a broking platform your tenant cannot write to, this build gives you a very tidy second register, which the standard is explicit is worse than none.
In short
What this is
A build plan for the advice file standard on Microsoft 365. Nine lists, four flows, a Power BI page, and a recording and mapping step you buy rather than build.
The core rule
Required keys for the purpose, minus the keys present, every time somebody asks. A column called Complete is the exact defect the standard exists to make unrepresentable.
What it does not do
It fills the file, attaches a card and names the gap. It does not approve a loan, select a product or certify anything, and SentBy holds a person.
The hard part
Not the transcription. Getting a verified write into the system the broker already opens, and getting an external id back that proves it landed.
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 advice file 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.
Settle the CRM question before you create a single list. The standard says the CRM record is the file, a second interview updates it, and a null external id is a failed interview rather than a completed one. That requires a write your tenant can make and an id it can read back. If the network’s platform has an interface a flow can call, build against it and treat the lists below as the working layer around it. If it does not, stop here and say so: a SharePoint mirror of a file that lives somewhere else is the second register the standards warn about, and it will drift inside a fortnight.
Second, be clear about what you are not building. The recording, the transcription and the mapper from an utterance to a field key are bought, as the standard says. Nothing in this plan listens to a call. It receives verbatim text with spans and a proposed field key, and everything after that is lists and gates.
2. The lists, with their columns
For your developer. Show the fields
Site: AdviceFiles
LIST FieldKeys (the published contract. Nothing else writes.)
FieldKey Text indexed. The key. Never renamed.
Label Text
Purpose Choice purchase | refinance | invest | all
Required Boolean
Blocks Choice application | recommendation | settlement
| none
ContractVersion Number
LIST Interviews
InterviewId Text indexed
ClientRef Text indexed
OfficeId Choice the network's own office codes
the network's offices, cut to yours. The flow writes it.
Broker Person
StartedAt DateTime
EndedAt DateTime
Channel Choice voice | video | face
DisclosureAt DateTime must precede the first extract
ConsentToRecord Boolean
ConsentToTranscribe Boolean
RecordingUri Hyperlink
TranscriptUri Hyperlink
LIST Extracts (the client's own words, kept)
ExtractId Text indexed
InterviewId Text indexed
FieldKey Text lookup to FieldKeys
Verbatim Note what was said, before classification
SpanStart Number
SpanEnd Number
NormalisedValue Text blank where confidence was low
Confidence Number
WrittenToFile Boolean
WrittenAt DateTime
LIST AdviceFiles (one live row per client, never two)
FileId Text indexed
ClientRef Text indexed, UNIQUE
ExternalId Text indexed. Blank means the interview FAILED.
OfficeId Choice the network's own office codes
Purpose Choice purchase | refinance | invest | all
decides which keys are required
ResponsibleLendingGate Choice open | blocked | waived
WaivedBy Person a person, or the gate is not waived
LastInterviewId Text
LIST FileFields (one row per key present on a file)
FileId Text indexed
FieldKey Text lookup to FieldKeys
Value Text
ExtractId Text REQUIRED. No field without a verbatim.
InterviewId Text indexed
WrittenAt DateTime
LIST MissingFields
FileId Text indexed
FieldKey Text lookup to FieldKeys
Reason Choice not_asked | asked_unclear | declined
| unverified
Blocks Choice application | recommendation | settlement
| none
copied from FieldKeys on create
TaskId Text blank until raised, once
RaisedOn DateTime
ClosedOn DateTime
ClosedByInterviewId Text
LIST Drafts
DraftId Text indexed
FileId Text indexed
ProductIds Text
Queue Choice draft | blocked | ready_for_person
| sent_by_person
SentBy Person
SentByUpn Text written by the send flow only
SentAt DateTime
LIST DraftCards (the join. Cards live in the panel library.)
DraftId Text indexed
CardId Text indexed. from the cited answer build
AttachedAt DateTime
LIST CoachingEvents
InterviewId Text indexed
Broker Person
FindingKey Choice living_expenses_not_asked | disclosure_late
| rate_quoted_on_call | purpose_not_confirmed
a closed list, fill-in OFF
SpanStart Number
SpanEnd Number
CardId Text
Severity Choice note | gap | breach_suspect
AcknowledgedBy Person
AcknowledgedAt DateTime
There is no Complete column anywhere. Look for one in code review.FieldKey is a lookup into FieldKeys on both FileFields and MissingFields, which is what makes an unpublished key unwritable rather than merely discouraged. The cards themselves are not duplicated here: they belong to the panel library the cited answer build owns, and DraftCards holds the ids that were attached at the time, because a card looked up later may have moved.
3. Every rule, and what enforces it
| The rule | Enforced by | How |
|---|---|---|
| Disclosure and consent are recorded before the first field | Interview flow | The extract flow refuses to run while DisclosureAt is blank on the interview, and records the refusal. Refused consent means a person types notes, and no Extracts rows are written at all. |
| Verbatim is stored before any field is filled | ExtractId column | FileFields requires an ExtractId, and the extract row carries the verbatim and its span. Classification is applied to a copy because the copy is a row of its own, written first. |
| Only published field keys are writable, and completeness is computed | Lookup column | FieldKey is a lookup into FieldKeys, so a free-text summary has nowhere to be saved. Completeness is a DAX measure over the required keys for the purpose and exists nowhere as a stored value. |
| Anything with a lookup is resolved, read back, and written as resolved | Confidence threshold | The flow writes NormalisedValue only above the threshold your resolver reports. Below it, the row goes to MissingFields with reason asked_unclear rather than a guessed figure landing in Value. |
| One live file per client, updated and never duplicated | ExternalId | The flow writes to the CRM, reads the id back and stores it. A blank ExternalId is an error path, not a completed interview, and ClientRef is enforced unique so a second interview updates rather than inserts. |
| Every blocking gap is a task with an owner and a due date, raised once | TaskId column | The task flow checks for an open MissingFields row with the same FileId and FieldKey before creating anything, and writes the task id back onto the row. A reminder in a chat message is not a task. |
| A recommendation cannot leave draft with a blocking gap or no card | Send flow | Ready_for_person is set by the flow after it counts open blocking gaps and DraftCards rows. Neither count is stored on the draft, so neither can be stale when the gate reads it. |
| Coaching cites a span or a clause, and breach_suspect goes to a named person | CoachingEvents | A finding with no span and no CardId is refused at write. Severity of breach_suspect triggers an approval to the named compliance person, and AcknowledgedBy stays blank until they act. |
4. The four flows
For your developer. Show the fields
FLOW 1 Open the interview
trigger the broker starts a recorded conversation
logic write the Interviews row: Channel, Broker, ClientRef
write DisclosureAt and the two consent flags
never let anything downstream run while DisclosureAt is blank.
No consent means a person types notes, and there is no
file the assistant pretends it heard.
FLOW 2 Verbatim, then fields, then the CRM
trigger a transcript arrives for an interview
logic 1 write Extracts rows FIRST: Verbatim, SpanStart, SpanEnd,
proposed FieldKey, Confidence
2 for each extract above the resolver's threshold, write a
FileFields row carrying ExtractId and InterviewId
3 for each one below it, write a MissingFields row with
reason asked_unclear. No value is guessed.
4 write to the CRM and READ THE ID BACK into ExternalId
5 blank id -> the interview is failed, not completed,
and the file is not marked as anything else
never write a field whose key is not in FieldKeys, and never
write a field before its extract exists.
FLOW 3 Gap to task, once
trigger a MissingFields item is created
logic if Blocks is none, stop
look for another open MissingFields row with the same
FileId and FieldKey that already has a TaskId
found -> stop. Not found -> create one task with an owner
and a due date, and write TaskId back onto the row
when a later interview supplies the value, write ClosedOn
and ClosedByInterviewId, and close the task
never raise a second task for a gap that reopens. One key, one
file, one task.
FLOW 4 The send gate
trigger a person asks to send a draft
logic count open blocking MissingFields for the file
count DraftCards rows for the draft
either count is wrong -> Queue = blocked, and say which
both right -> Queue = ready_for_person
approval to a named broker or credit officer
on approve: Queue = sent_by_person, write SentBy,
SentByUpn and SentAt, then send
never let the flow occupy SentBy. A machine cannot be the person
who issued a recommendation.Flow 2 is written in that order because the order is the check. A mapper that posts a tidy object of fields is the common shape and it arrives with the verbatim already discarded, which leaves a file nobody can defend. Step 4 is the step not to hand-roll, and step 5 is the one everybody omits.
5. The Power BI model and its measures
For your developer. Show the fields
Files Without An External Id = -- must be zero
CALCULATE ( COUNTROWS ( AdviceFiles ), ISBLANK ( AdviceFiles[ExternalId] ) )
Completeness = -- computed here, stored nowhere
VAR RequiredKeys =
CALCULATETABLE (
VALUES ( FieldKeys[FieldKey] ),
FieldKeys[Required] = TRUE ()
)
VAR PresentRequired =
COUNTROWS ( INTERSECT ( VALUES ( FileFields[FieldKey] ), RequiredKeys ) )
RETURN
DIVIDE ( PresentRequired, COUNTROWS ( RequiredKeys ) )
Blocking Gaps Open =
CALCULATE (
COUNTROWS ( MissingFields ),
ISBLANK ( MissingFields[ClosedOn] ),
MissingFields[Blocks] <> "none"
)
Gaps With No Task = -- must be zero
CALCULATE (
COUNTROWS ( MissingFields ),
ISBLANK ( MissingFields[ClosedOn] ),
MissingFields[Blocks] <> "none",
ISBLANK ( MissingFields[TaskId] )
)
Gap Age Days =
AVERAGEX (
FILTER ( MissingFields, ISBLANK ( MissingFields[ClosedOn] ) ),
DATEDIFF ( MissingFields[RaisedOn], TODAY (), DAY )
)
Uncited Drafts = -- must be zero
COUNTROWS (
FILTER (
Drafts,
Drafts[Queue] IN { "ready_for_person", "sent_by_person" }
&& ISBLANK ( CALCULATE ( COUNTROWS ( DraftCards ) ) )
)
)
Machine Sends = -- must be zero
CALCULATE (
COUNTROWS ( Drafts ),
Drafts[Queue] = "sent_by_person",
ISBLANK ( Drafts[SentByUpn] )
)
Breach Suspect Unacknowledged =
CALCULATE (
COUNTROWS ( CoachingEvents ),
CoachingEvents[Severity] = "breach_suspect",
ISBLANK ( CoachingEvents[AcknowledgedBy] )
)Completeness takes the required keys from whatever purpose is in filter context, which is why Purpose sits on the file rather than being inferred. Put Files Without An External Id on the front page even though it will read zero for months: it is the number that tells you the day the CRM write started failing quietly, and everything else on the page is wrong from that day on.
6. The order to build it in
- Prove the CRM write and the id coming back, with one hard-coded record and nothing else built. If this cannot be done, stop and report it rather than building the rest.
- Create FieldKeys and fill it from the network's published contract. Required and Blocks are the two columns everything downstream reads.
- Create AdviceFiles and FileFields with FieldKey as a lookup. Enforce ClientRef unique. Resist every request for a Complete column.
- Build Flow 2 as far as step 3, with a transcript you paste in by hand. Confirm a field cannot be written without an ExtractId.
- Add steps 4 and 5 and confirm that a deliberately broken CRM call leaves the file with a blank ExternalId and nothing marked done.
- Create MissingFields and build Flow 3. Run two interviews on the same client and confirm the second closes the gap and raises no second task.
- Build Flow 4 with the cards from the panel library. Try to send with a gap open, and confirm it blocks and says which key.
- Connect Power BI. Machine Sends and Uncited Drafts on the front page at zero, Completeness by office beside them.
7. Four traps specific to this build
A column called Complete
Somebody will ask for it in week two, for a view. The standard makes a file that reads complete while a required key sits in MissingFields the defect it exists to prevent, and a stored flag is exactly how that becomes representable: the flow that keeps it in step will miss a case, and the case it misses is the one in the file somebody later reads back. Completeness is the measure above and it is recomputed every time it is looked at. If a view needs it, the view reads the model.
Letting the list become the file
If the CRM write cannot be built, the tempting fallback is to treat AdviceFiles as the record and sync later. Do not. The standard's point is that the broker opens one thing and it is the file, and a second store that nobody lives in drifts inside a fortnight and is then defended by whoever built it. The honest output is a finding: the network's platform does not expose a write, so this standard cannot be implemented on the tenant until it does.
A gate on the flow is not a gate on the library
Flow 4 controls the path Flow 4 owns. It does not stop a broker opening the drafts library and attaching the document to an ordinary email, and no permission on a list changes that for somebody who has to be able to read their own drafts. Keep drafts in a library the client-facing path never reads from, have the send flow copy rather than link, and watch Machine Sends and Uncited Drafts as the detectors. Then describe the gate accurately to the licensee instead of over-claiming it.
Taking the mapper's output as the only copy
Transcription vendors hand back a tidy object of fields, and writing it straight into FileFields feels like the whole job done. The verbatim is gone at that point and the file is hearsay: nothing can show what the client actually said or where in the recording they said it. Write Extracts first with the span, every time, even when the mapper is confident. ExtractId being required on FileFields is what makes that a rule rather than a habit.
8. Where the automation stops
- Recording and transcription consent on the call. The flow records that it was given; it cannot give it.
- The responsible-lending assessment, and the decision that the loan is not unsuitable.
- The product recommendation issued to the client. SentBy holds a person and no flow may write into it.
- Acceptance of a lender-policy change into the live library, which belongs to the panel library rather than to this build.
- Waiving a blocking missing field, and escalating a breach_suspect coaching finding to compliance.
The pass test. Stage two interviews on one client on the same day. In the first, have the savings figure said unclearly and the address said slightly wrong. You must get one AdviceFiles row with a non-blank ExternalId, the resolved address in FileFields, the savings key absent from FileFields and present in MissingFields with reason asked_unclear, one task with an owner and a due date, and no draft above blocked. Search FileFields for a dollar amount: there must not be one. In the second interview the savings figure is clear. The same file updates, the gap closes with ClosedByInterviewId set, no second task appears, and the draft becomes ready_for_person only once a card is attached. Then decline the approval and confirm Queue stays where it was with SentBy blank. An afternoon, on a test client in the real CRM. If a dollar figure appeared, the mapper guessed and the file now contains something the client did not say. If there are two files, the external id was not read back. If Completeness reached one while the gap was open, completeness is a column somewhere and the standard is not implemented.