How We Built a SharePoint Case Management System for a Forensic Practice

When a forensic practice came to us, they had a growing problem. Their team was managing an increasing number of cases — quotes, instructions, expert reports, and a constant stream of client emails — across a combination of a shared drive, Outlook folders, and a spreadsheet acting as a case register. Nothing was findable. Emails were being missed. Cases were slipping between the cracks.

Eighteen months later they have a SharePoint-led operational platform with eight Power Automate flows running in production. Here's exactly what we built and how we built it.

The starting point: what they actually needed

Before designing anything, we mapped the business process. A forensic practice operates in two phases for each engagement: the quote phase (a potential instruction being evaluated) and the case phase (a confirmed instruction being worked). Every document, every email, every action ties back to one of those two states.

What they needed from SharePoint:

• A central register of all quotes with statuses, ages, and outcomes
• A case register that becomes the authoritative source of truth once an instruction is confirmed
• Active case files with a consistent folder structure across every case
• Email correspondence captured and filed against the right case automatically
• A KPI dashboard showing the health of the pipeline

The architecture: four connected components

The system is built on four main components, all within a single SharePoint site:

• Leads Data list — every quote, with Quote Reference, client details, instruction type, status, and Quote Age calculated daily
• Case Register list — confirmed cases only, with Case Reference, all case metadata, and status tracking
• Active Case Files library — document library where every case gets its own folder with six standard subfolders
• Email Filing Review list — staging area where captured emails land before being enriched and filed to Active Case Files

The eight flows: what each one does

Power Automate flows are what make the system operational rather than just a better filing cabinet. Here's each one:

1. Quote Reference Allocation

Triggered when a new record is created in Leads Data. Queries the existing list to find the highest current reference number, increments it, and writes the new reference back to the record. Concurrency controls prevent duplicate references when multiple quotes are created simultaneously.

2. Quote-Age Calculation

Runs daily on a schedule. Calculates the number of days since each open quote was created and updates the Quote Age column. Feeds the KPI dashboard and flags quotes that have gone stale — prompting follow-up before they're lost.

3. Lead-to-Case Conversion

This is the central flow — the most complex and the most important. When a lead is marked as won in Leads Data, the flow:

• Checks the Case Register for an existing case with the same reference (duplicate prevention)
• Creates the authoritative Case Register entry with all metadata from Leads Data
• Provisions the six-folder structure in Active Case Files: Correspondence, Expert Reports, Instructions, Financial, Research, and Completed
• Sets metadata on the new case folder (Case Reference, Client, Instruction Type)
• Creates a Trello card for the case in the appropriate board and list
• Writes a failure log entry if any step fails, so nothing is silently lost

4 & 5. Email Capture and Filing

The email system operates in two stages. Flow 5 monitors the shared mailbox and captures every incoming email to the Email Filing Review list — extracting subject, sender, received date, and critically the Internet Message ID. This unique identifier is what allows us to track the email after it's moved between Outlook folders.

Flow 6 then handles enrichment and filing. It reads the Quote Reference from the email subject line, looks up the corresponding case, enriches the record with Case Reference and Client Name, and moves the email's SharePoint representation to the correct subfolder in Active Case Files.

6 & 7. Automatic Document Metadata

When any document is uploaded to Active Case Files, a flow reads the folder path — which contains the Case Reference and Instruction Type in its structure — and writes those values to the document's metadata columns. Staff get correctly tagged documents without doing anything manually.

A companion flow extracts date information embedded in document filenames and populates a Document Date column. This enables date-sorted views in SharePoint without anyone needing to rename files or enter dates manually.

Testing and going live

Every flow was tested in a development environment with test data before being promoted to production. The lead-to-case conversion flow was tested for both the clean path (new case, no duplicates) and edge cases (duplicate reference attempt, missing data, Trello API failure). Each failure scenario was tested to confirm the failure log was written correctly and no partial records were created.

Production uses a dedicated service account with the minimum permissions required. The flows run under this account rather than a named user's credentials — so they don't break when someone leaves or changes their password.