for Trailmark Fund Solutions

Automation Flows

Every flow in the nightly reporting automation, block by block as it is built in Power Automate — trigger, actions, conditions, branches. Dashed blocks run outside Power Automate (an Azure Function, an Office Script, or an Automation runbook) and name what runs them.

How to read these

Power Automate step (trigger / action / condition) Runs outside Power Automate (named in the block)

Blocks run top to bottom; a condition splits into branches.

Identity and authentication

Component	Identity	Scope / permissions
Power Automate cloud flows — Azure Blob trigger, SharePoint, Approvals, Outlook (Scenario A)	Dedicated service account (Microsoft Entra ID Integrated, token-based connections)	Cloud flows can't use Managed Identity; each connector connection is authorized on the service account
Azure Function — email draft & send (Scenario B only)	Managed Identity	Graph Mail.ReadWrite + Mail.Send, scoped to reporting@trailmark.us via Exchange App RBAC
Automation Account — SFTP-window runbook	Managed Identity	Scoped to the storage account
Office Scripts — workbook refresh	Delegated service account	Excel workbook API is delegated-only (no app-only)

Flow 1 — SFTP delivery window

Not a Power Automate flow. An Azure Automation Account runbook opens the SFTP endpoint only for the nightly window, because the endpoint bills per hour while enabled.

Automation · Schedule

Open window (nightly, e.g. 20:45)

A schedule on the Automation Account starts the runbook.

Automation · Runbook

Enable SFTP on the storage account

Set-AzStorageAccount -EnableSftp $true · auth by Managed Identity scoped to the storage account.

…

Outside automation

Fund accounting delivers the night's files over SFTP

Files land in the Blob container root — this is what Flow 2 waits for.

Automation · Schedule

Close window (e.g. 23:30)

A second schedule runs the runbook again.

Automation · Runbook

Disable SFTP on the storage account

Set-AzStorageAccount -EnableSftp $false · endpoint stops billing until the next window.

Two short jobs a night sit inside the free Automation job-minutes. The window can only be tightened if the nightly cadence stays a fixed batch — see the open Cadence question.

Flow 2 — File intake & organize

Power Automate cloud flow. Turns a delivered file into a renamed, sorted, logged copy across the three SharePoint libraries.

Trigger

When a blob is added or modified (properties only)

Auth by a dedicated service account (Entra ID, token-based). Watches the container root only; a one-time backfill covers pre-existing files.

Action

Get blob content

Read the delivered file.

Action · SharePoint

Create file → RAW data library

Original, untouched, original name. The team can see exactly what arrived.

Action · Compose

Parse the filename

Split into Report Type / Reporting Date / Fund code; pick the newest by created timestamp.

Action · SharePoint

Get items → Clients list

Match the fund to its Working folder.

Action · SharePoint

Create file → Working / {client}

The copy staff work on, renamed to the Trailmark standard convention. Renaming happens here, at intake (Step 2).

Action · SharePoint

Create item → Run Log

First status row for the run: arrived (client, file, timestamp).

Flow 3 — Nightly report build

Power Automate orchestrates; the workbook refresh and report cleanup run as Office Scripts against the real Excel engine.

Trigger

When a file is created → Working / {client}

Kicks off once the renamed working copy lands (or on a per-client schedule).

Office Script (Excel Online)

Refresh the reporting workbook

Same Excel formulas as today, run by the real calc engine. Runs under a delegated service account (Excel's Graph Workbook API has no app-only support). Limits: 120s, 5MB/request, 1,600 runs/user/day.

Office Script (Excel Online)

Produce the clean client report

Renamed PDF + Excel with internal columns removed.

Action · SharePoint

Create item → NAV Values

Append tonight's row: Fund, As-of date, Share price (from the EOD file). A dated history, never overwritten.

Action · SharePoint

Create file → Working / {client}

Save the finished report next to its source.

Action · SharePoint

Update item → Run Log

Status: report generated.

Flow 4 — Email: build, approve, send

Power Automate builds the message from the SharePoint lists, then follows one of two scenarios for review and send. Which one we build is not yet decided — it turns on whether the reviewer only approves or must edit, and on attachment size.

Trigger

When the report is ready (per client / run)

Follows Flow 3.

Action · SharePoint

Get items → Notification Assignments

To / CC, subject & body templates, Send Mode, Approver — one row for this report.

Action · SharePoint

Get items → Email Sender Settings & NAV Values

The one shared footer, and tonight's share price for this fund.

Action · Compose

Merge subject + body

Substitute placeholders (date, NAV) and append the shared footer. From here, one of the two scenarios below runs.

Two scenarios — not yet decided

The split is one question: does the reviewer only approve, or do they need to edit the email before it goes out? Take Scenario A if approval is enough and attachments stay under 25 MB — it is simpler and fully out-of-the-box. Take Scenario B if the reviewer must edit the outbound email, or attachments can exceed 25 MB. To confirm with Matt: (1) approve vs. edit; (2) the typical nightly PDF + Excel size.

Scenario A — approve only, out-of-the-box Power Automate + Outlook · < 25 MB

Action · Outlook

Assemble the email + attach the report

Office 365 Outlook connector, from the shared mailbox: recipients, merged body, shared footer, and the report from the Working library. Attachments must stay under 25 MB. No Graph, no Azure Function.

Condition

Send Mode = Automatic?

If yes — Automatic

Action · Outlook

Send from the shared mailbox

"Send an email from a shared mailbox (V2)".

Action

Update Run Log — sent

If no — Manual (default)

Approval

Start and wait for an approval

Preview only: subject, recipients, body, attachment names. The reviewer approves; they do not edit the outbound email.

Condition

Outcome?

Approved

Action

Send from shared mailbox → Run Log (sent)

Rejected

Action

Update Run Log — rejected (stop)

Pure out-of-the-box Power Automate under the service account — no Graph, no Azure Function. Sending from the shared mailbox still leaves a proper Sent Items record.

Scenario B — editable draft via Graph Azure Function · edit in Outlook · up to 150 MB

Azure Function (.NET + Graph)

Build the draft in the shared mailbox

Graph Create message in reporting@trailmark.us, then upload attachments (LargeFileUploadTask for files over 3 MB, up to 150 MB). Returns the draft id + link. Managed Identity; Mail.ReadWrite + Mail.Send scoped to the one mailbox via App RBAC. Risk to confirm: Graph known issue #13644 can affect large-attachment upload sessions in a shared/delegated mailbox — validate against a real >3 MB nightly report during build.

Condition

Send Mode = Automatic?

If yes — Automatic

Azure Function

Send the draft

Graph messages/{id}/send.

Action

Update Run Log — sent

If no — Manual (default)

Approval

Start and wait for an approval

Preview + link to the draft, editable in Outlook Online before approving.

Condition

Outcome?

Approved

Azure Function

Send draft by id → Run Log (approved & sent)

Sends the (possibly edited) draft, so the reviewer's Outlook edits carry through.

Rejected

Azure Function

Delete draft → Run Log (rejected)

Adds an Azure Function (Managed Identity + Mail.ReadWrite + Mail.Send, App RBAC-scoped to the one mailbox) and Graph; orchestration, approval and Run Log stay in Power Automate. After send, the night's files archive to Archive / {year} / {date} and the Run Log row is finalised. Open points: approval timeout behaviour, and cleaning up drafts on reject or timeout.